Excel - Ignore blank cells

[robertbarrett]

I have a formula to take a cell and add a year to it in another cell. (i.e. modified date and expiration date)

In the expiration date field there is the formula:
=DATE(YEAR(D12)+1,MONTH(D12),DAY(D12))

However, if the referenced cell (D12) is blank, then I get "12/31/1900"
I just want the result in that situation to be a blank cell.

Please advise.

 

[SkipVought]

Hi,

[tt]
=IF(ISBLANK(D12),"",D12)
[/tt]

If D12 is a date then why used the DATE() function? All you need is to format that cell with a date format.

Alternatively, you could forgo the DATE() formula (just =D12) and use a Conditional Format to shade the FONT the Sam as the background color when the cell value is ZERO.

FAQ68-5827

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[robertbarrett]

Skip,

I see what you are saying but =IF(ISBLANK(D12),"",D12) does not allow a date value to be entered into D12 and populate as a year later for another cell.

 

[SkipVought]

What year? The cell is BLANK. There is no year! You currently have no valid year in that cell!

Please explain.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

I just want the result in that situation to be a BLANK cell.

THAT is an impossible result using a formula, because the cell cannot be BLANK (meaning empty) if a formula is in it!

A formula cannot return a BLANK.

It can return a zero length string, which is what my formula does.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[robertbarrett]

SOLVED:

=IF(ISBLANK(D12),"",DATE(YEAR(D12)+1,MONTH(D12),DAY(D12)))

Allows for a blank cell to produce a blank result. Any date in D12 will then be that date+1yr in the cell the formula is placed into.

Thank you for your assistance.

 

[SkipVought]

How dumb of me to have missed the YEAR+1!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]