Help with If statement to subtract time (follow up)
 

Hi all

I am trying to subtract 24 hours from a time if it is actually over 24 hours, if not then show 0

my formula works for everthing over 24 hours but can't get it show a 0 or under 24 hours.

In A1 8/3/2008 7:21

in B1 8/6/2008 15:36

in C1 I have =if(("B1-A1")(A1+"1"),(B1-A1)-"1",0)

The answer is 56:15:00 which is what I want but if B1 is 8/3/2008 it is showing ##### indicating a negative time number

instead of 0

any help would be appreciated

I did receive two responses but both options didn't work as expected.

Help with If statement to subtract time (follow up)
 

Try



=IF(B1-A11,B1-A1,0)


format as [h]:mm:ss





--


Regards,


Peo Sjoblom

"richard.littlewing" <richard.littlewing@gmail wrote in message
...
Hi all

I am trying to subtract 24 hours from a time if it is actually over 24
hours, if not then show 0

my formula works for everthing over 24 hours but can't get it show a 0 or
under 24 hours.

In A1 8/3/2008 7:21

in B1 8/6/2008 15:36

in C1 I have =if(("B1-A1")(A1+"1"),(B1-A1)-"1",0)

The answer is 56:15:00 which is what I want but if B1 is 8/3/2008 it is
showing ##### indicating a negative time number

instead of 0

any help would be appreciated

I did receive two responses but both options didn't work as expected.

Help with If statement to subtract time (follow up)
 

Why is it 56:15 not 80:15?

--
__________________________________
HTH

Bob

"richard.littlewing" <richard.littlewing@gmail wrote in message
...
Hi all

I am trying to subtract 24 hours from a time if it is actually over 24
hours, if not then show 0

my formula works for everthing over 24 hours but can't get it show a 0 or
under 24 hours.

In A1 8/3/2008 7:21

in B1 8/6/2008 15:36

in C1 I have =if(("B1-A1")(A1+"1"),(B1-A1)-"1",0)

The answer is 56:15:00 which is what I want but if B1 is 8/3/2008 it is
showing ##### indicating a negative time number

instead of 0

any help would be appreciated

I did receive two responses but both options didn't work as expected.

Help with If statement to subtract time (follow up)
 

If you want 56:15


=IF(B1-A11,B1-A1-1,0)

format as [h]:mm:ss

--


Regards,


Peo Sjoblom

"Peo Sjoblom" wrote in message
...
Try



=IF(B1-A11,B1-A1,0)


format as [h]:mm:ss





--


Regards,


Peo Sjoblom

"richard.littlewing" <richard.littlewing@gmail wrote in message
...
Hi all

I am trying to subtract 24 hours from a time if it is actually over 24
hours, if not then show 0

my formula works for everthing over 24 hours but can't get it show a 0 or
under 24 hours.

In A1 8/3/2008 7:21

in B1 8/6/2008 15:36

in C1 I have =if(("B1-A1")(A1+"1"),(B1-A1)-"1",0)

The answer is 56:15:00 which is what I want but if B1 is 8/3/2008 it is
showing ##### indicating a negative time number

instead of 0

any help would be appreciated

I did receive two responses but both options didn't work as expected.

Help with If statement to subtract time (follow up)
 

Try this:

=MAX(0,(B1-A1)-"24:00")

format cell as [h]:mm:ss



"richard.littlewing" wrote:

Hi all

I am trying to subtract 24 hours from a time if it is actually over 24 hours, if not then show 0

my formula works for everthing over 24 hours but can't get it show a 0 or under 24 hours.

In A1 8/3/2008 7:21

in B1 8/6/2008 15:36

in C1 I have =if(("B1-A1")(A1+"1"),(B1-A1)-"1",0)

The answer is 56:15:00 which is what I want but if B1 is 8/3/2008 it is showing ##### indicating a negative time number

instead of 0

any help would be appreciated

I did receive two responses but both options didn't work as expected.

Help with If statement to subtract time (follow up)
 

Slightly shorter...

=MAX(0,B1-A1-1)



"richard.littlewing" wrote:

Hi all

I am trying to subtract 24 hours from a time if it is actually over 24 hours, if not then show 0

my formula works for everthing over 24 hours but can't get it show a 0 or under 24 hours.

In A1 8/3/2008 7:21

in B1 8/6/2008 15:36

in C1 I have =if(("B1-A1")(A1+"1"),(B1-A1)-"1",0)

The answer is 56:15:00 which is what I want but if B1 is 8/3/2008 it is showing ##### indicating a negative time number

instead of 0

any help would be appreciated

I did receive two responses but both options didn't work as expected.