The tutorial explains how to use Excel functions to convert text to date and number to date, and how to turn text strings into dates in a nonformula way. You will also learn how to quickly change a number to date format.
Since Excel is not the only application you work with, sometimes you'll find yourself working with dates imported in an Excel worksheet from a .csv file or another external source. When that happens, chances are the dates will export as text entries. Even though they look like dates, Excel won't not recognize them as such.
There are many ways to convert text to date in Excel and this tutorial aims to cover them all, so that you can choose a texttodate conversion technique most suitable for your data format and your preference for a formula or nonformula way.
How to distinguish normal Excel dates from "text dates"
When importing data into Excel, there is often a problem with date formatting. The imported entries may look like normal Excel dates to you, but they don't behave like dates. Microsoft Excel treats such entries as text, meaning you cannot sort your table by date properly, nor can you use those "text dates" in formulas, PivotTables, charts or any other Excel tool that recognizes dates.
There are a few signs that can help you determine whether a given entry is a date or a text value.
Dates  Text values 


How to convert number to date in Excel
Since all Excel functions that change text to date return a number as a result, let's have a closer look at converting numbers to dates first.
As you probably know, Excel stores dates and times as serial numbers and it is only a cell's formatting that forces a number to be displayed as a date. For example, 1Jan1900 is stored as number 1, 2Jan1900 is stored as 2, and 1Jan2015 is stored as 42005. For more information on how Excel stores dates and times, please see Excel date format.
When calculating dates in Excel, the result returned by different date functions is often a serial number representing a date. To convert such serial number to date in Excel, all you have to do is change the cell formatting. For this, select a cell or a range of cells with the numbers you want to convert to dates and press Ctrl+1 to open the Format Cells dialog. On the Number tab, choose Date, select the desired date format under Type and click OK.
Yep, it's that easy! If you want something more sophisticated than predefined Excel date formats, please see how to create a custom date format in Excel.
If some stubborn number refuses to change to a date, check out Excel date format not working  troubleshooting tips.
How to convert 8digit number to date in Excel
It's a very common situation when a date is input as an 8digit number like 10032016, and you need to convert it into a date value that Excel can recognize (10/03/2016). In this case, simply changing the cell format to Date won't work  you will get ########## as the result.
To convert such a number to date, you will have to use the DATE function in combination with RIGHT, LEFT and MID functions. Unfortunately, it is not possible to make a universal formula that will work in all scenarios because the original number can be input in a variety of different formats. For example:
Number  Format  Date 
10032016  ddmmyyyy  10Mar2016 
20160310  yyyymmdd  
20161003  yyyyddmm 
Anyway, I will try to explain the general approach to converting such numbers to dates and provide a few formula examples.
For starters, remember the order of the Excel Date function arguments:
So, what you need to do is extract a year, month and date from the original number and supply them as the corresponding arguments to the Date function.
For example, let's see how you can convert number 10032016 (stored in cell A1) to date 3/10/2016.
 Extract the year. It's the last 4 digits, so we use the RIGHT function to pick the last 4 characters: RIGHT(A1, 4).
 Extract the month. It's the 3^{rd} and 4^{th} digits, so we employ the MID function to get them MID(A1, 3, 2). Where 3 (second argument) is the start number, and 2 (third argument) is the number of characters to extract.
 Extract the day. It's the first 2 digits, so we have the LEFT function to return the first 2 characters: LEFT(A2,2).
Finally, embed the above ingredients into the Date function, and you get a formula to convert number to date in Excel:
=DATE(RIGHT(A1,4), MID(A1,3,2), LEFT(A1,2))
The following screenshot demonstrates this and a couple more formulas in action:
Please pay attention to the last formula in the above screenshot (row 6). The original numberdate (161003) contains only 2 chars representing a year (16). So, to get the year of 2016, we concatenate 20 and 16 using the following formula: 20&LEFT(A6,2). If you don't do this, the Date function will return 1916 by default, which is a bit weird as if Microsoft still lived in the 20^{th} century :)
How to convert text to date in Excel
When you spot text dates in your Excel file, most likely you would want to convert those text strings to normal Excel dates so that you can refer to them in your formulas to perform various calculations. And as is often the case in Excel, there are a few ways to tackle the task.
Excel DATEVALUE function  change text to date
The DATEVALUE function in Excel converts a date in the text format to a serial number that Excel recognizes as a date.
The syntax of Excel's DATEVALUE is very straightforward:
=DATEVALUE(date_text)
So, the formula to convert a text value to date is as simple as =DATEVALUE(A1)
, where A1 is a cell with a date stored as a text string.
Because the Excel DATEVALUE function converts a text date to a serial number, you will have to make that number look like a date by applying the Date format to it, as we discussed a moment ago.
The following screenshots demonstrates a few Excel DATEVALUE formulas in action:
Excel DATEVALUE function  things to remember
When converting a text string to a date using the DATEVALUE function, please keep in mind that:
 Time information in text strings is ignored, as you can see in rows 6 and 8 above. To convert text values containing both dates and times, use the VALUE function.
 If the year is omitted in a text date, Excel's DATEVALUE will pick the current year from your computer's system clock, as demonstrated in row 4 above.
 Since Microsoft Excel stores dates since January 1, 1900 , the use of the Excel DATEVALUE function on earlier dates will result in the #VALUE! error.
 The DATEVALUE function cannot convert a numeric value to date, nor can it process a text string that looks like a number, for that you will need to use the Excel VALUE function, and this is exactly what we are going to discuss next.
Excel VALUE function  convert a text string to date
Compared to DATEVALUE, the Excel VALUE function is more versatile. It can convert any text string that looks like a date or number into a number, which you can easily change to a date format of your choosing.
The syntax of the VALUE function is as follows:
=VALUE(text)
Where text
is a text string or reference to a cell containing the text you want to convert to number.
The Excel VALUE function can process both date and time, the latter is converted to a decimal portion, as you can see in row 6 in the following screenshot:
Mathematical operations to convert text to dates
Apart from using specific Excel functions such as VALUE and DATEVALUE, you can perform a simple mathematical operation to force Excel to do a texttodate conversion for you. The required condition is that an operation should not change the date's value (serial number). Sounds a bit tricky? The following examples will make things easy!
Assuming that your text date is in cell A1, you can use any of the following formulas, and then apply the Date format to the cell:
 Addition:
=A1 + 0
 Multiplication:
=A1 * 1
 Division:
=A1 / 1
 Double negation:
=A1
As you can see in the above screenshot, mathematical operations can convert dates (rows 2 and 4), times (row 6) as well as numbers formatted as text (row 8). Sometimes the result is even displayed as a date automatically, and you don't have to bother about changing the cell format.
How to convert text strings with custom delimiters to dates
If your text dates contain some delimiter other than a forward slash (/) or dash (), Excel functions won't be able to recognize them as dates and return the #VALUE! error.
To fix this, you can run Excel's Find and Replace tool to replace your delimiter with a slash (/), all in one go:
 Select all the text strings you want to convert to dates.
 Press Ctrl+H to open the Find and Replace dialog box.
 Enter your custom separator (a dot in this example) in the Find what field, and a slash in the Replace with
 Click the Replace All
Now, the DATEVALUE or VALUE function should have no problem with converting the text strings to dates. In the same manner, you can fix dates containing any other delimiter, e.g. a space or a backward slash.
If you prefer a formula solution, you can use Excel's SUBSTITUTE function instead of Replace All to switch your delimiters to slashes.
Assuming the text strings are in column A, a SUBSTITUTE formula may look as follows:
=SUBSTITUTE(A1, ".", "/")
Where A1 is a text date and "." is the delimiter your strings are separated with.
Now, let's embed this SUBSTITUTE function into the VALUE formula:
=VALUE(SUBSTITUTE(A1, ".", "/"))
And have the text strings converted to dates, all with a single formula.
As you see, the Excel DATEVALUE and VALUE functions are quite powerful, but both have their limits. For example, if you are trying to convert complex text strings like Thursday, January 01, 2015, neither function could help. Luckily, there is a nonformula solution that can handle this task and the next section explains the detailed steps.
Text to Columns wizard  formulafree way to covert text to date
If you are a nonformula user type, a longstanding Excel feature called Text To Columns will come in handy. It can cope with simple text dates demonstrated in Example 1 as well as multipart text strings shown in Example 2.
Example 1. Converting simple text strings to dates
If the text strings you want to convert to dates look like any of the following:
 1.1.2015
 1.2015
 01 01 2015
 2015/1/1
You don't really need formulas, nor exporting or importing anything. All it takes is 5 quick steps.
In this example, we will be converting text strings like 01 01 2015 (day, month and year are separated with spaces) to dates.
 In your Excel worksheet, select a column of text entries you want to convert to dates.
 Switch to the Data tab, Data Tools group, and click Text to Columns.
 In step 1 of the Convert Text to Columns Wizard, select Delimited and click Next.
 In step 2 of the wizard, uncheck all delimiter boxes and click Next.
 In the final step, select Date under Column data format, choose the format corresponding to your dates, and click Finish.
In this example, we are converting the text dates formatted as "01 02 2015" (month day year), so we select MDY from the drop down box.
Now, Excel recognizes your text strings as dates, automatically converts them to your default date format and displays rightaligned in the cells. You can change the date format in the usual way via the Format Cells dialog.
Example 2. Converting complex text strings to dates
If your dates are represented by multipart text strings, such as:
 Thursday, January 01, 2015
 January 01, 2015 3 PM
You will have to put a bit more effort and use both the Text to Columns wizard and Excel DATE function.
 Select all text strings to be converted to dates.
 Click the Text to Columns button on the Data tab, Data Tools group.
 On step 1 of the Convert Text to Columns Wizard, select Delimited and click Next.
 On step 2 of the wizard, select the delimiters your text strings contain.
For example, if you are converting strings separated by commas and spaces, like "Thursday, January 01, 2015", you should choose both delimiters  Comma and Space.
It also makes sense to select the "Treat consecutive delimiters as one" option to ignore extra spaces, if your data has any.
And finally, have a look at the Data preview window and verify if the text strings are split to columns correctly, then click Next.
 On step 3 of the wizard, make sure all columns in the Data Preview section have the General format. If they don't, click on a column and select General under the Column data format options.
Note. Do not choose the Date format for any column because each column contains only one component, so Excel won't be able to understand this is a date.
If you don't need some column, click on it and select Do not import column (skip).
If you don't want to overwrite the original data, specify where the columns should be inserted  enter the address for the top left cell in the Destination field.
When done, click the Finish button.
As you see in the screenshot above, we are skipping the first column with the days of the week, splitting the other data into 3 columns (in the General format) and inserting these columns beginning from cell C2.
The following screenshot shows the result, with the original data in column A and the split data in columns C, D and E.
 Finally, you have to combine the date parts together by using a DATE formula. The syntax of the Excel DATE function is selfexplanatory:
=DATE(year, month, day)
In our case,
year
is in column E andday
is in column D, no problem with these.It's not so easy with
month
because it is text while the DATE function needs a number. Luckily, Microsoft Excel provides a special MONTH function that can change a month's name to a month's number:=MONTH(serial_number)
For the MONTH function to understand it deals with a date, we put it like this:
=MONTH(1&C2)
Where C2 contains the name of the month, January in our case. "1&" is added to concatenate a date (1 January) so that the MONTH function can convert it to the corresponding month number.
And now, let's embed the MONTH function into the
month
; argument of our DATE formula:=DATE(F2,MONTH(1&D2),E2)
And voila, our complex text strings are successfully converted to dates:
Quick conversion of text dates using Paste Special
To quickly convert a range of simple text strings to dates, you can use the following trick.
 Copy any empty cell (select it and press Ctrl+C).
 Select the range with text values you want to convert to dates.
 Rightclick the selection, click Paste Special, and select Add in the Paste Special dialog box:
 Click OK to complete the conversion and close the dialog.
