Don't show 1900 on blank date

Question

I have a spreadsheet that converts the date in one cell to a year in another. I want to pull the formula down so that it automatically shows the date in columns A and B if there is a date in column C; however, I do not want it to say 1900 on blank cells. Columns A and B pull from C (are not manually entered) as shown in the image below:

A and B use this formula:

A is   =TEXT(C27,"YYYY")
B is   =TEXT(C27,"MMM") 

How can I edit these formulas so that columns A and B will show blank if there is no date in column C? If I cannot edit the formula, is there another solution?

Answer 1

=IF(C27=0,"",TEXT(C27,"YYYY")) should work - excel treats empty cells as equal to zero. So if it's equal to zero, this formula returns an empty string (""), which makes the cell look blank, if not then it returns TEXT(C27,"YYYY")

If this doesn't work your blank cells might have empty text (if you look in the formula and there's a single quote, ', that marks it as text and it won't be equal to zero) so try =IF(C27="","",TEXT(C27,"YYYY")) instead

Answer 2

In many cases, Excel treats a blank cell as if it were a zero 0. Excel is perfectly happy with a date value of 0 and treats it like January 0, 1900. Yes, the 0th day of January in 1900. (1904 if you're on an older version of Excel for Macintosh). For more details, see the relevant Microsoft support page.

For your purposes, if you want blank when the date is blank, checking for that blank state and returning blank will work.

=IF(C27="","",TEXT(C27,"YYYY"))
=IF(C27="","",TEXT(C27,"MMM"))

Other alternatives that may work for you are:

=IF(ISBLANK(C27),"",TEXT(C27,"YYYY"))
=IF(LEN(C27)=0,"",TEXT(C27,"YYYY"))
=IF(C27=0,"",TEXT(C27,"YYYY"))

Answer 3

All this takes is a custom number format for the cell.

First, format it with your desired date format (which I am sure it already is).

Then bring up the formatting functionality and choose Custom at the bottom of the list of types of number formatting.

It will show (to the right of that list) the symbols making up the current date format. For instance, it could show this:

m/d/yy;@

which will show any positive number as a date.

These formats can have four formats set: "positive value;negative value;zero value;text value" and while there can be more to it than that depending upon how you get interesting with the four formats permitted, all you need to do is add a format for any zero values the cell ends up containing. So you are interested in leaving the first part alone, and adding the two middle sections. (The @ actually is the fourth setting: text.)

Notice I separated the sections as I named them with the ; character. So the following will address all four sections and add the handling of zero values:

m/d/yy;;;@

Just that. The lack of any formatting characters between the 1st/2nd and the 2nd/3rd ; characters means that negative values and zero values will both be formatted as blank cells. (This has the advantage of handling negative values, but it you want what Excel will do for those on its own instead, put something, the m/d/yy (your own formatting, actually, but for this answer, that) perhaps, in between the 1st/2nd ; characters.

And you'll now see a blank when the value is zero, which is what it is when you get that 1/0/1900 result.