Calculating time in Excel can be very frustrating, especially when all you want to do is sum a column of times to get the total, but for some reason you end up with a random number like in the example below.
Let me explain what’s going on and how to calculate time in Excel.
Since time is a concept rather than a mathematical equation, Excel has come up with systems for handling dates and times whereby they are given a numerical value.
Dates in Excel
Excel gives each date a numeric value starting at 1st January 1900.
1st January 1900 has a numeric value of 1, 2nd January 1900 has a numeric value of 2 and so on... These are called ‘serial values’, and they enable the use of dates in calculations.
Times in Excel
Times are seen as decimal fractions. 1 being the time for 24:00 or 0:00. 12:00 has a value of 0.50 because it is half of 24 hours, or the whole number 1, and so on.
To see Excel's value for a date or time, simply format the cell as general.
For example the date and time of 1st January 2012 10:00:00 AM has a true value of 40909.4166666667
40909 being the serial value representing the date 1st January 2012, and .4166666667 being the decimal value for the time 10.00AM and 00 seconds.
Although the above is important to know, thankfully Excel has built in formatting so that we don’t have to enter our dates and times in serial or decimal values.
However it’s the lack of understanding of these serial and decimal values for time that cause common errors when performing calculations on time.
The Secret to Calculating Time in Excel
If you want to sum time (as in my example above) you need a custom format that uses [ square brackets ] around the hours. Like this:
You can see in the Sample box the correct total appears. This way I know I’ve formatted my time correctly.
These square brackets instruct Excel to add the hours. Without them it will reset the sum to zero every time it gets to 24 hours.
There’s no need to modify the formatting of the minutes with square brackets as they automatically add up.
Note: in some versions of Excel when you insert a formula it will automatically apply the correct formatting to give you the total. Just be sure to check the total is reasonable or check the formatting is as stated above.
This square bracket time formatting requirement also applies when using other operators like +/-.
What if you want to sum seconds to find out the total seconds?
While this isn’t the wrong answer, I want to know the total number of seconds, not how many minutes and seconds there are. To do this you’d need a custom number format like this:
You can see from the sample box I now get 237 seconds, instead of 3 minutes 57 seconds.
This can also be applied to minutes or hours. Just change the formatting to [mm] or [h] respectively.
I quite often want to calculate wages or a charge out fee. But if you don’t know this trick you’ll be tearing your hair out...and probably revert to using fractions like 7.50 for 7 hours 30 minutes, just so you can get the answer you expect.
While entering halves or quarters of an hour as fractions is fine as, it becomes a hassle when your billing increments come down to 10 minutes or any other fraction you can’t calculate in your head....unless you’re superhuman!
Thankfully the solution is simple. Just multiply by 24 like I have in the example below.
Timesheets to Calculate Time Worked
Below is a fairly basic timesheet layout. You can see in the formula bar that the time calculation is performed as a simple equation =I4-I2-I3.
I’ve done some funky formatting to the cells to assist the person keying in the time:
- Rows 2 and 4 are formatted with h:mm AM/PM. The employee has to type in their time as you see it in the cell for the formatting to work correctly. The advantage to this is they don’t need to convert their finish time to a 24 hour clock style. The disadvantage is a bit more typing with the need for the AM or PM distinction. Swings and roundabouts.
- Rows 3 is formatted with h:mm "h:mm". This adds the text h:mm to the end of the value for presentation purposes. The employee only needs to type in 0:30 for a half hour lunch break, and Excel will add the h:mm to the end.
- Row 5 is formatted with [h]:mm "h:mm" to ensure the hours are added correctly.
You can then calculate wages using the total figure in cell N5 with the Time x Rate formula above. Of course this doesn’t take into account overtime and penalty rates. That lesson is for another day.
Calculating Time that Spans 2 Days
When your start and finish times are on different dates, as in the case of shift workers, you either need to enter the Date and Time in your timesheets, or if you only enter the time then you need a clever formula to detect this. In the example below the finish time for Monday is actually 7AM on Tuesday.
Here 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.
Alternatively you could use a MOD formula in cell G4, like this:
The MOD function returns the remainder after a number is divided by a divisor. The formula is clever because it handles negative times, which usually return pound errors, by converting them to the balance of a day (hence the 1 in the formula). This returns the same result as the first formula [ =(E4-B4+(E4<B4)-(D4-C4+(D4<C4)))*24 ] above.
While I think the MOD function example above is super clever, it's much more difficult to explain, and more difficult to understand for those who might later inherit your spreadsheet. Feel free to use the formula you're most comfortable with, as they both return the same result.
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.
Do you need help with a time calculation? Post your question on our Excel Forum and we'll be happy to help you.