I have created a spreadsheet to find the difference between two time intervals...for example when something was received (date and time) and when it was sent out (date and time).
Assuming:
A2 = 8/15/2010
B2 = 8:00AM
C2 = 8/16/2010
D2 = 10:00AM
In my Excel spreadsheet column A is date in, column B is time in, column C is date out, column D is time out, finally column E is the difference in text version as follows:
E2=C2-A2-(B2>D2)&" days "&TEXT(1+D2-B2,"hh:mm")
In column F, I want to be able to translate column E into minutes only or hours only so I used the following formula for hours:
F2=IF(C2-A2>0, TEXT(1+D2-B2,"[h]:mm") & " hour(s)", TEXT(D2-B2,"[h]:mm") & " hour(s)")
Or using minutes only: F2=IF(C2-A2>0,TEXT((1+D2-B2)*60, "[h]:mm") & " minute(s)", TEXT((D2-B2)*60,"[h]:mm") & " minute(s)")
My problem arises when it is the same time/day…for some reason it just states 24 hours instead of 0. Assuming the following:
A2=8/15/2010
B2=2: 00PM
C2=8/15/2010
D2=2:00PM
I would really appreciate if anyone could tell me why this is happening/ how to solve this…or if there is a better equation to use! Could you please write out the formula? Thank you!
Bookmarks