Excel Formula

  • Thread starter Thread starter Lisa
  • Start date Start date
L

Lisa

I am trying to get results for cells with dates in them. Who do get the
following results:
a2 = begining date (3/4/8)
b2 = ending date (3/5/8)
c2 = difference (1)
I do not want (zero) 0 to show up if the cells are blank
I do not want a negative number to show if b2 is blank
I do want a zero to show if the b2-a2=0

here is the current formula I am using
=IF((N73-M73<0)," ",(SUM(N73-M73)))
If the M & N are blank I get a zero in O73. I want O73 to be blank
If N73 is blank, O73 is also bank. This is OK
If N73 & M73 have the same date I get Zero in O73. This OK
 
Looking at your formula, you don't need the SUM() function, because you are
not adding a series of parameters; look at Excel help for the SUM function.
Also, you don't need so many parentheses, but they don't do any harm.
Also you don't need spaces between the double quotes to get a blank; easier
to stick to "", then it's easier to test for such an empty string.

You seem confused between A2/B2/C2 and M73/N73/O73, and you've also
apparently contradicted yourself in saying
"I do want a zero to show if the b2-a2=0", but also saying
"If N73 & M73 have the same date I get Zero in O73. This OK".

But you could try:
=IF(OR(N73="",M73=""),"",N73-M73)
 
Hi Gary,

Your formula does allow for the number zero to show if N73-M73 = 0
3/4/8 - 3/4/8 = 0 Zero needs to show in O73
 
Thanks David. That worked
--
Lisa M.


David Biddulph said:
Looking at your formula, you don't need the SUM() function, because you are
not adding a series of parameters; look at Excel help for the SUM function.
Also, you don't need so many parentheses, but they don't do any harm.
Also you don't need spaces between the double quotes to get a blank; easier
to stick to "", then it's easier to test for such an empty string.

You seem confused between A2/B2/C2 and M73/N73/O73, and you've also
apparently contradicted yourself in saying
"I do want a zero to show if the b2-a2=0", but also saying
"If N73 & M73 have the same date I get Zero in O73. This OK".

But you could try:
=IF(OR(N73="",M73=""),"",N73-M73)
Click to expand...
 
Back
Top