The past few weeks I have had endless questions about time calculations in Excel. So this week I thought I would share a few with you.
First let’s quickly understand how Excel stores dates and time.
Date and Time 101
Excel stores dates and time as serial numbers. When you format the serial number as a date or time, or date and time as in the example below, it displays it in a date/time format but underlying is still a serial number.
The serial number is made up of two parts.
The digits before the decimal are the date and the digits after the decimal are the time.
Dates in Excel
Dates in Excel start on 1st January 1900. Therefore the serial number for 1/1/1900 is 1.
The serial number for 1/1/2012 is 40909 because it is the forty thousand, nine hundred and ninth day since 31/12/1899*.
Note: in Australia our dates are displayed as dd/mm/yyyy.
*Actually 1/1/2012 is only the 40908 day but Excel includes the date 29th Feb 1900 even though 1900 was not a leap year. This inclusion was intentional to provide compatibility with Lotus 1-2-3 which contained the bug and had the market share when Excel was released!
Time in Excel
Time serial numbers represent a fraction of a 24 hour day.
Convert Time to Decimals
Often we need to convert time to a decimal so that we can calculate hours x rate for the purpose of payroll or billing.
It’s easy. Remember, the serial number is a fraction of a day so you simply multiply it by 24.
Note: you don’t need to enter a date and time. If you enter time only, the date part of the serial number is 0.
OK, now you know the rules let’s look at some examples or working with time.
Shift Work Timesheets and Overtime
Calculating the difference between two times on the same date is as simple as subtracting the start time from the finish time, but it’s not so easy if your start and finish times are on different dates, as in the case of shift workers.
Notice the finish time below for Monday is actually 7AM on Tuesday.
We can use a clever trick to test for time that finishes on a different date by checking whether the finish time is less than the start time, as is the case for Monday and Tuesday above.
Taking the formula in cell G4:
The first part of the formula takes the finish time less the start time and then checks whether the finish time is less than the start time (E4<B4). In the case of Monday (E4<B4) evaluates to TRUE, and since TRUE = 1 it adds 1 to E4-B4 to correctly calculate the time.
Note: if your times are entered with the date and time you can simply subtract one from the other, it’s only in the case where times are entered on their own that you need to test whether the finish time is < the start time.
I’ve had a few people ask me how to round time in 10 minute increments for the purpose of billing clients at an hourly rate.
The table below shows rounding using the ROUNDUP, MROUND and ROUND functions.
If you want to bill in 30 minute increments change the 10 in the above formulas to 30.
Display Time with Text
I had a question this week asking how to display time in a format that reads ‘2 hours 15 minutes’.
There are quite a few formulas that will concatenate text for the words ‘hours’ and ‘minutes’, but I prefer to simply use a custom number format.
You can see in cell K2 the formula subtracts the start time from the arrival time to give the number of hours late.
I then formatted the cell to show the time with words using a custom number format like this:
The benefit of this approach is that the underlying time value remains in the cell so you can use it in other formulas. For example you might like to add up the Time Late column to get a total time late etc.
Enter your email address below to download the sample workbook.
For more time and date related tutorials: