Converting Excel dates to text and vice-versa
Sometimes you need to convert a date from the Excel cell saved format to a particular format.
Microsoft makes that easy with the Text() function. Use it to convert an Excel date into a specific format like this;
The date value in cell A2 is converted into a text string using the formatting string given. Formatting strings are the same as those in the Format | Cell | Custom option.
TEXT() can convert any Excel value into a string using the specified formatting.
Converting Text to Date
Unfortunately, Microsoft hasn’t made converting text into a date anywhere near as easy.
You’ll look in vain for a TEXT2DATE() function where you can give Excel a date/time text string plus a format, and get back an Excel date value.
There is DATEVALUE() which converts text into an Excel date. But it’s not entirely reliable because you can’t specify a ‘mask’ for the text formatting. DateValue generally works if the date text matches the current regional setting. For example DateValue(“3/12/2005”) only works if the Windows Regional setting is English (US) with a short date format of M/d/yyyy .
We’re obliged to Lorelei F for extra information:
DateValue() does work beyond the regional setting in many cases IF the text date uses letters for the month.
“3 Dec 2005” “December 3, 2005″ will be converted by DateValue() but ‘2005 Dec 3” will not.
The month has to use the system set Language. On an English language Windows/Excel a Spanish date like “3 diciembre 2005” won’t be converted.
Instead all you have is
DATE(year,month,day) where you have to laboriously parse a text string into day, month and year segments using LEFT(),RIGHT(),MID() and FIND().
Here’s a real DATE() function at work to convert a text date and time into one that Excel software can understand.
To convert, a <date text string) like:
needs something like this:
RIGHT(TRIM(LEFT(<date text string>,FIND(" ",<date text string>))),4),
LEFT(TRIM(LEFT(<date text string>,FIND(" ",<date text string>))),FIND("/",TRIM(LEFT(<date text string>,FIND(" ",<date text string>))))-1),
MID(TRIM(LEFT(<date text string>,FIND(" ",<date text string>))),FIND("/",TRIM(LEFT(<date text string>,FIND(" ",<date text string>))))+1,2))+
TIMEVALUE(TRIM(RIGHT(<date text string>,FIND(" ",<date text string>)-1)))
This example is especially galling. It’s necessary because Microsoft itself is supplying dates in that text format with the upcoming Stock data type.
Unix Text to Date
If you’re importing date information in Unix format a slightly different approach is needed.
Unix calculates a date as the number of seconds since 1 January 1970.
Sure, you might never use Unix or even know what it is. Importing data into Excel can arrive in a wide range of formats and Unix date format is often used. Our Real Time Excel book shows how to deal with Unix formatting for incoming Weather data.
You need to convert the Unix value into a day value (divide seconds into minutes, then hours then days) then add the Excel data value for 1 Jan 1970 – like this
Or if you like obscure and hard to understand formulas …
Is it a date or just text?
Excel worksheets can go awry quite easily when there’s a mix up between a cell that looks like a date but is actually text that looks like a date.
Have a look at these three cells, at first glance they look like dates but only one is an Excel date.
The middle date is a typo. The bottom date is plain text.
You can see the effect when we try to add 15 days to each of the cell values.
Only the valid Excel date in row 11 can be used in arithmetic.
The trap of typing in dates
When manually typing in dates it’s easy to get trapped. Excel will automatically convert a typed date into an Excel date but only if you type very accurately. One little mistake and you’ll get a mix of Excel dates and text that looks like a date.
How can you tell?
The official way to tell an Excel date cell from text is the Number Format pull down list on the Home tab.
A valid Excel date will have the Date format or a Custom date.
Otherwise, the cell will be some other format like Text or most likely General.
You could create a Conditional Formatting rule using the CELL() function to test for Date or String formats but there’s an easier way.
The General and Text cell formats usually have default right formatting (perhaps left in some languages). The Date format is usually Center or otherwise noticeably different from what’s typed in.
Make sure the Date formatting is noticeably different from text formatting. Most commonly by making the dates center formatted.
Here it’s easy to see there’s something wrong with one of the cells compared to the others.
Automatic Error Checking
Modern Excel has some automatic error checking which may apply to dates.
Two digit years
Excel will warn when you enter a date with only two digits for the year. You get the option to make it either a 20th or 21st Century date.
All the Error Checking options are at Options | Formulas | Error Checking rules. Only one option specifically handles dates ‘Cell containing years represented as two digits’ but others may be triggered in some circumstances.
Office Watch has the latest news and tips about Microsoft Office. Delivered once a week.