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. For example, if =TODAY()+7 returns a number like 44286 instead of the date that is 7 days after today, that does not mean the formula is wrong. Simply, the cell format is set to General or Text while it should be Date.
To convert such serial number to date, all you have to do is change the cell number format. For this, simply pick Date in the Number Format box on the Home tab.
To apply a format other than default, then select the cells with serial numbers 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 :)
Note. The formulas demonstrated in this example work correctly as long as all numbers you want to convert to dates follow the same pattern.
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:
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:
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.
Note. For the Text to Column wizard to work correctly, all of your text strings should be formatted identically. For example, if some of your entries are formatted like day/month/year format while others are month/day/year, you would get incorrect results.
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.
How to change text to date in Excel an easy way
As you see, converting text to date in Excel is far from being a trivial oneclick operation. If you are confused by all different use cases and formulas, let me show you a quick and straightforward way.
Install our Ultimate Suite (a free trial version can be downloaded here), switch to the Ablebits Tools tab (2 new tabs containing 70+ awesome tools will be added to your Excel!) and find the Text to Date button:
To convert textdates to normal dates, here's what you do:
 Select the cells with text strings and click the Text to Date button.
 Specify the date order (days, months and years) in the selected cells.
 Choose whether to include or not include time in the converted dates.
 Click Convert.
That's it! The results of conversion will appear in the adjacent column, your source data will be preserved. If something goes wrong, you can simply delete the results and try again with a different date order.
Tip. If you chose to convert times as well as dates, but the time units are missing in the results, be sure to apply a number format that shows both the date and time values. For more info, please see How to create custom date and time formats.
If you are curious to learn more about this wonderful tool, please check out its home page: Text to Date for Excel.
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.
658 comments
Hi,
I have extracted data from my netbank and receive the dates in decimal numbers, such as;
Date
0,150497685
0,108831019
0,067164352
0,858136574
How can I concert this to dates?
Hi!
I have no idea what result you want to get, so I recommend this article: How to convert number to date in Excel.
Hi,
I have dates in the format 02122022 and I want to convert them to 02Dec2022. I am using the below formula but it does not see to work after day 12th.
=IF(ISNUMBER(C2),DATE(YEAR(C2),DAY(C2),MONTH(C2)),DATE(MID(C2,LEN(C2)3,4),LEFT(C2,LEN(C2)8),MID(C2,LEN(C2)6,2)))
If I apply this formula to 12122022 it gives me 12Dec2022 but as soon as the day value changes to 13 i.e, 13122022 it gives 12Jan2023.
Can you please help me?
Hi Team,
Can someone please respond here?
By reading the comments I am sure you can help. I exported a report from a tool, the report has a date column but the column has two different date formats, one format interprets the date correctly, but the other format interprets the day as the month, so even all data is from January from example, when I filter the date what should be Jan 1 Jan 12 shows as Jan 1st, Feb 1st, march 1st and so on, how can I correct that, Thank you in advance.
Hi!
Your tool receives data from two different sources in different formats. Set up the instrument so that there is one format. Or set the date format in Excel manually.
I found a solution.
Hi i have an issue, i got sent a table that has Date column as 18Oct, 18Nov, 18Dec, 19Jan, 19Feb, 19March but excel reads it as 10/18/2023 but the column is actually suppose to read October 2018 ... Jan 2019, so this is affecting my work, and have tried what i know, the result i get is NAME ERROR
I have the below text structure downloaded from a dataset that I need to convert into a date format that SPSS or Jamovi can use. I do not need the day of the week, just a numeric MM/DD/YYYY. I've tried converting by formatting the cells as dates, using the flash fill tool, and using =DATEVALUE, but none worked (no change at all). I will ultimately have thousands of entries; so, manual conversion is not feasible! Any ideas on how I can convert? The original date selection was made using a calendar tool where one selects a specific date and the text date is produced in the .csv download, if that's helpful.
Mon Dec 27 2021
Thank you!
Hello!
Use substring functions to extract the desired parts of the text for the DATE function. We have a more universal and simple solution that will solve the problem in a couple of clicks. I recommend paying attention to the Text to Date tool. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
=DATE(RIGHT(D1,4), MONTH(1&MID(D1,5,3)), MID(D1,9,2))
Thanks so much! I'll give these recommendations a try.
Hey there! I am trying to show a range of dates  "Number of Days"  that calls back to two other dates outside the print area. But the two dates keep showing up as text.
I am using the following super basic formula. =C9&" ("&C11&"  "&C10&")"
C11 and C10 are my two dates that show up as text. I want them to show up s mm/dd/yyyy. Can you help a newb out?
Hello!
You can convert the date to text in the format you need using the TEXT function. See examples at the link above. I hope it’ll be helpful.
Hi, I've got
"Tue Nov 29 2022 15:50:53 GMT0600 (Central Standard Time)" and would like it formatted into: "MM YYYY"
Any ideas are greatly appreciated! Thank you!
Hi,
You can get the result as a text or as a date. Apply the date format of your choice to the date.
=MONTH("1"&MID(A1,5,3))&" "&MID(A1,12,4)
=DATE(MID(A1,12,4),MONTH("1"&MID(A1,5,3)),1)
We have a tool that can solve your task in a couple of clicks  Text to Date tool. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
Hi, hope you can help
I have the following "Apr 3, 2021 3:54am" and want to convert it to UK Date format using a formula  03/04/21
I know I can use the text to columns function but there are multiple columns like this so would be helpful if there was a formula, I tried looking above at the other solutions but could not see one like mine
Thanks in advance
Hello!
To convert your text to a date, use this formula.
=DATE(MID(A1,FIND("~",SUBSTITUTE(A1," ","~",2),1)+1,4), VLOOKUP(LEFT(A1,3), {"JAN",1;"FEB",2;"MAR",3;"APR",4;"May",5;"JUN",6;"JUL",7;"AUG",8;"Sep",9;"OCT",10;"NOV",11;"DEC",12},2,0), MID(A1,FIND(" ",A1,1)+1,SEARCH(",",A1,1)5))
In one click, the text is converted to a date using the Text to Date tool. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
Here's a "neat" method to convert an 8digit number into a valid dd/mm/yyyy date:
(Don't forget to format the result as "short date" 😎 )
if cell A1 contains: 20220512
then this formula: =TEXT(A1,"0000\/00\/00")+0
will yield the date: 12/05/2022
This is, of course, much simpler method than your solution, explained above:
=DATE(RIGHT(A1,4), MID(A1,3,2), LEFT(A1,2))
Enjoy
I'm doing a complicated append formula and when I pull the cell that has the date information it translates it into the Date Value. Is there a way inside of the append formula to have it display the date from the date value? For example, Y2 holds the date displayed as 4/1/2022
My append formula (I'm keeping it simple as there are about 10 other appends in the cell I'm merging everything into) is ="Paid Thru Date: "&Y2&"... "
This formula is displaying: Paid Thru Date: 44652...
I would like it to display: Paid Thru Date: 4/1/2022...
I can't quite figure out how to get it to convert that 44652 back to the 4/1/2022 inside of the appended formula
Hello!
To display the date as text, use the TEXT function when concatenating values.
Please check the formula below, it should work for you:
="Paid Thru Date: "&TEXT(Y2,"m/d/yyyy")
Awesome  Thank you so much!
Hey! :)
I see you explained how to go from an 8 digit number to a date, is it possible to do it the other way around? I need to generate a specific 8digit code for every test I do, and connect it to the specific date. In some instances, I run more than one test a day so I need to add an extra digit at the end. Eg: on 10.11.2022 I run 3 tests and I need to convert the date to 101120221, 101120222 and 101120223, or even more ideally to a six digit + 1 (1011221, 1011222, 1011223)
I would appreciate your help
Hello!
To count the number of matching dates, use the COUNTIF function.
You can convert a date to a number using the TEXT function and the "ddmmyy" format.
=IF(COUNTIF($A$1:$A$100,A1)=1, TEXT(A1,"ddmmyy"), TEXT(A1,"ddmmyy")&COUNTIF($A$1:A1,A1))
Copy this formula down along the column.
Hope this is what you need.
Thank you for your help.
how can I calculate a month like "January" into Days
Hi!
What do you want to calculate exactly? Your question is not entirely clear, please specify.
Trying to convert text to dates: example : 15011749 (old church records from the 1500's to 1900's)
Used this formula, like your example:
=DATE(RIGHT(B14,4),MID(B14,4,2),LEFT(B14,2))
It returned: 15Jan3649
All of the day/months are correct, but all the years are off by 1900 years.
What am I doing wrong? Thank you for your help.
Ann
Hello!
Excel does not work with dates prior to 1900. If the year number is between 0 and 1899 inclusive, Excel calculates the year by adding that number to 1900.
Thank you  I thought I was losing my mind!
Trying to think of a workaround. I can make value combined with the right function to turn the year into a number in a new column, and then just display the m/d in a separate column. Is that the best I can do?
Would any of the date functions in Ablebits help me with this?
I'm taking data from Family Tree Maker for church parishes with birth, marriage and death dates, with other text fields, so I need to be able to play with different sorts to come up with birth rates, causes of death over time, and other statistical info. Lots of calendar math needed. There are between 717k records in each of the 3 parishes.
Once again, thank your for your help,
Ann
Hello!
Extract the year from the date using the YEAR function and write the correct year in a separate cell by subtracting 1900.
YEAR(DATE(RIGHT(B14,4),MID(B14,4,2),LEFT(B14,2)))1900
In A1 i have 1984 B1 i have Apple
A2 I have Jun1990 B2 i have Orange
A3 i have 01May2020 B3 I have Grape
i need to join the text as in C1 =Apple (1984)
C2 =Orange (Jun1990)
C3 = Grape (01May2020)
Used text function its not working for C1.
Please help me in resolving this issue
Hi!
How can I guess which function you used? I assume that you wanted to convert the date to text. Use the TEXT function as described in this article: How to convert date to text in Excel.
If it don’t work for you, then please describe your task in detail.
i have year as 1984 in A1, and 28Feb18 in A2,
if use text to convert it date, 1984 comes as 07Jul1909, A2 comes as 28Feb2018 correctly, but i need it 1984 as 1984 only instead of 07Jul1909.
Hi!
How can I help if you do not answer my questions and the problem description is completely unclear?
Is it possible determine the number of days from a month text such as "August"
Many thanks
Hello!
Convert text to date and determine the first day of the month using the DATEVALUE function. Determine the last day of the month using the EOMONTH function. Find the difference between dates and add 1 day.
Please check the formula below, it should work for you:
=EOMONTH(DATEVALUE(1&A1),0)DATEVALUE(1&A1)+1
Hey,
I need to covert 20200818 (Aug 18  2020) to be 08/18/2020 (mm/dd/yyyy) using a single formula!
Please help!
Hi!
Pay attention to the following paragraph of the article above – How to convert 8digit number to date in Excel.
It covers your case completely.
How could I convert this to date? JAN01/19
I'd be forever grateful!
Hello!
Use the DATE function to convert text to a date 
=DATE(RIGHT(D1,2), MONTH(1&LEFT(D1,3)), MID(D1,4,2))
I have two strings.
#1: 72222 and need it to be 7/22/22
#2: 120121 and need it to be 12/1/21
Thank you!
Hello!
Use substring functions to extract text from cell. Please try the following formula:
=DATE(RIGHT(A2,2), LEFT(A2,LEN(A2)4), MID(A2,LEN(A2)3,2))
hello
i have a set of text 551231024556 in which i would extract the first 6 character to be converted to date value result to be 31121955
any solution to this?
Thank you
Hello!
To extract the first two characters from the text, use the LEFT function. To extract characters from text, use the MID function. Use the DATE function to get the desired date.
The formula below will do the trick for you:
=DATE(LEFT(A1,2),MID(A1,3,2),MID(A1,5,2))
Thank you very much, it works, great.
Hello, Mr. Alexander.. Is there a way to swap the data and month VALUE? Not just the formatting. When I paste date values from a text file or PDF, Excel autointerprets the values incorrectly. I.e. 02032019 is imported as 03rd Feb instead of 02nd Mar. Simply changing the formatting does not fix this problem.
Hello!
When you import dates from a text file, Excel uses your Windows Regional settings. You can change them as described in this article: How to change the default date and time formats in Excel. I hope this will help, otherwise don't hesitate to ask.
I have date formatted field(12/20/1984) in excel, when i am saving data as xml. Date is converted to number. What i need to do in excel to save this as date. I used below custom validation to allow field string(Delete) and date
=OR(AND(ISNUMBER(J3),LEFT(CELL("format",J3),1)="D"),J3="Delete")
Hi!
Excel stores the date as a number. The .xml format does not have a date format. Save the file as an Excel file (.xlsx) and then you can write the date as a date, not as a number.
Final data I need in XML . I am not clear on statement  Save the file as an Excel file (.xlsx) and then you can write the date as a date, not as a number. Can you give me one example.
Hi!
You can store the date as text using the TEXT function. Then in the xml file the date will look like a date, not a number.
Thank You Alex.
How do I convert this date
Jan011985 TO 19850101
Hello!
To convert text to date, extract the desired characters using the substring functions.
=DATE(MID(A1,FIND("~",SUBSTITUTE(A1,"","~",2),1)+1,4), VLOOKUP(LEFT(A1,3), {"JAN",1;"FEB",2;"MAR",3;"APR",4;"May",5;"JUN",6;"JUL",7;"AUG",8;"Sep",9;"OCT",10;"NOV",11;"DEC",12},2,0), MID(A1,FIND("",A1,1)+1,2))
Then use DATE function. Set the cell to the custom date format that you need. For example, "yyyymmdd".
To convert text to date, you can also use the Text to Date tool.
It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
Hi, I have this exact date values in my worksheet:
a. 10/06/2022 8:00:48 p. m. > Type: Date > Format: d/mm/yyyy h:mm:ss AM/PM
b. 10/06/2022 8:00:48 p. m. > Type: General > Format: d/mm/yyyy h:mm:ss AM/PM
When I compare a=b the result is FALSE. I can't convert b. to date but I'm seeing the exact same values.
Any suggestions would be very welcome.
Hello!
Date and time in Excel are numbers. If you set a cell to a custom number format with 10 decimal places, you will see that these numbers are different.
Hi I have this data of date from CSV 20220612T20:30:59.611Z may I know how to convert it to Date & Time in excel?
Hi
Extract the desired strings from the text and convert them to date and time using the DATEVALUE and TIMEVALUE functions
=DATEVALUE(LEFT(A1,10))+TIMEVALUE(MID(A1,12,8))
Set the cell to the date and time format.
Current format: 12 25 20 22
How can I convert this to 12/25/2022??
Hello!
Change text to date format by using the SUBSTITUTE function. Then follow the instructions from the article above.
=DATEVALUE(SUBSTITUTE(SUBSTITUTE(A1," ","",3)," ","/"))
You own a Altroz XM+ 1.2 P car no UK00AA0000;you had serviced your vehicle from OBERAI MOTORS (0000000Sv&PaDehradunOberaiM)3 days ago on 29APR22,which was a Second Free Service. I require your feedback regarding the same. I Need 29APR22 separately from this sentence using a formula in excel. Plz help out.
My formula is not working and not sure what I am doing incorrectly:
Original w/formula needs to be
9122004 12/9/2004 9/12/2004
Here is the formula used:
=DATE(RIGHT(O434,4),MID(O434,2,2),LEFT(O434,1))
Any help is appreciated
Hi!
Your formula returns December 9, 2004. What result would you like to get?
I am looking to get the out put of 9/12/2004
Hi!
Set the date format in the cell to whatever you need. You can learn more about date format in Excel in this article on our blog.
Monday, February 21, 2022
WANT IN 02212022
Hi!
We have a tool that can solve your task in a couple of clicks. Our Text to Date tool allows you to convert text to normal Excel dates.
hello
i typed so many dates
and i suddenly received a combination of numbers and text in the cell, and I need to return the value to date
these are examples of the values I'm receiving
30637514H
310038084H
Thank you! Great help!
HELP? There is no way to convert this "2022FEB" from text to a date. I have 40 charts to build each where, and the exports always put the months/years in text format. I am spending time manually retyping the dates for them to work. Is there no formula to make the date a REAL date in excel, All the methods online wont work for this?
Hello!
Here is the formula that should work perfectly for you:
=DATE(LEFT(A1,4),VLOOKUP(RIGHT(A1,3), {"JAN",1;"FEB",2;"MAR",3;"APR",4;"May",5;"JUN",6;"JUL",7;"AUG",8;"Sep",9;"OCT",10;"NOV",11;"DEC",12}, 2,0),1)
You can learn more about DATE function in Excel in this article on our blog.
Hi! Could someone help me if there is a formula to convert general format 07122021 to a date format of 07Dec2021? I tried changing the format but it doesn’t work. Thank you!
Hello!
Here is the article that may be helpful to you: How to change Excel date format and create custom formatting.
This blog is much helpful. Thanks Ablebits team. Especially Mr. Alexander Trifuntov.
How can I convert a lot code into a date? Lot code is XX02282A which iquals 02/28/2022 which is an expiry date: PPMMDDYS
XX  producing plant code
02  month
28  day
2 for the year
A  producing shift
Hello!
If I got you right, the formula below will help you with your task:
=DATE("202"&MID(A1,7,1),MID(A1,3,2),MID(A1,5,2))
You can learn more about DATE function in Excel in this article on our blog.
I have a column of dates as e.g. 31 Mar, 2022 and have been using the formula =DATE(RIGHT(E4,4), MONTH(DATEVALUE(LEFT(E4,3)&"1")), MID(E4,5,FIND(",",E4)FIND(" ",E4)1)). It's a weekly report that I convert but lately it has been throwing back #VALUE!. Do you have any idea why one week it will work and another it won't. I can't see any change in the original dates including any spaces. Thank you.
Hello!
This formula converts the text "Mar 31, 2022" into a date. To convert the text "31 Mar, 2022" to date, you can use the DATEVALUE function, as described in the article above.
Importing a text file date shows as per left column... desired output in right column.. Any formula recommendations to get the desired result? (issue is that excel drops the zero in front of the first string)
String text Desired Answer
7122021 12/7/2021
25102021 10/25/2021
Hello!
Use Excel substring functions to insert the desired delimiters:
=DATEVALUE(LEFT(A1,LEN(A1)6)&"."&MID(A1,LEN(A1)5,2)&"."&RIGHT(A1,4))
This should solve your task.
Hi guys
Any way to convert 2m 19d (2 months and 19 days...!) to an excel recognised date format?
I realise it is the most ridiculous export, please help!
Hello!
The information you provided is not enough to understand your case and give you any advice. There are a different number of days in a month. What should be the result? March 19, 1900?
I have a similar request for help. My Excel sheet has the length of service written as "1 year 2 months 28 days". Can I write a formula to convert that to total number of days? And then can Excel convert the number of days to determine the exact hire date.
Hello!
Extract numbers from a text string using the substring functions.
=LEFT(A1,2)*365 + MID(A1,SEARCH("month",A1)3,3)*30 + MID(A1,SEARCH("day",A1)3,3)
Also take a look at this guide: Extract number from text string in Excel.
How to convert this text value to date only:
Wed Sep 01 00:07:00 UTC 2021
Hello!
The formula below to solve your task:
=DATE(RIGHT(A1,4),VLOOKUP(MID(A1,5,3), {"JAN",1;"FEB",2;"MAR",3;"APR",4;"May",5;"JUN",6;"JUL",7;"AUG",8;"Sep",9;"OCT",10;"NOV",11;"DEC",12},2,0), MID(A1,9,2))+ TIMEVALUE(MID(A1,12,8))
Hi , I have a question.
Emp ID Name Tenure Year Tenure Month Workout Core Time Productivity Productivity Q2 Productivity Q3 Workout Q2 Workout Q3 Time Worked
20 Pat 2 10 1064.99 893.65 119.17% 119.17% 120.82% 1064.99 1079.71 1008.52
30 Him 5 9 502.15 421.47 119.14% 119.14% 120.82% 502.15 509.22 469
32 Piy 2 7 825.75 697.75 118.34% 118.34% 120.82% 825.75 843.04 926
34 Sak 2 4 939.56 811.97 115.71% 115.71% 120.82% 939.56 981.03 950.6
33 Rav 8 8 917.66 803.18 114.25% 114.25% 120.82% 917.66 970.41 943.38
28 Dee 2 9 861.31 754.28 114.19% 114.22% 120.82% 861.54 911.30 902.3
35 Sam 6 2 907.71 807.12 112.46% 114.22% 120.82% 921.92 975.17 908.45
31 Om 9 0 892.36 797.5 111.89% 114.22% 120.82% 910.94 963.56 938.4
19 Log 5 8 834.71 788.82 105.82% 114.22% 120.82% 900.97 953.01 908.28
Based on this data I want to get maximum and minimum potential benefits from Productivity @ quartile 2 and quartile 3.
Productivity baseline = sum of workout / sum of core time.
Productivity at quartile 2 = total workout at Quartile 2 / core time and
Productivity at quartile 3 = total workout at Quartile 3 / core time and
and productivity variance
can you please help
thanks, Maddy
Hi!
If I understand correctly, your task is to find the sum of the column and divide the numbers. This has nothing to do with the topic of the article. If you have a question about a specific formula, ask it in the required section.
SAMPLE…
DATA A1 6
A2 EA
A3 11/04/21
USED FORMULA: =CONCATENATE("SHORTAGE OF ",ABS(A1),"(",A2,")",", ISSUED ON ",A3," (NO VARIANCE)"
OUTPUT : SHORTAGE OF 6(EA), ISSUED ON 44297 (NO VARIANCE)
How to convert the value format 44297 to date format using the formula.
Hello!
To convert a number to a date in text form, replace 44297 with the formula:
TEXT(44297,"mm/dd/yy")
Thanks for the help... Happy.
formula:
=TEXT(44297;"mm/dd/yy")
results in:
00/dd/yy
Can you pls help?
Hi!
The date in Excel is stored as a number. But you must provide to the TEXT function not a number, but a date. Use the DATE function:
=TEXT(DATE(2021;4;11);"mm/dd/yy")
I hope it’ll be helpful.
Hello,
The date in csv document was exported as the following:
1.57775E+12 (it displays as 1577750400000) in the formula field.
The cells with the dates are in General format.
How can I convert such number in the Date?
All the dates supposed to be Dec.31 of various years.
Thank you very much for your help!
Hi!
I cannot guess what date this number corresponds to.
Hi Alexander, I have a text format like this 15/06/2021 and I want to convert as date 2021/09/22.
Hello!
See the answer in this comment.
You will receive a date that you can write in any custom date format.
I have data from an export that gave me dates formatted as text or general (applying other preformatted date styles has no effect): 960212 (YYMMDD) and I need to convert it to a date in this format: 02/12/1996 (MM/DD/YYYY).
Any help for this?
Ken
Hello!
The formula below will do the trick for you:
=DATE(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2))
You are a genius, thank you so much. Was struggling a lot because of this.
Hi, I would like to convert this date and time string (Nov 15, 2019 02:34 PM) exported from a system to a valid date field in excel so that I can use the =WORKINGDAYS formula. Many thanks Laura
Hello!
How to convert your text to date and time, see this comment.
I hope I answered your question. If you have any other questions, please don’t hesitate to ask.
Hi Alexander, the formula works however, it returns a #VALUE error when the date is a single date eg. 4th rather than 14th of a month.
Jan 4, 2021 09:22 AM returns #VALUE!
Jan 15, 2021 08:27 AM returns 15/01/2021 as expected.
Can you help me identify what in the formula needs to change or if there is a second step I need to complete. The WORKINGDAYS formula works well on the result
Hi!
I wrote this formula based on the description you provided in your original comment. Please note that if you’d provided me with the precise and detailed description of your task from the very beginning, I’d have created a different formula that would have worked for your case.
=DATE(MID(A1,FIND("~",SUBSTITUTE(A1," ","~",2),1)+1,4), VLOOKUP(LEFT(A1,3), {"JAN",1;"FEB",2;"MAR",3;"APR",4;"May",5;"JUN",6;"JUL",7;"AUG",8;"Sep",9;"OCT",10;"NOV",11;"DEC",12},2,0), MID(A1,FIND(" ",A1,1)+1,SEARCH(",",A1,1)5))
Thank you for your help.
Hello all,
I have a question, I get amount of data as a text genereated in .csv file, for e.g. "02NOV18" I would like to convert this as a date in excel format "yyyymmdd". I was trying to use text as a column method, but nothing change, or when I use formula =Value or date.value nothing also change. Someone can help me with that ? Thanks in advance
I WANT TO CONVERT A DATE INTO A DAY EXAMPLE 28/02/1968 IN A GRAPH I WANT TO HAVE ONLY 28 THE LABLE THE THE GRAPH AS BELOW FREBRUARY
Hi!
If I understand your task correctly, here is the article that may be helpful to you: Excel date functions  formula examples.
Hello, my question is how can i convert text to set as date example 12232021 >>> 12/23/2021
Hi!
Read the article above carefully and pay attention to the paragraph: How to convert 8digit numbers to date in Excel.
What is the equivalent function in excel of the "to_date" in Google Sheets?
Thanks in advance!
I need to convert a text string with military time and date to number so that can calculate total time in minutes. Its currently in hhmm ddmmmyyyyy (i.e start 2345 04JUL2020 stop 0231 05JUL2020). I would appreciate any help.
Hello!
Please use the following formula/the formula below to solve your task:
=DATE(RIGHT(A1,4),VLOOKUP(MID(A1,8,3), {"JAN",1;"FEB",2;"MAR",3;"APR",4;"May",5;"JUN",6;"JUL",7;"AUG",8;"Sep",9;"OCT",10;"NOV",11;"DEC",12},2,0), MID(A1,6,2)) + TIME(LEFT(A1,2),MID(A1,3,2),0)
Good Morning Alexander
This formula was just what I needed.
Thanks for the tip.
Mark
I have this text output from an sql query, but I need to convert it to date so it can be sorted by date and time. Apr 14 2021 8:15AM. I would appreciate your advice.
Hello!
I believe the following formula will help you solve your task:
=DATE(MID(A1,FIND("~",SUBSTITUTE(A1," ","~",2),1)+1,4), VLOOKUP(LEFT(A1,3), {"JAN",1;"FEB",2;"MAR",3;"APR",4;"May",5;"JUN",6;"JUL",7;"AUG",8;"Sep",9;"OCT",10;"NOV",11;"DEC",12},2,0), MID(A1,FIND(" ",A1,1)+1,2)) + TIMEVALUE(MID(A1,LEN(A1)6,5)&" "&RIGHT(A1,2))
Set the date and time format in this cell.