If Function Date Format Changes to Serial Date

[Ashlie Hoffman]

=IF((Genesis!AF2=""),"",Genesis!AF2)&CHAR(10)&IF(Genesis!AG2="","",Genesis!AG2)

Genesis!AF2 is a formulated Y

Genesis!AG2 is a hard entry date, 1/1/18, and formatted to be as such date. 

I want the formula to return as :

Y

1/1/18

if true, and if not then to leave blank. Genesis!AF2 returns correctly, as Y, but Genesis!AG2 returns a Serial, as 43101. I have formatted the formula cell to match the same as the hard entry Date and still get Serial.

I entered the IF statement to return a blank if the date cell is blank, and if not then to return the cell. This works, but returns a Serial Date instead of the formatted date. 

[#2]

You combine your value with text CHAR(10). Excel takes the value of the AG2 cell, which is 43101, convert to text and adds to the text before. To keep formatting use TEXT(AG2,"mm/dd/yyyy") instead of cell AG2 value.

[#3]

If there isn't a date entered in AG2, then your suggestion returns a 01/00/1900 instead of leaving the cell blank. Is there a way to keep my IF function and add the TEXT function as well?

[#4]

Yes, you shall check if the cell is blank or not. Like this

IF(ISBLANK(AG2),"",(TEXT(AG2,"mm/dd/yyyy"))