Subtracting time...again


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
 
Ad

Advertisements

J

joeu2004

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.
 
Ad

Advertisements

W

wabbleknee

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top