A new series of articles offering tips and tricks to conquer common Excel problems starts with time formats and calculations.
Buried within the vaults of AccountingWEB are numerous Any Answers queries and tutorial articles that tackle Excel problems that catch people out. One of the most common is how to format dates and times - particularly if you want to use them for calculations, for example in a timesheet.
Excel time/date system
To start from the beginning (which for Excel is 00:00:00 on 1 January 1900),
Microsoft explains that its spreadhseet has been designed to store all dates as integers and times as decimal fractions. This set-up allows Excel to add, subtract, or compare dates and times like any other number.
Excel’s serial numbering system starts at 1, which represents 12:00:00am on 1/1/1900. Times are stored as decimal numbers between .0 and .99999, where .0 is 00:00:00 and .99999 is 23:59:59.
There are some weird quirks about this system (see below), but the combination of date integers and time decimal fractions makes it possible to display dates and times - and to run calculations on them.
For example if you type 40585.06 into an Excel cell and then choose Format Cell from the right mouse click menu, it will show the date and time 11/02/2011 01:26:24 (depending on the default time format). You can add these numbers together (=A1+B1) and SUM them to get a total figure for hours elapsed, but will encounter a problem if the total adds up to more than 24 hours. This is explained in more detail in the timesheet section below.
Useful time functions
Excel comes with a set of ready made functions to simplify complex date and time calculations. These functions are available via the Formulas tab in the Excel 2007 and 2010 menus, but can be installed on Excel 2003 using the
Analysis Toolpak Add-in.
- The TIME function - TIME(hour,minute,second) - converts hours, minutes and seconds to a serial number value under Excel’s system and displays it in a conventional time format.
- The TIMEVALUE function - TIMEVALUE(“time_text”) - converts a conventional time format entered into a cell with a General format into an Excel time value. Formatting the cell as a number will display the decimal value (eg 0.35763889 = 8:35 AM), or more sensibly, you can alter the Format to Time to make it comprehensible for a human user. To use the Function select it either by clicking the fx figure just to the left of the formula box, or choose it from the Time & Date function list via the Forumlas tab. Or just use the following string in your formula: TIMEVALUE(“8:35 AM”). Any date information in the “time_text” string is ignored, which will complicate calculations involving separate days (see below).
- The NOW Function - NOW() - returns the serial number the date and time the function is invoked.
Working with dates and times
The most common arithmetic use of times will be to calculate elapsed hours. Most commonly for accountants this will be on a timesheet where they want to multiply the results by an hourly rate to produce billing total.
Excel’s timing system makes it easy to add or subtract the hourly totals, and all you need to do to retain more human-compatible logic is to display the results in a Time format cell. Several years ago, AccountingWEB.co.uk member
Chris Lloyd explained how to calculate the difference between two times as a decimal number. Keep these points in mind:
- To display the value as a number of days, or as a fraction of a day for times, simply choose Format, Cells and choose a number format.
- To enter 45 minutes in a cell formatted to display a time, you need to enter the zero number of hours, so 0:45,
- If you are working with times, and need a value in decimal hours, multiply the result by 24 to convert days to hours.
Time values over 24 hours
An old query from
Jonathan Taylor’s highlighted the discrepancy that occurred if a time calculation produced a value greater than 24 hours, in which case Excel would act like a clock and revert to zero, so 28 hours would be displayed as 04:00. In response AccountingWEB member Zubair Edhy devised a function combining MOD and INT to get the result. It was a clever approach, but it’s much easier to follow David Wagstaffe’s advice to choose the Custom format [h]:mm which accumulates the hourly total rather than reverting to 0:00 after 24.
Hourly charge calculations
To multiply time values by monetary values for a timesheet on basis of pounds per hour, remember to multiply by the number of hours in a day as well as the hourly rate. For example =A1*24*72.
YouTube video - MrExcel time calculation tutorial
Also see the ExcelZone Compendium dates & numbers digest and Formatting section.
Register with AccountingWEB.co.uk and sign up for our free monthly ExcelZone eBulletin to receive regular updates.
