If formula Date not Blank
 

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

If formula Date not Blank
 

=IF(M18="","", M18+60)
--
David Biddulph

Wanna Learn wrote:
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

If formula Date not Blank
 

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

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

If formula Date not Blank
 

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,"")



Wanna Learn wrote:

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

--

Dave Peterson