Hi - Dave here.
Happy Friday!
One of the weirdly complicated problems in Excel is calculating the hours between two times.
In part, this is because Excel stores time as fractional values. For example, 0.25 is 6:00 AM or 6:00 hours, depending on formatting. The number 0.25 makes sense when you consider that 6 hours is one-quarter of a day, and a day in Excel equals 1. But it isn't the way most people think about time.
The other reason time is complicated is that it resets to zero again at midnight. If you have a start time and an end time that occur on the same day, you can subtract the start time from the end time and end up with a positive number that represents the hours between the two times.
However, if the times cross midnight, the start time can be greater than the end time, and you'll end up with a negative time, which Excel does not support.
One solution to this challenge is to use a conditional formula like this:
=IF(end>start,end-start,1-start+end)
But you can also use a compact formula based on the MOD function:
=MOD(end-start,1)
You can see the second formula in the worksheet below:
[
Download the workbook and read the full explanation]
I went down the rabbit hole this week trying to explain this problem in some detail while also providing 3 formulas that will get the job done. I hope you find it useful. Download the worksheet and try it out yourself.
Note: These formulas will work in any version of Excel.
Excel formulas
We maintain a list of over 500 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
