Solved: date & time difference

[vzachin]

hi,

I have 2 columnslabeled "Time In" & "Time Out".
The cells in each column are formatted as mm/dd/yy hh:mm.
I need to calculate the difference, whether it's in minutes, hours or days....there should be no need for months (based on the data i have)


There was a recent post regarding how to calculate date & time but i'm unable to locate it.

thanks
zach

[Paul_Hossler]

In C4, enter

[vba]
=B4-A4
[/vba]

and number format C4 as [h]:mm to get 24 hour clock if you just want hours

There's a DateDiff() function, but it depends on which Excel version you're using

Paul

[vzachin]

hi paul,

thanks for the reply...i forgot about format [h]:mm....

is there an easy way to figure out "number of days + hours"

eg: if C4 = 30, i would divide C4/24 and i would get 1.25. how do i equate .25 to mean 6 hours. if i did long division C4/24, the answer is simple: 1 remaining 6 (which is 1 day 6 hours)

my math is fuzzy right now.

thanks again
zach

[agarwaldvk]

Hi vzachin

So long as the two values are not entered as text valules, simply taking the difference will return the number of days (in decimal values). The number to the left of the decimal point gives you the number of whole days and that to the right gives you the number of part days. Multiplying the fractional part by by 24 will give you the number of hours. Again, the number to the left of the decimal point gives you the number of whole hours and that to the right gives you the number of part hours. Multiplying the fractional part by by 60 will give you the number of minutes. Again, the number to the left of the decimal point gives you the number of whole minutes and that to the right gives you the number of part minutes. Multiplying the fractional part by by 60 will give you the number of seconds. Again, the number to the left of the decimal point gives you the number of whole seconds and that to the right gives you the number of part seconds.

Hopefully, this will give you what you are after.


Best regards


Deepak Agarwal

[Paul_Hossler]

if the difference is in E4 ..

[VBA]
=INT(E4)&" days, "&(24*(E4-INT(E4)))&" hours"
[/VBA]

Paul

[vzachin]

Deepak & Paul:

Thanks for the explanation & formula.

zach