How to calculate hours between two times

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: [How to calculate hours between times with the MOD function.]( [[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 [View this email in a browser]( [Exceljet Logo]( Exceljet P.O. Box 4804 Salt Lake City, UT 84110 Copyright © 2024 Exceljet, All rights reserved. You received this email because you are subscribed to our newsletter. To unsubscribe, click the link below. [Unsubscribe](