Dates play an important role when we need to analyze trends. This is one of the most important things which you need to capture in the right way. Apart from the basic date format which is used by Excel to store dates. There are some other data formats that are acceptable.
Now the thing is: When we capture a date in the wrong format Excel treats that date as a text and, then we can’t use that date further in any calculation.
When it comes to textual data formats there is no one particular thing. It depends on the person who is entering those dates. The bottom line is: We do need some methods which we can use to convert a text to an actual date with the right format.
So today, in this post, I’d like to share with you 10 methods that we can use to convert a text to date format in Excel. For converting a text to date we need to use a combination of different functions.
So let’s get started.
1. Convert a Text to a Date
Using the DATEVALUE function is a basic method to convert a date into an actual date which is stored as text.
Let’s say you have a list of dates in your worksheet and all the cells where dates are entered have text format but all those dates are in the correct date format.
In the below example, we have a date in a cell but when I try to add a number to that date I’m getting an error, even though the format of the data is correct.
But if you look at the cell format, it’s text. So here, is the best way to use the DATEVALUE function. You just need to refer to that cell in the function and it will convert that data into actual date.
And, then you can change its format to date.
Note: There are some situations where DATEVALUE can’t help us to get a date from a text. There is n number of irregular formats of dates and to deal with all those we need to create a custom formula using different functions.
In the next 10 examples, I will share with you how can we use these different functions.
2. Date with Back Slashes
The Excel date system supports dates with forwarding slashes. And, if we have a date where we have backslashes, it will treat it as a text.
To solve this problem we can use the below formula.
=DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))
3. Date with Month Name
Now, we have a date format where the month is entered with its name. For Excel, this is not a date anymore, even if it’s presenting a date clearly.
The below formula can help us with this.
=DATEVALUE(RIGHT(A3,2)&"-"&TEXT(MID(A3,6,3),"MMM")&"-"&LEFT(A3,4))
4. Date with Dots
It’s a common kind you the problem where we get dots inside a date. This is a kind of format used by people who are not aware that this is not a proper date format.
And, this is the formula to correct it.
=DATE(RIGHT(A4,4),MID(A4,4,2),LEFT(A4,2))
5. Date with the Month Name and a Comma
This format is almost the same as the format we discussed in point 3.
And, we can correct it with the below formula.
=DATEVALUE(LEFT(A5,2)&"-"&TEXT(MID(A5,4,3),"MMM")&"-"&RIGHT(A5,4))
6. Date with the Day Name
Sometimes people store a date with the day’s name. And, if that date is not in a proper format Excel will treat it as text.
Use this formula for this type of text date.
=DATEVALUE(MID(A6,FIND(",",A6)+6,2)&"-"&MID(A6,FIND(",",A6)+2,3)&"-"&RIGHT(A6,4))
7. Date with a Day Name in the End
Here we have a date with the day name at the end. Now, the problem with this format is it has a comma between the date and day name.
But, we can get the valid date using the below formula.
=DATEVALUE(MID(A7,10,2)&"-"&MID(A7,6,3)&"-"&LEFT(A7,4))
8. Date Having a Suffix with Day
We have a “th” suffix with the day number and it makes it a text instead of a date as Excel is not able to recognize it.
The below formula will help you to get the real date.
=DATEVALUE(LEFT(A8,FIND("th",A8)-1)&"-"&MID(A8,FIND(" ",A8)+1,3)&"-"&RIGHT(A8,4))
9. Date with Space Between Day, Month, and Year
When we have a date format with space between day, month, and year, we can use the below formula to correct the date format.
=DATE(RIGHT(A9,4),MID(A9,4,2),LEFT(A9,2))
10. Date with Short Month Name
With a date format where we have a short month name at the start of the date.
We can use the following formula to convert it to a valid date format.
=DATEVALUE(MID(A10,5,2)&"-"&LEFT(A10,3)&"-"&RIGHT(A10,4))
11. Date Without Any Space Between Day, Month, and Year
And for a date where there is no space between day, month, and year.
The below formula will help us to get the correct date format.
=DATE(RIGHT(A11,2),MID(A11,3,2),LEFT(A11,2))
Hi Puneet,
I need a practice file for making practice.
Regards
jas
how do I convert this text into a Standard Date or even a number so I can sort by date properly:
Thursday, July 20, 2023
‘Jan’17
‘Feb’17
‘Jan’18
‘Feb’18
‘Jan’19
‘Feb’19
in above data, 17, 18 & 19 are the years. how can i convert this format to MMM-YY?
Aug 12, 2022 THIS DATE IS NOT WORKING I WANT IT IN THIS FORMAT 12-08-2022 OR 12-AUG-2022
I need some quick help please. I have a huge reference data file that has the date available as
“Jun 27, 2022” and the master file that I am using has dates as “21-Feb-22″. How to get the dates in the reference data file in the same format (Master File date format can’t be changed as there are linkages and formulas”
Help – the suffix date format only considers the case of “th” suffix – not working in case of suffixed dates like “st” (as in 1st, 21st, 31st), “rd” (as 2nd, 22nd) and “rd” (as in 3rd, 23rd)
HELP! There is nothing to convert TEXT date of “2022-Jan” to an actual date. I have many reports with charts and I am always needing to retype dates for each. Its hundreds of text dates for about 20-30 charts. There doesn’t seem to be any solution found online. I would love to know how to fix this with a simple formula. But all the date help assumes there is a month, day, and year. I only have text showing year-month. I’d love suggestions, as I am required to get this done so fast, retyping is taking too long.
George, do you want to convert 2022-Jan into 1-Jan-2022?
Hi, thanks for you sharing.
But when I use it, it shows “Function DATE parameter 2 expects number values. But ‘4/’ is a text and cannot be coerced to a number.” I don’t know why.
11. Date Without Any Space Between Day, Month and Year
it doesn’t work at all, if the month is October. like 16102015, this is not working.
if some data is 16.12.15, how can we convert to 16/12/2015? Please help I struggle with this.
Hi Puneet
I have data in a cell in the format: EDM 101, POL 102, POL 301 and I want it this way:
EDM 101
POL 102
POL 301 That means I want them in a separate cells and not a single cell.
How to calculate the number of days between two dates.
Thank you for your response.
What formula would I use if my data is in this format: 20170913 (its the reverse of #11).
Hello Puneet ,
This tip is really helped me .
Now i can do my work easily ,
Thanks for sharing and keep posting these things.
Good article, I needed same, but including hours….
I have this value in excel “7/31/2019 1:57:00 AM” cell. I need the number of hours elapsed from that time to today().
any help is greatly appreciated
HI
Would you please tell me how to convert the Excel dates below to the Russian Format YYYY.MM.DD
NOTE with dots between YYYY MM DD
The format seems different
when the Month number is greater than 12 !!
thank you in advance
best regards
Derek
========================================
04/01/2019
04/02/2019
04/03/2019
04/04/2019
04/05/2019
04/08/2019
04/09/2019
04/10/2019
04/11/2019
04/12/2019
04/15/2019
04/16/2019
04/17/2019
04/18/2019
04/23/2019
04/24/2019
04/25/2019
04/26/2019
04/29/2019
04/30/2019
05/01/2019
05/02/2019
05/03/2019
05/07/2019
05/08/2019
05/09/2019
05/10/2019
05/13/2019
05/14/2019
05/15/2019
05/16/2019
05/17/2019
05/20/2019
05/21/2019
05/22/2019
05/23/2019
05/24/2019
05/28/2019
05/29/2019
05/30/2019
05/31/2019
06/03/2019
06/04/2019
06/05/2019
06/06/2019
06/07/2019
06/10/2019
06/11/2019
06/12/2019
06/13/2019
06/14/2019
06/17/2019
06/18/2019
06/19/2019
06/20/2019
06/21/2019
06/24/2019
06/25/2019
06/26/2019
06/27/2019
06/28/2019
07/01/2019
07/02/2019
07/03/2019
07/04/2019
07/05/2019
07/08/2019
07/09/2019
07/10/2019
07/11/2019
07/12/2019
07/15/2019
07/16/2019
07/17/2019
07/18/2019
07/19/2019
07/22/2019
07/23/2019
07/24/2019
07/25/2019
07/26/2019
07/29/2019
07/30/2019
07/31/2019
08/01/2019
08/02/2019
08/05/2019
08/06/2019
08/07/2019
08/08/2019
08/09/2019
Would you please tell me how to convert the dates below to the Russian Format YYYY.MM.DD
thank you in advance
best regards
Derek
5 wasn’t working for me with single digit dates either, so I modified the formula to check if the third character of the cell was blank and if so use an appropriate version of the formula, and if not, assume it’s double digit as normal and use the original.
=IF(MID(H10,3,1)” “,DATEVALUE(LEFT(H10,1)&”-“&TEXT(MID(H10,3,3),”MMM”)&”-“&RIGHT(H10,4)),DATEVALUE(LEFT(H10,2)&”-“&TEXT(MID(H10,4,3),”MMM”)&”-“&RIGHT(H10,4)))
That should read
=IF(MID(H10,3,1)” “,DATEVALUE(LEFT(H10,1)&”-“&TEXT(MID(H10,3,3),”MMM”)&”-“&RIGHT(H10,4)),DATEVALUE(LEFT(H10,2)&”-“&TEXT(MID(H10,4,3),”MMM”)&”-“&RIGHT(H10,4)))
Tap
Hi, I need to cut and paste rows on same worksheet to the last row but before that I need to have 2 criteria met. For example, a value in col B and a range of numbers like from 300 to 500 in any cell of col E. Could anyone help me please.. Thanks a lot!!
Sir, 13th may,2019.
Very useful tips and tricks you have given.
Hope to receive such tips in future too.
thanking you.
I remain.
I create a column of 1’s and then use copy / paste special / multiply to convert text to a real date.
Dear all,
Does anyone know how to solve the problem of converting or formatting the long date (NOT a date format) to date format? I tried using text-to-column but it doesn’t help. I also tried applying your given formula as below, but this is good for 2-digit day e.g. Tuesday, Dec 16, 2015. What about for single-digit day e.g. Monday, Mar 4, 2019? I could just change the “…+6,2 to …+6,1 or….+5,2” but I can only use 1 formula in the same column which contains many dates. Sorry, I am seeking for help as I am very new to excel. I guess somewhere within the formula below I could add in something but don’t know how. Thank you!
=DATEVALUE(MID(A6,FIND(“,”,A6)+6,2)&”-“&MID(A6,FIND(“,”,A6)+2,3)&”-“&RIGHT(A6,4))
Hi,
I tried to copy your formula on Point-6 regarding Date with Day Name. However, this seems to work only for 2-digit day. As a file I received contains text format date with combination of single and double digit day. Could you pls advise how to include 1-digit day? e.g. Friday, Mar 8, 2019
=DATEVALUE(MID(A6,FIND(“,”,A6)+6,2)&”-“&MID(A6,FIND(“,”,A6)+2,3)&”-“&RIGHT(A6,4))
Thank you in advance!!
🙂 Thanks
My favourite method for getting excel to recognize a date is found within the Text to Column function. If you click through delimited, etc you can then choose ‘Date’ and tell excel to recognize the numbers as a date. Especially useful if you get data with all of the dates in D/M/Y as if you choose that, excel will recognize the first number as the day, and then the dates will be in appropriate M/D/Y structure.
Selecting problem dates (using the wrong symbol such as or . as a separator) and using Text to Columns (on the Data tab) will often sort out date problems.
In the first step, choose delimited, in the next step choose a symbol that’s not being used in the date, in the third step choose Date and the order of day, month, year for your dates. Change the destination if you don’t want to overwrite the original entries and click Finish
Thanks Paul
Excellent tip. Saved my day!