Calculating Time in Excel

It’s frustrating 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 with Excel and summing time.

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.

Download the Excel 2007 workbook and follow along.

Dates in Excel

Excel gives each date a numeric value starting at 1^st January 1900.

1^st January 1900 has a numeric value of 1, 2^nd January 1900 has a numeric value of 2 and so on…  These values are called ‘serial values’ in Excel, 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 1^st March 2008 9:30:30 AM has a true value of 39508.39618.

39508 being the serial value representing the date 1^st March 2008, and .39618 being the decimal value for the time 9.30AM and 30 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 in Excel.

How to SUM 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 appear.  This way I know I’ve formatted my time correctly.

These square brackets instruct Excel to and add the hours.  Without them Excel 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.

Time x rate to calculate wages or charge out fees

I quite often want to calculate wages or a charge out fee in Excel.  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.

Use Excel in 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 Excel adds the hours 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.

Is there a time calculation that you’d like me to cover? Let me know by leaving a comment below.