What you have just done is tell Excel to add a zero (empty cell) to your text dates. To be able to do this, Excel converts a text string to a number, and since adding a zero does not change the value, you get exactly what you wanted  the date's serial number. As usual, you change a number to the date format by using the Format Cells dialog.
To learn more about the Paste Special feature, please see How to use Paste Special in Excel
Fixing text dates with twodigit years
The modern versions of Microsoft Excel are smart enough to spot some obvious errors in your data, or better say, what Excel considers an error. When this happens, you will see an error indicator (a small green triangle) in the upperleft corner of the cell and when you select the cell, an exclamation mark appears:
Clicking the exclamation mark will display a few options relevant to your data. In case of a 2digit year, Excel will ask if you want to convert it to 19XX or 20XX.
If you have multiple entries of this type, you can fix them all in one fell swoop  select all the cells with errors, then click on the exclamation mark and select the appropriate option.
How to turn on Error Checking in Excel
Usually, Error Checking is enabled in Excel by default. To make sure, click File > Options > Formulas, scroll down to the Error Checking section and verify if the following options are checked:
 Enable background error checking under Error Checking;
 Cells containing years represented as 2 digits under Error checking rules.
This is how you convert text to date in Excel and change dates to text. Hopefully, you have been able to find a technique to your liking. In the next article, we will tackle the opposite task and explore different ways of converting Excel dates to text strings. I thank you for reading and hope to see you next week.
Hi,
I have a column with 'Apr 7, 2016 10:39 AM' type data in it. How do I convert this text into a date/time format so I can use it to do further calculations? I.e Find the difference in time elapsed between 'Apr 7, 2016 10:39 AM and Apr 14, 2016 09:21 AM'?
I've tried text to column (which I don' want to do in order to protect the data source). Is there a formula for this?
Hi, Izzy,
activate the cell with a text you want to transform, then under the Home tab find Number format box, and choose Long Date or Short Date to your liking. If it already says Date – your data is ready for calculations.
Leading 0 issue
my formula works for dates with no leading zero
=DATE(RIGHT(E2,4), LEFT(E2,2), MID(E2,3,2))
10021980 10/02/80 > worked
03301980 09/01/82
Good morning!
my date data is in custom data type '00000000'
changing it to string loses the leading zero and using the formula
=DATE(RIGHT(E3,4), LEFT(E3,2), MID(E3,3,2))
also does not work with the leading zero. any thoughts?
example below from my worksheet
10021980 10/02/80 (great)
03301980 09/01/82 (not good)
Hello, Joshua,
could you send us your sample workbook with the data and the result you expect to get to support@ablebits.com? It's a bit difficult to help you right now, since we need to take a look at the way your data is formatted and arranged.
one way around is
=DATE(RIGHT(E3,4), LEFT(E3,len(e3)6), MID(E3,3,len(e35)))
this way it can change the spot it starts reading the number from depending on the length of the number string since it doesn't see the lead 0
Hi,
I am using Google sheet. I have a column in which the date is entered as text "Apr 21,2017  Apr 25,2017". Now, I need your help to convert this to 04/21/2017  04/25/2017.
Please help me, if possible.
Thank you in advance.
all failed
i had to retype dates
best way
Thanks for the help, great job!
Please help me, If I have in a cell this: 201506, and I need to have Jun2015, How should I do?
hi team,
i am trying to convert excel date format from 5/29/2017 to 20170529 but not working. can anyone give me a hand?
Thanks.
Hi,
I have a bunch of date/time stamps that look like this:
1497541289
1497541291
1497541294
1497541297
1497541299
1497541300
How do I convert them to real dates & times?
Thanks,
Pete
Hi, Pete,
could you please specify what are these values stand for?
It's just the very last date in Excel (December 31, 9999) is stored under the 2958465 number. The stamps you provided simply can't represent the integers of the dates for Excel.
So, for us to help you better, please specify what these values should transform into exactly.
Thanks!
please how do i convert 25/5/17 "twentyfifth may twenty seventeen" to a format excel recognizes? thank you
this is awesome. thank you!
you're awesome
How to convert below date format into "DD/MM/YYYY HH24:MI:SS"
Jul 1, Sat 2017 6:30:01:236
Jul 1, Sat 2017 6:30:01:330
Jul 1, Sat 2017 6:30:01:421
Jul 1, Sat 2017 6:30:01:564
Jul 1, Sat 2017 6:30:01:678
Jul 1, Sat 2017 6:30:42:303
Please assist.
Hi,
Good Day!
How to convert complete no of entries in ss from 20170703 12:47:47 IST to 20170703 12:47:47, i need to remove text(IST) in all the date entries.
Love, love, love your site. It helped me so much. So easy and fast finding help. I will recommend to everyone. Thank you
8.6.2017
8.6.2017
dd.mm.year
calculate the days with current today date
Hello,
if I understand your task correctly, you will need to use DATE function. You will find the instructions on how to work with the function on this blog post.
If you need an instant solution, you can try our Date & Time Wizard from our collection of addins for Excel  Ultimate Suite. You can download its trial version from this web page.
Very good solution. Converted the below format date into standard format by following the above steps. Thanks alot.
Feb 1, 2012 2:39:39 PM AST
hey i want to convert date into next date..
Example: 1/4/2015= 2/4/2015
15/4/2015=16/4/2015
Hey,
as far as I can see, you just need to add one day.
This topic explains how to that :)
how do i convert: "september 10, 2017" and "march 12, 2016" to dd/mm/yyyy?
Hi!
By using the TEXT function (the result will be a text string:): =TEXT(A1, "dd/mm/yyyy")
Or, by applying the custom format (the result is a date): dd/mm/yyyy
Here's my problem:
=CONCATENATE("Campaign Performance"," ",+Procedure!D1)
which results in:
Campaign Performance 43021
When I want:
Campaign Performance 10/13/2017
+Procedure!D1 is a formatted date field
Anybody have any ideas?
Hello,
I would like to sort below date format but it is not formatted as DATE. How do i change this to a DATE format so I can sort in chronological order? thank you in advance.
Jan 01, 2016
Mar 10, 2016
Feb 11, 2016
Feb 07, 2016
Jan 22, 2016
Feb 12, 2016
Jul 07, 2016
Jun 27, 2016
Jul 07, 2016
Jul 02, 2016
Jul 17, 2016
Jul 16, 2016
May 29, 2016
Dec 06, 2016
Feb 17, 2017
Jul 16, 2016
Jul 12, 2016
Feb 08, 2017
Feb 16, 2017
Jun 14, 2016
Hi there,
You can call me Padman. I find your page is different than others in the net. I managed to learn a bit here.
My problem is unique to me, haven't seen the solution here or in any other sites.
I downloaded a bunch of data, one of them is date  "1/13/2017 8:10:00 AM" (left aligned in the cell), when I try to format that, I get "#VALUE!" error, when I use the formula "=DATEVALUE(TEXT(B__,"mm/dd/yyyy"))". For some data, like "1/12/2017 10:50:00 PM", it's converted to "12Jan2017". Need your help to resolve and get the dates converted.
I'll post more appreciation once my issue gets resolved. Message me if there's any question. Thanks
Padman
I want to restrict user to use date format in mm/dd/yyyy. However when i send the excel to user. He can enter date in mmddyyyy. Can you please tell what can we use in data validation to restrict user to only use mm/dd/yyyy
Hello, Gaurav,
Please try using a VBA macro. We are always ready to help you, but we do not cover the programming area (VBArelated questions).
You may try to find the solution in VBA sections on mrexcel.com or excelforum.com.
Sorry we can't assist you better.
If E day is 25 December 2017 and I have a column that is E + or  the number of days from E day, how do I make the next column show the calendar date from the E day
.
Hi, I'm trying to convert 11/9/2017 to a date but when I charge the format it comes up as #########. How do I convert it to a date?
Thanks!