Joseph, thanks for the twitter question!QUESTION
Today is 9/12/12
Instead, Excel was showing:
Today is 41164.950103588
Basically: How do I get text and date in same cell? And formatted properly?
The first time I dealt with something like this, it was WTF??
I had created 50 certificates in an Excel-Word mail merge, put the nice expensive paper in the printer and pulled the trigger. The first few printed and I noticed the long bizzaro number where a date should have been. More than half were printed before the job terminated.
Straight into the trash with the first certificates, and then figure out where that number came from.
ANSWER
Dealing with dates in Excel is tricky. For those who don’t know, Excel uses 1 JANUARY 1900 as day #1. So, when Joseph was getting 41164.9501 it’s saying we’re almost on day 41165 days; i.e. 41164 from 1JAN of 1900.
Ordinarily, Excel might assume we want a date. But when there are mixed formats in one cell, all bets are off. Excel delivers raw, unformatted data.
Yup! 41164.9501 instead of 9/12/12
Now we’ve got to be explicit in how we want Excel to treat 41164.9501 and we’ll use the TEXT function …
| Intuitively we’d build | |||
| CELL | Formula | Action | Result |
| A1 | =NOW() | Results in current date and time | 9/12/2012 |
| A3 | =”Today is “&A1 | Adds the text Today is to the result of A1 | Today is 41164 |
| Corrected: Text and date in same cell and properly formatted | |||
| Cell | Formula | Action | Result |
| A1 | =NOW() | Current date and time | 9/12/12 |
| A3 | =”Today is “&TEXT(A1,”mm/dd/yy”) | 1. Adds Today is to the result of A1 2. Converts 41164 into TEXT 9-12-12 |
Today is 9/12/12 |
So, we’re all good, and Joseph is moving forward!
Do you have questions? Send them in and let’s get an answer for you. I don’t know everything but I’ll do what I can to help you get cooperation from your data.
