Subtracting time...again

  • Thread starter Thread starter wabbleknee
  • Start date Start date
W

wabbleknee

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.

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. Tx
 
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.
Click to expand...

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.
Click to expand...

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.
 
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.
Click to expand...

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.
Click to expand...

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.
 
Back
Top