Text and date in same cell

 
Joseph, thanks for the twitter question!

 

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.

 

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.

 

 

photo credit: Theen … via photo pin cc

Share Button

Oz du Soleil

I am Oz du Soleil. Data Mercenary and Excel Trainer. My courses have been described as informative, fun and they get people to relax about using Excel. I'm based in Chicago, a veteran of the U.S. Navy, and have a passion for custom-made hats, good bourbon, and spicy food.
0 comments
Sort: Newest | Oldest
Follow

Get every new post delivered to your Inbox

Join other followers: