Serial dates are the @#$%@ devil

October 16, 2015 10:49 AM

I need Excel to stop reverting my written date into a serial date. It is not cooperating.

I have a column of different dates in an Excel spreadsheet that are displaying thusly: December 15, 2016

I have a column of the same bit of text that I want to concatenate with the date. Text reads: Lease expires

What I want to end up with: Lease expires September 9, 2016

What I'm getting when I concatenate: Lease expires 42719

I have tried every way I can think of to get Excel to stop reverting the damn date back to the serial date. I know it can be done because I've done it before, I just can't remember HOW.

Help please?

posted by Serene Empress Dork to Technology (8 answers total)

Pick "Format Cell" and change it to "Text"?
posted by Lucinda at 10:51 AM on October 16, 2015 [1 favorite]

Have you formatted the column to text?
posted by billiebee at 10:52 AM on October 16, 2015

Best answer: If A1 is "Lease expires" and B1 is your date, put this in C1

=A1 & " " & TEXT(B1,"mmmm dd, yyyy")
posted by desjardins at 10:53 AM on October 16, 2015 [11 favorites]

I was just about to paste almost word for word what desjardins said. Curse my slow fingers...
posted by pipeski at 10:55 AM on October 16, 2015 [2 favorites]

Response by poster: desjardins... THANK YOU!!!! Worked like a charm. I'm printing out your formula and pinning it to my cube wall. This sort of thing comes up just often enough that I can't remember what worked the last time.

My sanity and my blood pressure thank you. :)
posted by Serene Empress Dork at 11:06 AM on October 16, 2015 [1 favorite]

I couldn't remember either but the way I quickly found it was to click the function button to bring up the Insert Function dialog box, and typed in "format date." I knew the first 2 results (DATE and DATEVALUE) were not what you needed so I clicked TEXT and then read the help link for that to make sure I was using the correct formatting.
posted by desjardins at 11:19 AM on October 16, 2015 [1 favorite]

Response by poster: Thanks for describing how you found it, I poked around in the functions but didn't really know what I was looking for.
posted by Serene Empress Dork at 11:50 AM on October 16, 2015

Alternatively, you can force a date to be text by typing it in as text, Eg 'December 31,1999. I needed this when working with non-standard pre-modern dates and wanted, for example to put in Jan 1 1659/1660. (actually, I typed 1659/60-01-01, which nicely alphabetised after '1659-03-03).

But the solution above is better when working with modern standard dates - you have have the date function and the display you want.
posted by jb at 6:53 PM on October 16, 2015