Sometimes you need to convert a date from the Excel cell saved format to a particular format. There are at least five ways to do that. DateValue(), Date() or converting Unix dates to Excel. Simply typing dates into a cell also works but there are traps for the unwary.
Excel’s trap when 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 OR the wrong 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 cases.
DateValue()
Now let’s look at ways to convert the text in one cell into an Excel date.
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.
Date()
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:
6/15/2017 22:33:49
needs something like this:
=DATE(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)))
Text to Date with Paste Special
Another way to convert text to an Excel date is via Paste Special. It’s a cute trick but has the same limitations as DateValue().
Details are at Text to Excel Date conversion by adding Zero with Paste Special
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
=(((<unixdatetimecell>/60)/60)/24)+DATE(1970,1,1)
Or if you like obscure and hard to understand formulas …
=(<unixdatetimecell>/86400)+25569
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.
Converting an Excel date to text
Finally, the reverse, converting an Excel date into a specific text format. Useful for setting up data for mail merges or reports.
Microsoft makes that easy with the Text() function. Use it to convert an Excel date into a specific format like this;
=TEXT(A2,"dd-mmm-yy")
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.
Text to Excel Date conversion by adding Zero with Paste Special
Simple text with day, month and year to Excel date conversion
Converting Text with month and year into Excel dates
