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.
720 comments
Need help to Convert 21Jun24 14:46 to JUN24
DATE FORMAT DESIRED FORMAT
21Jun24 14:46 JUN24
Hi! Change the date format as described in this article: How to change Excel date format and create custom formatting.
Hi, I'm trying to convert time which was pulled from a system which has no AM/PM and I'm using this formular but I'm getting an error message.
=LEFT(TEXT(H2,"h:mm:ss AM/PM"),FIND("",TEXT(H2,"h:mm:ss AM/PM"))4)
Hi! Change format to AM/PM if time is written in cell H2. Here are the instructions to do this: Excel time format: 12/24 hour, custom, default.
Before changing format, if time in H2 is written as text, convert text to time as described here: Convert text to time using TIMEVALUE function.
I hope it’ll be helpful. If something is still unclear, please feel free to ask.
Hi , would need help on this:
how to convert "5/29/2024 0:00" to " 29/05/2024 " ?
Encountered a problem when wanna apply =datavalue, it came out as " #VALUE! "
Appreciate if you could help on this, thank you and have a nice day ;)
Hi! Extract the desired data from the text string and create a date using the DATE function.
You can find the examples and detailed instructions here: Excel substring: how to extract text from cell.
Based on the information provided, the formula could look like this:
=DATE(MID(A2,SEARCH("/",A2,4)+1,4), LEFT(A2,SEARCH("/",A2)1), MID(A2,SEARCH("/",A2,4)2,2))
I recommend paying attention to the Text to Date tool. This tool easily converts text strings of different formats to dates. The tool is included in the Ultimate Suite for Excel and can be used in a free trial to see how it works.
How can i format 22.05.2024 to 20240522?
Hi! You can find the answer to your question in this article: How to change Excel date format and create custom formatting.
I would like to say a BIG BIG Thank you for the information on this website
For me, EXAMPLE 2: “Convert Complex Text Strings to Dates”, helped me tremendously.
Hallo Team,
Many thanks for the article and indicating that datevalue won't work with any delimited other than "/". I was using German Regional Format, which has "." as the date delimiter and Mar2023 used to datevalue was throwing error. Now I have a solution either to change settings to English or use substitute function in case the cell referred to has "." in it.
I find your articles very useful and descriptive.
This formula =SUMIF(AN31:AR31, ">TODAY()", AN30:AR30) always results in ZERO (0) even though row 31 has dates in date format and row 30 has numerical format numbers. Putting the actual TODAY date in place of the TODAY function gives the correct result. Can someone tell me why my formula doesn't work with the function criteria.
Hi! You've written the condition incorrectly. Look for the example formulas here: How to use Excel SUMIF with dates. Based on the information provided, the formula could look like this:
=SUMIF(AN31:AR31, ">"&TODAY(), AN30:AR30)
I am having trouble converting multiple dates from 8 dates to format of yyyy/mm/dd. I tried the formula =DATE(RIGHT(E5,4), MID(E5,3,2), LEFT(E5,2)) I tried applying formula to multiple cells (E5E133 & F5F133) with no success. Please help.
Hi! I can't check your formula, as I don't see any example of the data you want to convert to date.
Here are some examples of the data I am trying to convert from 8 digits to yyyy/mm/dd. I have tried the formula listed above from this site and it only allowing me to input one cell on the column. Is there a formula to convert one cell and apply formula to all 8 digit dates listed in column?
Example:
20240201 20240202
20240315 20240315
20240314 20240314
20240313 20240313
20231016 20231016
20240313 20240313
20230831 20230831
My apologies, added two columns on previous example. Here is one column:
20240201
20240315
20240314
20240313
20231016
20240313
20230831
Everytime I try to use the Formula =DATE(LEFT(C4,4), MID(C4,5,2), RIGHT(C4,2)) in attempts to change 20240201 to 20210201 with no success as Error (circular reference) keeps popping up and then once I click OK, the date that gets inputted is 19000101.
Is there something Im missing and is there a way to convert a whole column as the "Text to Column" section does not work properly on my PC/Excel.
Hi! If cell C4 contains only the number 20240201, the formula works correctly if you enter it in D4, for example. I don't have all your data, and I can't guess why you are getting the error. Maybe this article will be helpful: Circular reference in Excel: how to find, enable, use, or remove.
Also, here is the article that may be helpful to you: How to split cells in Excel using Text to Columns.
Use the formula for the first number and then copy it down the column.
Hi! From your description, it is difficult to fully understand your task. If more than one 8digit date is written in the same cell, you must split every 8 digits into separate cells as described in these articles: Split string by delimiter or pattern, separate text and numbers or TEXTSPLIT function in Excel: split text strings by delimiter. Then use the formula from the article above to convert the 8 digits to a date.
Good afternoon all,
I am trying to create a dynamic spreadsheet that changes as I change a specific field's date.
I would like a number of cells to reference this cell's date within their name. eg, "Number of projects created since 03/03/24". However the formula that I have tried to use below only gives me the date's numerical reference.
="Number of projects created since "&J3&"."  The formula I am using
"Number of projects created since 45354."  What the formula gives me
How do I make this number show up as a date instead?
Thank you.
Billy
Hello! As we have written many times on this blog, a date in Excel is stored as a number. To insert a date into text, convert the date to text using the TEXT function. Use the custom date format that you need. For more information, please visit: How to convert date to text in Excel with TEXT function and without formulas.
For example:
="Number of projects created since "&TEXT(J3,"mm/dd/yyyy")
Very helpful. All of the conversion information saves time!
Hi,
I need to convert a date from 102 (1st of February) and 1104 (11st of April) of the year 2021 to 01.02.2021 and 11.04.2021 (tt.mm.yyyy)
both dates are in one column.
Would be grate if you could help
Thank you
Hi! There are many examples of solving your problem in the comments below. Extract the required day and month numbers using substring functions and use them in the DATE function. Set a custom date format in the cell that you need.
=DATE(2024,RIGHT(A1,2),LEFT(A1,LEN(A1)2))
Hi! I have a data set that has format of 202203. So yyyymm
It shows an exclamation of text stored as number.
I convert to number.
I tried using =Date(Left(AN2, 4), Mid(AN2, 5, 2))  excel says not enough arguments. I'm assuming b/c I'm not trying to extract a day.
How do I convert this to 03/2022? My ultimate goal is to find time between 2 dates like this that are in 2 separate columns.
Thanks!
Hi! A date is a day. Therefore, add a day to the DATE formula. For example,
=Date(Left(AN2, 4), Mid(AN2, 5, 2), 1)
03/2022 is not a date, but text. You can show the date 01/03/2022 as 03/2022 by using the custom date format mm/yyyy.
Thank you for the quick reply! I used the suggested formula and it returned 1/0/1900?
Hi! I wrote this formula based on the description you provided in your original comment. The formula works correctly with the data you wrote: the number 202203, which is stored as text in cell AN2. This text may contain spaces or other characters you did not mention.
I am getting the 202203 value from an index/match formula.
It looks like if I just ignore the error that this value is a number stored as text, then apply your suggested formula it returns the date of 3/1/2022. Which is perfect so I'll just go that route :)
Hello,
I have the date 1987 Apr that i am trying to convert to EOMonth mm/dd/yyyy. Please help
Hello! I assume that your date is written as text. Use substring functions to extract the required text strings for the DATE function.
Try this formula:
=DATE(LEFT(A1,4),MONTH(1&RIGHT(A1,3)),1)
Hi! How can I convert YYYYMM to MM/YYYY format? Please help
Hi! If you have a date written in your cell, use a custom date format "MM/YYYY"
If it is text, use the LEFT and RIGHT functions to change the order of the characters:
=RIGHT(A1,2)&"/"&LEFT(A1,4)
Hello,
I used Oracle and exported a .csv file. This is how I got it:
August 01, 2023 14:00:00
Now I cannot sort it from old to new and vice versa, so what can be done to make it in DATE format? I have done a lot but unable to achieve anything.
I managed it. I made a text file from the .csv and then reimported the data into the new .xlsx file.
Hi! I recommend paying attention to the Text to Date tool. This tool easily converts text strings of different formats to dates. The tool is included in the Ultimate Suite for Excel and can be used in a free trial to see how it works.
How would I convert a date formatted as 1st Mar 23 7:50AM, into 01/03/23, Thanks.
Hi! To get a date from text, extract the year month and day from the text. Then use the DATE function to get the date. You can extract the year month and day from text using substring functions, and you can also use the TEXTSPLIT and CHOOSECOLS functions. TEXTSPLIT will divide the text into parts by spaces, and CHOOSECOLS will help you select the part of the text where the year, month or day is written.
=DATE(20&CHOOSECOLS(TEXTSPLIT(A1," ",),3), MONTH(1&CHOOSECOLS(TEXTSPLIT(A1," ",),2)), LEFT(LEFT(A1,SEARCH(" ",A1)1),LEN(LEFT(A1,SEARCH(" ",A1)1))2))
or
=DATE(20&MID(A1,SEARCH(" ",A1,6)+1,2), MONTH(1&MID(A1,SEARCH(" ",A1,3)+1,3)), LEFT(LEFT(A1,SEARCH(" ",A1)1),LEN(LEFT(A1,SEARCH(" ",A1)1))2))
I recommend paying attention to the Text to Date tool. This tool easily converts text strings of different formats to dates in one click. The tool is included in the Ultimate Suite for Excel and can be used in a free trial to see how it works.
Hi.
Please help me to convert this data ISSUE DATE1102017 into a date format like 01Oct2017. I tried many times but it didn't work.
Thank you
Hi! Extract the date from the text as described in this article: How to extract text after character. Convert this text to a date using the DATEVALUE function as described in the article above. For example:
=DATEVALUE(MID(A1,SEARCH("",A1)+1,50))
Thank you so much
how to change this date format December 14, 2023 to 14/12/2023
Hi! We have a special tutorial that can help to solve your problem: How to change Excel date format and create custom formatting. Date format dd/mm/yyyy.
how to convert 29/12/2023 at 06:08:50.000 PM into 20211231T09:23:06.369Z
Hi, I am trying to convert text to date/time so I can use a filter to put them in chronological order. As the dates/times are transactions they are in the following format: DD/MM/YYYY HH:MM:SS and I cannot seem to convert them and filtering via text does not work correctly. I would also like this format to be set in a template that can be downloaded and used by other colleagues so whatever is input in that column can be filtered in the same way, even if it is copy/pasted, as opposed to everyone having to use a formula each time. Is this possible?
Hi! To convert text to date and time, I recommend using math operations (multiply by 1) or Paste Special (add zero) as described in the article above.
Hi,
I was trying to convert this 10012023 to 10/01/2023, i tried this formula, =TEXT(DATE(RIGHT(T4,4),LEFT(T4,1),MID(T4,2,2)),"MM/DD/YYYY") but the result is this 01012023.
Thank you for the help
Hi! Please pay attention which characters you extract using the LEFT and MID functions. Try the formula:
=TEXT(DATE(RIGHT(T4,4),LEFT(T4,2),MID(T4,4,2)),"MM/DD/YYYY")
You can use the formula
=DATE(RIGHT(T4,4),LEFT(T4,2),MID(T4,4,2))
Apply the date format "MM/DD/YYYYYY".
26/07/202115/09/2021 need to change it to
07/26/202109/15/2021 the above formula is giving wrong answer
Hi! Your data is not dates, but a text string. You can extract the start and end dates using the LEFT, MID, and RIGHT functions. Then, convert them to actual dates using DATEVALUE.
=DATEVALUE(LEFT(A1, 10))
=DATEVALUE(RIGHT(A1, 10))
=DATEVALUE(MID(A1, 12, 10))
I can also offer a solution without the formulas.
If you prefer a nonformula approach, you can use the Text to Columns wizard:
Select the cell with the date range.
Go to the “Data” tab and click “Text to Columns.”
Choose the delimiter () and specify the format for the resulting column (e.g., “Date”).
Click “Finish” to convert the text dates to two actual dates.
HI,
This is awesome. Thank you very much. i have my date imported in this format ~40464%, please how do i convert it.
Hi! I can't tell you anything as I don't know what result you want to get after converting.
Hi Dev Team,
I have the data like this :
Sep 26, 1989
But I apply the date with this formulae:
=DATE(YEAR(cell),MONTH(cell),DAY(cell)).
It shows me error. PLease suggest me
Hi! If I understand correctly, your date is written as text. Use substring functions to extract separate parts of the date from the text string. Try this formula:
=DATE(RIGHT(A1,4), MONTH(1&LEFT(A1,3)), MID(A1,5,2))
I recommend paying attention to the Text to Date tool. This tool easily converts text strings of different formats to dates. 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.
I will really pay the attention on this.
Thanks 1000 times.
2023/10/09 08:36:51
Hi, I am struggling to put this date and time format in general or text format that still displays as per above that it can be utilized in a pivot table.
If I put it in general format it displays as
45208 3589236111
Hi! To write the date as text in the desired format, use the TEXT function as recommended in these instructions: How to convert date to text in Excel with TEXT function and without formulas. For example:
=TEXT(A1,"yyyy/mm/dd hh:mm:ss")
GMT
2022_121:00:00:00.000
2022_122:00:00:00.000
2022_123:00:00:00.000
2022_126:00:00:00.000
I need to convert the above GMT time to a time usable by excel.
The above is what is entered into the cell by the data pull. I need to pull a time delta between the value, but i need to convert into something usable by excel.
THanks
Hi! Give an example of what result you want when converting text to a date. What does 2022_121:00:00:00:00.000 mean ? You need to extract the year, month and day from this text using these guidelines: Excel substring functions to extract text from cell. Then use them in the DATE function to get the date.
Hi can any one share that how we can add text in my date in once cell showing e.g (Date as of : 03Oct23)
Hi! If I understand your task correctly, the following tutorial should help: How to insert today date & current time as unchangeable time stamp. The second option is to use VBA.
Wow this is fantastic info! What formula would convert Aug182023 to a date format? All the dates have a three letter month, two number day, and four number year. (I don't want to use the Text to Columns wizard.)
Hello! Use substring functions to extract specific parts of text and use them in the DATE function. Try this formula:
=DATE(RIGHT(D1,4),MONTH(1&LEFT(D1,3)),MID(D1,4,2))
I'm trying to change this 20160808T13:00:00.000Z to 20160808 in excel
Hi! Extract the first 10 characters from the text string using LEFT function.
=LEFT(A1,10)
Svetlana Cheusheva
I cannot thank you enough.
You are my Hero!
I need help to convert date 07012002 from below
20020701
Hi! Pay attention to the following paragraph of the article above: How to convert 8digit number to date in Excel.
Need to convert YYMMDDHHMM to DDMMYY format. Can someone help
Hi! If you are talking about changing the date format, use these instructions: How to change Excel date format and create custom formatting. If this is not what you wanted, please describe the problem in more detail.
Nice formual to use in excel. Thanks.
Dear Svetlana, Alexander,
Unless I misread and missed an answer to my issue in your post and other solutions, would you be able to give a hint how to deal with the following situation?
I am working on a Dutch machine and receive dates hardcoded in the csv file as "21MAR2024". Data import GUI offers "DMY" option how you suggest, but my Excel is unable to read the result. It would it read it had it "21MRT2024" written been.
Without installing another language pack, writing a macro or substituting "MAR" with "MRT", is there any other way to force Excel to recognize the correct date in my case?
Regards,
Hi! Excel in dates uses your computer's local settings. So the best choice, I think, would be substituting.
Dear Alexander,
Thank you for a quick answer!
I have a date that shows as 18/11/2015 10:04:38 AM that is generated from an external database. The number format is General. I have tried several tips you suggested above to convert to a date format i can use for my pivot table, but none works. Can you help?
Hi! To convert the text date and time to the normal date and time, I recommend trying 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.
how to covert Friday, March 03, 2023 4:42:12 PM to 3/3/2023 16:42
Hi!
I recommend paying attention to the Text to Date tool. This tool easily converts text strings of different formats to dates. 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.
Thank you much. You helped me a lot!
if Feb'23 then result should be 1Feb2023 and in next cell 28Feb2023 (ie. start date and end date); if its Jan23Mar23 it should be 1Jan2023 and 31Mar2023. Please help me with this
Hi!
I think you will find the EOMONTH function useful, which get date of the last day of month.
Please use the formula below:
=EOMONTH(A1,1)+1
=EOMONTH(A1,0)
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.
Please help!!!!
Hi!
If I understand your task correctly, try the following formula:
=IF(ISNUMBER(C2),DATE(YEAR(C2),DAY(C2),MONTH(C2)),DATE(MID(C2,LEN(C2)3,4),MID(C2,LEN(C2)6,2),LEFT(C2,LEN(C2)8)))
I'd recommend you to have a look at our Text to Date tool. This simple tool effortlessly converts text strings of different formats into dates without formulas. 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.
Hello. Is there a way to translate a sequence of numbers in to date and time. As an example, to convert 2303252000 to 25/03/2023 20:00. This format is used by ICAO on NOTAM YYMMDDhhmm.
Hello!
Use the MID function to extract the necessary digits to create the date and time. Use the DATE and TIME functions to do this.
=DATE(MID(A1,1,2),MID(A1,3,2),MID(A1,5,2))+TIME(MID(A1,7,2),MID(A1,9,2),0)
For more information, please visit: Excel MID function – extract text from the middle of a string and Excel DATE function with formula examples to calculate dates.
Hello. Thank you very much for your reply. It was very helpfull! I used the following formula (with ; rather , (excel2007 user :( ) and adding 20& for 2000+ year and it worked
=DATE((20&MID(A1;1;2));MID(A1;3;2);MID(A1;5;2))+TIME(MID(A1;7;2);MID(A1;9;2);0)
Thank you very much again!
Hello,
Im trying to insert data from MSSQL with SSIS into Excel. In MSSQL date is DATETIME or DATETIME2, everything looks good until insert into excel when excel formats it by itself to DATE without TIME in the rows and date with time is shown only in formula box. How to set Excel so it will show exactly as im importing from database with DATETIME in rows and it is DATE type in excel also with time in rows?
Thanks for your help.
Hello!
Set the cells to the date and time format you want. Use these guidelines: How to change Excel date format and create custom formatting. I hope this will help.
Hi. I have a problem that I can not solve. I have a Serial Number of equipment i.e 1Z6A08655AT. I need to take out from it the production date  these is represented by two first characters 1Z. 1 stands for 2021, Z stands for December.
First character indicating Year
1 is 2021,
2 is 2022,
3 is 2023
...
Second represent months:
1,2,3,4,5,6,7,8,9, X,Y,Z
I need to have outcome in mmmyy format
No idea how to deal with it. Appreciate if you could share how to fix this ;)
Hi!
Use the MATCH function to find a match for the month character.
=MATCH(MID(A1,2,1), {"1","2","3","4","5","6","7","8","9","X","Y","Z"}, 0)
To get the year, use this formula
LEFT(A1,1)+2020
Use the DATE function to get the production date.
=DATE(LEFT(A1,1)+2020, MATCH(MID(A1,2,1), {"1","2","3","4","5","6","7","8","9","X","Y","Z"},0),1)
Hope this is what you need.
This was awesome. I just coincidentally had the same challenge and this helped massively. Thank you