Forum Discussion
Need help calculating the difference between two dates and times
Hi Everyone!
I need to calculate the difference between two times on different dates in excel. I know this is an easy ask if I use the timestamp number format, however, for operational reasons, date and time have to be in separate columns.
In the example below, the difference between the two times is (negative) twelve hours. I'd like to develop a formula which would help me calculate the difference without merging date and time.
Any help would be really appreciated here
HassaanAhmed Calculating with dates and times like this is tricky. you need to introduce the MOD function to take only the decimal part of the date.time value.
By the way, I prefer to calculate with the TIME function when adding hours. See attached.
Note also that the 00:00 hour should always be the last hour for the day. On row 14 you had this hour on September 1.
8 Replies
HassaanAhmed You would build a formula like:
=(Date1+Time1)-(Date2+Time2)
It doesn't require the dates and times to be merged in the same cell to do this calculation.
Riny_van_EekelenThanks. I tried doing that already, but the result flips after 12 pm.
As you can see below, the time difference between the two times should still be -12 even after 12 pm, but it doesn't pick it up that way.
HassaanAhmed Please include a screenshot that shows the formula you entered and the column and row headers.
