Ronny, a loyal Office Watch reader asks “OK, I now understand that entering dates in Excel as plain text isn’t a good idea. How do I convert my cells with MM/DD text into proper Excel dates?”
It’s a trap for Excel novices. Typing in a date as plain text seems simple and easy … until you want to do date calculations or comparisons. In Ronny’s case he wanted to find the most recent date from a list of text MM/DD cells. That’s easy if the cells were Excel dates, very difficult as text.
In this article we’ll look at converting text dates with two elements (e.g. month/day, day/month, month/year etc). See Simple text with day, month and year to Excel date conversion if your text dates have all three parts (eg ’12 05 2012′, ‘April 5, 2012’ yy/mm/dd, dd/mm/yy etc) etc.
Excel Dates give more choices.
You’re always better off, in the medium / long term, putting dates in cells in Excel format. Once in ‘correctly’ you have all manner of options for calculating / displaying the data. As plain text, your options are limited.
For example, dates in cells can be converted to plain text with your choice of formatting using the TEXT() function see Five ways to turn text into Excel dates . Find the earliest or latest date of a list with the MIN() or MAX() functions.
Convert text dates into Excel dates
Converting your current text dates into Excel dates is a little pain but worth the trouble.
In short:
- Make a backup worksheet before you start <g>.
- Insert a second column next to each current column with text dates.
- In the new column, use the DateValue() or Date() functions to convert the text dates into ‘real’ dates. We’ll look at that in detail below and here
- Make sure the conversion is correct, see below.
- Convert the new calculated dates into fixed values. See Convert Excel formula into fixed values or text
- Delete the original ‘text dates’ column, leaving the Excel date value column in its place.
Convert text dates with DateValue()
The usual advice for converting text to dates is the DateValue() function or Value().
DateValue() or Value() seems simple enough, give it a date in text and it’ll return an Excel date (ie a number).
DateValue() is specific to date only conversion.
Value() is a general function for converting text to Excel values. It can also handle date & time string conversions.
It’s not that simple. DateValue() requires the text date to have the current date format as set in Windows or have the text in an explicit format (eg “3 Mar 2017” or “October 15, 2016”).
Warning: the Windows regional date format settings changes the way DateValue() works. Keep that in mind when you’re trying this yourself because your regional settings will be different from the ones we used. Also be careful you’re sharing a date conversion worksheet with others.
Try DateValue() and Value() with MM/DD text dates and the results are a mixed bag. We’ve highlighted the results which aren’t what you’d expect because Excel thinks the text is MM/YY but others are converted as MM/DD.
With MM/DD dates you need to add a specific year.
Error Checking Warning
Modern Excel tries to help. See the little green boxes on the left of some cells? Excel sees there’s a problem, unfortunately it’s not the real problem.
Excel thinks the problem is the two-digit year and wants to know if it’s in the 20th or 21st century. But that’s not the problem.
The real problem is that it’s a month/day text string.
Date() gives a more accurate conversion
Date() gives a better conversion from text to dates. Yes, it’s more complicated but you get an accurate conversion than DateValue() or Value().
We’ll show you one method here. There are other text to date conversion tricks that we’ll look at in Simple text with day, month and year to Excel date conversion
The function syntax is: Date(Year,Month,Day)
The trick is splitting up the MM/DD text into separate parts
Month or left part
The part to the left of the backslash looks like this:
LEFT(A2,FIND("/",A2)-1)
In other words, Find() the backslash and get its position in the string, subtract 1, the get all the characters to the Left() from the backslash position.
Day or Right part
Getting the part to the right of a backslash looks like this:
RIGHT(A2,LEN(A2)-SEARCH("/",A2))
Search() for the backslash and get the position, subtract that position from the total length of the string – which gives the number of characters after the backslash. Then grab that number of characters from the Right() of the string.
If you’re unsure of these formulas or having a problem, make some temporary columns to try it out for yourself.
Making a full Date()
The whole formula looks like this, where A2 is the date text cell.
=DATE("2018",LEFT(A2,FIND("/",A2)-1),RIGHT(A2,LEN(A2)-SEARCH("/",A2)))
The year is fixed as 2018.
Formatting the Excel dates
Here’s how it looks, we’ve chosen a full dd mmm yyyy format for the results. That’s to make sure the conversion has worked correctly.
To make the new Excel date cells look the same as the original text cells, choose a different cell format at Home | Number | More Cell Formats …
The cell format ‘m/d’ will make the MM/DD cells look the same as they did as plain text.
Or use another cell format like this one ‘d mmm’ which avoids any confusion about months vs days.
Just one example of the flexibility available once the dates are in Excel form.
Options and Variations
Change the date separator: Replace the backslash “/” with “-” , “.”, ” ” (space) or whatever date separator character is used.
Different Date Formats: switch the year, month, day as required, for example if the text date is DD/MM then swap the month and day parts around:
=DATE("2018", RIGHT(A2,LEN(A2)-SEARCH("/",A2)),LEFT(A2,FIND("/",A2)-1))
If the date is MM/YY then try this
=DATE(RIGHT(A2,LEN(A2)-SEARCH("/",A2)),LEFT(A2,FIND("/",A2)-1),"1")
If the date is YY/MM then use:
=DATE(LEFT(A2,FIND("/",A2)-1),RIGHT(A2,LEN(A2)-SEARCH("/",A2)),"1")
When you type in a month/year with no day, Excel assumes it’s the first of the month.
Simple text with day, month and year to Excel date conversion
Convert Excel formula into fixed values or text
Five ways to turn text into Excel dates
