Home

If Function Date Format Changes to Serial Date

Ashlie Hoffman
New Contributor

=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. 

3 Replies

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.

 

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?

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

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

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies