Apr 11 2018
12:25 PM
- last edited on
Jul 25 2018
11:40 AM
by
TechCommunityAP
Apr 11 2018
12:25 PM
- last edited on
Jul 25 2018
11:40 AM
by
TechCommunityAP
=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.
Apr 11 2018 05:08 PM
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.
Apr 12 2018 06:17 AM
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?
Apr 12 2018 01:46 PM
Yes, you shall check if the cell is blank or not. Like this
IF(ISBLANK(AG2),"",(TEXT(AG2,"mm/dd/yyyy"))