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

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.

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

#### Oz du Soleil

Oz du Soleil is a Microsoft Excel MVP.   He's an Excel Trainer whose courses have been described as fun and they get people to relax about using Excel. Oz is a U.S. Navy Veteran, and loves good bourbon, and spicy food. Above everything else, Oz's commitment is to clean data.