Summing time in Excel

When I hire someone to fill a short-term casual role here at Gravity Computing, I usually make up a simple timesheet in Excel.  I’ll use that to track start and end times, and to calculate how much they should be paid at the end of the week.  One wouldn’t think of this as any particularly advanced Excel formula – it should be pretty basic, right?  But Excel never does this the way I think it should:

This is because Excel doesn’t store time as a time, but actually as a percentage of the day that has passed:

Once we know this, we can find ways to get around it.  In my example above, I was trying to calculate daily pay excluding an unpaid break of .5 hours.  I could reformat the lunch break as a time value, and enter the lunch break as 30 minutes instead:

This works great.  Now I can do other stuff, like calculate the total number of days worked, the total length of lunch breaks, average start & finish times, and also see what I should expect the payroll package to pay this poor guy:

Hm.  It’s counted the days accurately; the average start time and total breaks look fine… But it looks like there’s something seriously wrong with the other cells!  A quick check shows that the average end time is actually correct.  But why is Excel suggesting that Mr Bloggs has only worked 14 payable hours?  Clearly he’s worked closer to 38!  The answer is in the fact that the total time & total to pay columns are also formatted as time.  This is one of the things that Excel does to help us out: when we’re summing a set of data, the summed result is automatically formatted the same as the source data!  And this means that anything above 24 loops right back around to zero… Just like a clock.  Because, remember, Excel is only storing these times as a percentage of the passage of the day.  If you change the cell format to a number, you’ll see that cell G11 actually only holds the value 1.58.  And Joe would be pretty unhappy with me if I paid him for an hour and a half after a whole week’s work!

The secret here (at least, the easy way) is to use a special format for the two bottom-right totals.  If we click on the cell, press Ctrl-1 (or go to cell properties), we can get the correct format:

With the new format selected for our totals cells, we get the correct output!

I guess I should wrap this up by saying… Don’t trust Excel to always do the right thing.  When Excel helps you out, it doesn’t always get it right, and it’s important that you check the answers that you’re getting from the package, BEFORE you rely on them.  Make sure it’s ready to handle things that fall outside of it’s expectations.  For example, what would have happened above if Joe had a particularly late night and finished after midnight?