Joeu2004. Thank you. I understand your answer.

"joeu2004" wrote in message

wabbleknee said:

I have been subtracting time using the mod function =mod(a2-a1,1) where a2

is stop time, a1 start time.

i.e (a2) 02/03/2014 06:50:00 PM - (a1) 02/03/2014 03:18:00 PM gives me

the answer 3:32 ([h]:mm or 3.53 in decimal format.

If you have date as well as time of day in A1 and A2, you don't need the

MOD(...,1) kludge at all.

Simply use the formula =A2-A1 formatted as [h]:mm.

Or use =(A2-A1)*24 formatted as Number for decimal hours.

wabbleknee said:

Now when I go beyond 24 hrs or 36 hrs for 48 hrs it is not showing

correctly

i.e. 02/04/2014 07:24:00 PM - 01/30/2014 03:01:00 PM gives me the

answer 4:23 The decimal format show's 124.38

The answer is 5 days 4 hours and 23 minutes. (I want the answer shown as

124:23 in [h]:mm format.)

Is the only answer subtracting the dates, then x24 and then add hrs.

You cannot subtract the dates and add the 24-difference of hours calculated

with the MOD(...,1) kludge.

The representation and calculation above is the "best" way, IMHO.

If dates and times of day were in separate cells, for example A1:B1 and

A2:B2, you could use the formula:

=A2+B2-(A1+B1)

or

=A2-A1+B2-B1

formatted as [h]:mm. Or

=(A2-A1+B2-B1)*24

formatted as Number for decimal hours.