Date formula to Return blank if cell is empty

**Ctaxes** · 09-30-2008, 05:30 PM

Trying to answer a question, right now i have a formula that calculates 12 months on to a prior field, IE:=DATE(YEAR(C6),MONTH(C6)+12,DAY(C6)), when I type this into cell (D6)...if there is nothing in cell (C6), the date formats to 12/31/1900....once i type something into C6, the date fills in to the proper date, is there a formula, or a way to format this cell so if nothing is in the cell C6, D6 shows nothing in the field.

**VBA Noob** · 09-30-2008, 05:36 PM

Ctaxes,

Welcome to the forum. Please read forum rules below before posting again.

Your title has being amended for you this time

=IF(C6="","",DATE(YEAR(C6),MONTH(C6)+12,DAY(C6)))

VBA Noob

**mewingkitty** · 09-30-2008, 07:55 PM

I've used

=IF(ISBLANK(B2),"",your formula here)

That way if c6 is blank, your formula cell outputs a "" which is nothing, so it stays blank. with the formula after that, since that is the "In case of false" statement, then it will carry out your formula.

Same thing as what he said, I like the isblank statement because it's clear what I've done later on, and I'm good at forgetting things.

Cheers.

**Ctaxes** · 09-30-2008, 08:00 PM

I posted the formula you responded with, testing the formula in my cell, the cell however will return blank even if information is in the cell, C6 is suppose to show a date i enter manually, IE: 1/22/2008, in D6 the desired effect, is suppose to be 1/22/2009, unless there is nothing entered into the cel C6, then I wish D6 to show empty, even with the correct formula in the cell.

IE:
C6____________D6
1/22/2008______formula[ =DATE(YEAR(C6),MONTH(C6)+12,DAY(C6)) ] which will show 1/22/2009.

If there is no date in C6 it shows the date 12/31/1900 in cell D6

IE:
C6___________D6
*blank*_______formula[*same as above*] which shows 12/31/1900

**Ctaxes** · 09-30-2008, 08:09 PM

I just used your formula Mewingkitty, I got the desired effect and the formatting sticks! I appreciate all the help, a problem solved! I do apoligize if I am not posting my issues correctly, practice will make perfect!

**mewingkitty** · 09-30-2008, 09:08 PM

Forgot to wrap code when I posted that first link.
I'll keep an eye on it in the future, I'm new here, sorry for the breach in policy.

**tunaaa** · 09-17-2011, 12:47 PM

Originally Posted by mewingkitty

I've used

=IF(ISBLANK(B2),"",your formula here)

That way if c6 is blank, your formula cell outputs a "" which is nothing, so it stays blank. with the formula after that, since that is the "In case of false" statement, then it will carry out your formula.

Same thing as what he said, I like the isblank statement because it's clear what I've done later on, and I'm good at forgetting things.

Cheers.

Apologies for reopening this thread, but I feel it should be pointed out that the above two solutions are, technically speaking, incorrect. A cell containing a null string "" is not the same as a blank cell. This technicality is important and should not be ignored, as formulas assessing blanks will return unexpected results and hence risk introducing more bugs into the workbook. For example, most blatantly the ISBLANK(value) function will return FALSE for a cell containing a null string.

However, workarounds do exist and the following links should point you in the right direction:
http://stackoverflow.com/questions/1...rmula-in-excel
http://excel.tips.net/T002814_Return...ank_Value.html