using if statement to subtract 24 hours from time
 

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/2088 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

Richard

using if statement to subtract 24 hours from time
 

Dear Richard,

Try =IF(F27-F26<0,0,F27-F26)

HTH


"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/2088 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

Richard

using if statement to subtract 24 hours from time
 

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

This will subtract 24 hours (1 day) from B1 (replace with A1 if you want to
reduce that by one day) if the difference is more than 24 hours otherwise it
will retrun zero.

btw in one of the dates you typed 2088... not 2008.

"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/2088 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

Richard