If formula Date not Blank

[Wanna Learn]

Hello in Excel 2003
This is my formula =IF(M18=" "," ", M18+60)

In Cell M18 person will enter a date
Cell M 19 is formated as date formula is ( if cell M18 is blank then
blank, otherwise the date on cell M18 plus 60 days )
The problem is if I add any date to cell M18 I get the correct answer , but
if cell M18 is blank I get a date February 29 1900
How Do I formula so that if M18 is blank them M19 is blank

 

[David Biddulph]

=IF(M18="","", M18+60)

 

[Chip Pearson]

The problem is that in your formula, you are testing for a space
character, not an empty cell. Between the quote marks, you have a
space character. If M18 is truly empty, it isn't equal to a space, so
the M18+60 gets calculated. If empty, M18 will be treated as a 0, so
you are calculating 0+60 as a date. Dates in Excel are just numbers, 1
= 1-Jan-1900, ... 60 = 29-Feb-1900 ... 39891 = 19-March-2009. That's
why you get the 29-Feb-1900 value. It is 60 days past 1-Jan-1900. Get
rid of the space characters within the quotes and your formula will
work properly.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Thu, 19 Mar 2009 12:44:16 -0700, Wanna Learn <Wanna

 

[Dave Peterson]

Like Chip mentioned, you should clean up those cells that have space characters
in them. It's a bad way to make a cell look empty/blank.

It's better to just select the cell and then hit the delete key on the keyboard.

But if you want, you could use a couple more variations:

=if(trim(m18)="","",m18+60)
or even check to see if M18 contains a number.

=if(isnumber(m18),m18+60,"")