subtracting time

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

Using the following formula I am receiving data that is not correct:

=IF(B6-E6<0,"-"&TEXT(ABS(B6-E7)/24,"[h]:mm"),B6-E6)

I would like to have a formula that will subtract time. For example I need
the return data in D1 to be -0:00:45. In D2 it needs to be +0:12:09 etc…

Thanks in advance for your help.

Assoc 12:29:15 PM 12:30:00 PM -0:11
Assoc 8:12:09 AM 8:00:00 AM 0:12:09
Assoc 7:53:28 AM 8:00:00 AM -0:02
Assoc 6:56:45 AM 7:00:00 AM -0:05
Assoc 8:57:20 AM 9:00:00 AM -0:02
Assoc 7:58:18 AM 8:00:00 AM -0:03
 
=MOD(B6-E6,1)

and format as time

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Try

=IF(B6-E6<0,"-","+")&TEXT(ABS(B6-E6),"[h]:mm:ss")

although I'm not sure how you'd deal with time periods that span
midnight, do you have any of those?
 
Bob,

This is great if the time is over, however I am receiving a 59 hour thing if
the time is under. How do I use =MOD for same day returns?

Thanks


8:00 7:27 59:27
8:00 17:05 9:05
8:00 18:30 10:30


Bob Phillips said:
=MOD(B6-E6,1)

and format as time

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

JR said:
Hello,

Using the following formula I am receiving data that is not correct:

=IF(B6-E6<0,"-"&TEXT(ABS(B6-E7)/24,"[h]:mm"),B6-E6)

I would like to have a formula that will subtract time. For example I need
the return data in D1 to be -0:00:45. In D2 it needs to be +0:12:09 etc.

Thanks in advance for your help.

Assoc 12:29:15 PM 12:30:00 PM -0:11
Assoc 8:12:09 AM 8:00:00 AM 0:12:09
Assoc 7:53:28 AM 8:00:00 AM -0:02
Assoc 6:56:45 AM 7:00:00 AM -0:05
Assoc 8:57:20 AM 9:00:00 AM -0:02
Assoc 7:58:18 AM 8:00:00 AM -0:03
Click to expand...
Click to expand...
 
JR,

That formula assumes that if the end time is before the start time, it is in
the next day, which would be 23 hours plus (not sure where you get 59
from?).

If you want 33 mins, try

=ABS(E6-B6)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

JR said:
Bob,

This is great if the time is over, however I am receiving a 59 hour thing if
the time is under. How do I use =MOD for same day returns?

Thanks


8:00 7:27 59:27
8:00 17:05 9:05
8:00 18:30 10:30


Bob Phillips said:
=MOD(B6-E6,1)

and format as time

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

JR said:
Hello,

Using the following formula I am receiving data that is not correct:

=IF(B6-E6<0,"-"&TEXT(ABS(B6-E7)/24,"[h]:mm"),B6-E6)

I would like to have a formula that will subtract time. For example I need
the return data in D1 to be -0:00:45. In D2 it needs to be +0:12:09 etc.

Thanks in advance for your help.

Assoc 12:29:15 PM 12:30:00 PM -0:11
Assoc 8:12:09 AM 8:00:00 AM 0:12:09
Assoc 7:53:28 AM 8:00:00 AM -0:02
Assoc 6:56:45 AM 7:00:00 AM -0:05
Assoc 8:57:20 AM 9:00:00 AM -0:02
Assoc 7:58:18 AM 8:00:00 AM -0:03
Click to expand...
Click to expand...
Click to expand...
 
Back
Top