Excel formula that hides results

March 6, 2014 4:47 AM

I am drowning in Google results! Looking for help making a formula that will only show a value once data is entered into another cell.

I have one cell which can have a date added to it, and an adjacent cell which needs to show the following month.

I have tried this but it isn't working:

=IF(A1="","",(A1+30))

help!

posted by ellieBOA to Computers & Internet (10 answers total)

That works for me. If I make a new worksheet and put in:

A1: 1/1/2014
B1: =IF(A1="","",(A1+30))

and set B1 to have a date format, B1 will display 1/31/2014 (which is not a month later, but that's another problem altogether). If I delete A1, B1 disappears.

What happens for you?
posted by grouse at 4:52 AM on March 6, 2014

Define: "isn't working". What do you get?

"A1+30" isn't adding a month. You need to fiddle with the dates a bit, but without knowing what you want and what you're getting, there's no answer to this.
posted by pompomtom at 4:53 AM on March 6, 2014

Best answer: By the way, to add a month, you can use the EDATE function, as in =IF(A1="","",EDATE(A1,1)).
posted by grouse at 4:55 AM on March 6, 2014 [2 favorites]

Response by poster: Typo on the cell reference of my original formula!! This is what happens when you stare at a spreadsheet all morning. The EDATE is new to me so thanks grouse.
posted by ellieBOA at 4:59 AM on March 6, 2014

This seems to work for me: =IF(A1="","",A1+30)
posted by davidvanb at 5:04 AM on March 6, 2014

OMG- EDATE function, where have you been all my life!

Once again, Meta Filler prepares me for the work day
posted by Lesser Shrew at 5:38 AM on March 6, 2014

For reference, though, =ISBLANK(A1) is the way to test whether a cell is really blank. Under certain circumstances, A1="" and ISBLANK(A1) can return different results.
posted by Wolfdog at 1:58 PM on March 6, 2014 [2 favorites]

Interesting. Can you name one of those circumstances?
posted by grouse at 3:24 PM on March 6, 2014

Type just a single quote ' in A1. This makes it an empty string and =(A1="") will return TRUE while =ISBLANK(A1) will return FALSE.
posted by Wolfdog at 3:31 PM on March 6, 2014 [1 favorite]

ellieBOA's formula cell will also return different results for =(A1="") and =ISBLANK(A1). If her formula evaluates to True, the cell will equal "" but won't be considered blank (because it has a formula in it).
posted by lostburner at 4:01 PM on April 4, 2014