subtracting time
 

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

subtracting time
 

=MOD(B6-E6,1)

and format as time

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"JR" wrote in message
...
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

subtracting time
 

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?


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=515457

subtracting time
 

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" wrote:

=MOD(B6-E6,1)

and format as time

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"JR" wrote in message
...
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

subtracting time
 

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" wrote in message
...
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" wrote:

=MOD(B6-E6,1)

and format as time

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"JR" wrote in message
...
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