Custom Date Format Concatenated 1

[sxschech]

This is exported from MS-Access 2010 to an excel file...

Tab Name is Data
Value in Cell S2
'February 11, 2012

This is in Cell U2 to try and get the date to subtract by 1
=DATEVALUE(S2)-1

Tab Name is Overview
This is how should be displayed in cell A2

Spring 2012 As of February 10, 2012 (Census)

This is the formula:
=Data!B2 & " as of " & IF(AG37="Enrolled",Data!U2,Data!S2) & IF(AG37="Enrolled"," (Census)","")

This is what is actually showing in cell A2

Spring 2012 as of 40949 (Census)

I realize this is convoluted and you may say just fix the date in access before you export. The issue is that this is a display date (by user request) that only needs to be changed twice a year on census date and I want to maintain the actual run date for reference, since the data file created on the specific date rather than the date minus 1 day as a few years down the road if need to locate the source data, won't find it on the day - 1. Would like to automate this so don't have to deal with it. If I hard code, then I have to remember to fix the formula so that excel file will show the updated dates on non census date.

 

[Gavona]

Have a look at the Text function:
=text([red]IF(AG37="Enrolled",Data!U2,Data!S2) & IF(AG37="Enrolled"[/red],"DD MMM YY")

Gavin

 

[sxschech]

Thanks Gavin.

Here is the formula based on your suggestion:

[tt]
=Data!B2 & " as of " & IF(AG37="Enrolled",TEXT(Data!U2,"MMM DD, YYYY"),Data!S2) & IF(AG37="Enrolled"," (Census)","")
[/tt]