Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to return a blank formula cell if the reference is blank? | Excel Worksheet Functions | |||
Formula for looking up blank and/or date-filled cells- contingent | Excel Discussion (Misc queries) | |||
Average Formula to display blank cell if named range is blank | Excel Worksheet Functions | |||
Date Formula Problem - Leave date blank if Null | Excel Worksheet Functions | |||
How do I make a blank cell with a date format blank? | Excel Worksheet Functions |