Warning: mysql_real_escape_string(): Access denied for user 'dragon99'@'localhost' (using password: NO) in /home3/dragon99/public_html/wp-content/plugins/easy-contact-forms/easy-contact-forms-database.php on line 152

Warning: mysql_real_escape_string(): A link to the server could not be established in /home3/dragon99/public_html/wp-content/plugins/easy-contact-forms/easy-contact-forms-database.php on line 152
Text and date in same cell - Oz du Soleil
Joseph, thanks for the twitter question!


Joseph wanted to put NOW() in cell A1 and have another cell show

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.


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.

photo credit: Theen … via photo pin cc