If you import data to Excel from another program chances are the dates will come in formatted as text, which means they’re not much use to you in formulas or PivotTables.
There are many ways to fix dates formatted as text in Excel and the method you choose will depend partly on the format they’re in and partly based on your preference for a formula or non-formula solution.
Tip: You can often tell that a number or date is incorrectly formatted as text because it is aligned to the left. The default alignment for text is left and numbers and dates are aligned right.
Note: since I’m in Australia our dates are formatted dd/mm/yyyy so I’ll be using this format in the following examples.
Jump to Section
1. VALUE Function
Syntax:
=VALUE(text)
Where the ‘text’ is the reference to the cell containing your date text string.
The VALUE function was included in Excel for compatibility with other spreadsheet programs, and I'm glad it was cause it's handy.
It can convert any text string that looks like a number into a number, so it’s useful for fixing any number, not just dates.
Tip: What’s also useful about the VALUE function is it will convert a cell containing date and time that is formatted as text, as you can see in row 7 above where 2014-1-1 8:00 became 41640.3333, which is the Excel serial number for the date and time: 1/01/2014 8:00 AM, whereas the DATEVALUE function, which we’ll look at next, ignores the time portion of the text string.
Notes:- If your text string isn’t in a format recognised by Excel it will return the #VALUE! error.
- Excel will return the serial number for your date, you will then need to format the cell with a date format to make the serial number ‘look’ like a date as I have done in column E above.
2. DATEVALUE Function
Syntax:
=DATEVALUE(date_text)
Where ‘date_text’ is the reference to the cell containing the date text string.
DATEVALUE is very similar to the VALUE function except it only converts a text string that looks like a date to a date serial number. It can’t handle any old text string that happens to look like a number, for that you need the VALUE function.
- Time information in date_text is ignored, as you can see in row 13 above where the result is the date portion only.
- If the year portion of date_text is omitted, DATEVALUE uses the current year from your computer's built-in clock as you can see in row 15 above.
- Using the default date system in Excel for Windows, date_text must represent a date from January 1, 1900, to December 31, 9999. Or, in Excel for the Macintosh, date_text must represent a date from January 1, 1904, to December 31, 9999. DATEVALUE returns the #VALUE! error value if date_text is out of this range.
3. Find & Replace
If your dates have decimal places as delimeters then VALUE and DATEVALUE are of no use to you, for example dates like this:
1.01.2014
This is where Find & Replace is a great option.
Using Find and Replace to replace the decimal with a forward slash converts the text string to an Excel serial number in one fell swoop.
To use Find & Replace:- Select all the dates you want to fix
- Press CTRL+H to open the Find & Replace dialog box<
- Enter a decimal place in the ‘Find what’ field, and a forward slash in the ‘Replace with’ field
- Click ‘Replace All’:
Excel should detect that your text is now a number and format it automatically as a date. If that doesn’t work, you can try the next tool; Text to Columns.
Tip: You can also use Find & Replace to fix date text strings with other delimiters like spaces, or the hyphens we saw in the VALUE and DATEVALUE examples. Just enter a space or hyphen in the ‘Find what’ field instead of the decimal place.
4. Text to Columns
Personally I love Text to Columns. It is one of the most versatile tools for fixing data imported from other systems. I’m going to show you how to fix dates with it now, but I recommend you take some time to play around with the other options it offers.
So, if your dates are formatted in text strings like this:
You can use Text to Columns to quickly reformat them all.
Simply select your dates in column A > Data tab of the ribbon > Text to Columns to open the Wizard:
Step 1 of the Wizard: Choose ‘Delimited’
Step 2 of the Wizard: uncheck all Delimiters (just to be safe)
Step 3 of the Wizard: Choose 'Date' from the 'Column Data Format' options and choose your date format from the drop down list (my dates are YMD), and click the Finish button:
Tip: Note how the ‘Date’ drop down list above has many different combinations of D M Y, you simply choose what order your dates are formatted in from the list.
This means you could have also used Text to Columns to fix the dates we looked at with VALUE and DATEVALUE that had hyphens, or even decimal place delimiters, or for example if your dates are text strings like these:
Jan 1 2014 Jan 2 2014 etc.
You’d simply choose MDY from the drop down list in step 3 of the wizard to fix the dates above. Versatile eh?
Now, while Text to Columns is pretty powerful, it has its limits. For example if your dates are formatted like this:
Wednesday, January, 1, 2014
You need to put in a bit more effort. Here's a tutorial where I use the DATE function and Text to Columns to fix the above date format.
VALUE and SUBSTITUTE Functions
If you prefer a formula solution to the Find & Replace or Text to Columns options above, then you can use the VALUE function with SUBSTITUTE function on stubborn dates that use delimiters like decimals:
The syntax for SUBSTITUTE is:
=SUBSTITUTE(text, old_text, new_text, [instance_num])
So, you can see in the above examples we are using SUBSTITUTE as our ‘text’ argument in the VALUE function to replace the decimals with forward slashes, just like we did with Find & Replace.
The VALUE function then converts the output of SUBSTITUTE to an Excel serial number.
Tip: We have omitted the ‘instance_num’ argument in SUBSTITUTE as we want to replace all of them.
Error Checking
Now, Excel is pretty clever (and I’m not just saying that because I love it 😉 ); it has inbuilt error checking that is always on the lookout for numbers formatted as text.
You can tell if it has spotted an error because it will have a small green triangle in the top left of the cell and if you select the cell an exclamation mark will appear:
Clicking on the exclamation mark will reveal some options relevant to your text:
In this case the dates above only have a 2-digit year so Excel is asking me what format I want to convert it to; 19XX or 20XX.
You can quickly fix all dates using the error checking by selecting all of your cells containing date text strings before clicking on the Exclamation mark on the first selected cell.
Turn on Error Checking
To make sure Error Checking is turned on go to Excel Options > Formulas > and make sure the following options in orange are checked:
So, there you have 6 different ways to convert dates formatted as text in Excel. There are many more ways with formulas, but the above should allow you to handle almost any permutation quickly and easily.
Bonus - Power Query to Fix Dates
The video below runs through 5 common date formatting problems and illustrates how easy it is to fix them with Power Query. The bonus with Power Query is that you can refresh the query to get new data and have it automatically fix those dates too.
Download Workbook
Enter your email address below to download the sample workbook.
Who Needs Word When Excel Has Got Fill Justify Text
Ad Noor
Your writing was clear, and I found it easy to follow your instructions. The fact that you covered multiple solutions, along with their pros and cons, was incredibly insightful.
Mynda Treacy
Glad it was helpful, Ad!
Felicia Walker
I have multiple cells containing general text, for example 200608. The first 4 numbers indicate the year and the last two numbers the month. I would like to convert the number series to read August 2006. Is there a way to do this to a batch at a time. So far I’ve found how to convert serial numbers and dates containing month, day, year but not just year and month. I have over 900 cells to convert.
Mynda Treacy
Hi Felicia,
You can use Power Query, as explained in the post above, or you can use a formula to convert the text to a proper date that is the 1st of each month like so:
=DATE(LEFT(A1,4),RIGHT(A1,2),1)
Then you can format that date with the number format: mmmm yyyy
Mynda
Felicia Walker
Thank you.
Dan McC
Hello, after searching for quite a while to find out why excel was having issues with my date data, I found your website. Thank you for providing an elegant, simple solution You are now bookmarked!
Mynda Treacy
So pleased we could help, Dan!
Ahmed
Thanks a lot
Mynda Treacy
Our pleasure, Ahmed!
Prat Agrawal
Hi,
My excel, 2016 version isnt able to recognise text format. Value function is returning error and ISTEXT function is returnning true value indicating data is in text format. Text to column function isnt working either. Data is in 3/18/2021 5:57:04 PM and i am trying to convert it to yyyy-mm-dd.
I was able to get rid of time using find and replcae. Any suggestion what could be wrong here ?
Mynda Treacy
Hi Prat,
Please post your question on our Excel forum where you can also upload a sample file and we can help you further.
Martin
G’day from Australia 🙂
An SQL report give the dates in test format, which sort alphabetically rather than date & time order which is what I need; some examples below:
Apr 8 2021 4:05PM
Apr 9 2021 8:15AM
Apr 21 2021 4:06PM
Aug 5 2021 3:14PM
Feb 2 2021 4:13PM
Do you have a trick for converting this to date/time format please?
Cheers, and thanks in advance.
Martin
Mynda Treacy
Hi Martin, I recommend you use Power Query to clean the dates. Once loaded to Power Query you can set the data type to Date and then ‘Close & Load’ back to Excel. If you get stuck please post your question on our Excel forum where you can also upload a sample file and we can help you further. Mynda
Udeshi
Hi there,
I have a cell with data “3-9”. When I move that cell with a condition to another sheet, It keeps changing to a date format as “9-Mar”. But I need to move exactly original data.
How can I do it in VBA?
Philip Treacy
Hi Udeshi,
Not sure what you mean by ‘move with a condition’ – are you using an IF function to test the value? You should just need to set the number format of the destination cell to Text. Not sure why you need VBA for this?
If you still have the issue please expand on the problem in detail and provide a workbook in a new post on our forum.
Regards
Phil
M.
Just wanted to say thank you. I now share your love for Text to Columns 🙂
Catalin Bombea
Thanks for feedback, always glad to hear you found it useful!
Hassan
Monday, 2 February 2020
How to convert it in short date?
Mynda Treacy
Hi Hassan,
If it’s a proper date serial number then you can just change the cell format. If not you can use this formula:
=DATEVALUE ( MID ( A1, FIND (“,”, A1) +2, LEN(A1) – FIND(“,”,A1) ) )
Then format as a date.
Mynda
Alex Urgilez
Hi, thank you for the contribution.
I’d like to ask what to do when we have the format:
2015.56 –> which would indicate somewhere in June 2015.
Is there a way to transform this to dd/mm/yy?
Thank you
Mynda Treacy
Hi Alex,
What date in June does 2015.56 translate to? What is the logic? Please post your question and sample Excel file on our forum where we can help you further: Excel forum
Thanks,
Mynda
John Baddeley
Brilliant! Thanks so much, so useful.
Mynda Treacy
So pleased it was helpful, John!
arman
good
Sandy
I just had this issue this wk, #4 was my only resolution out of methods found here and elsewhere. I just have one other challenge, how can do this task in Power Query?
All different methods for this data as text in Power Query have not worked to transform it from text, even with eliminating the auto Changed Type and even splitting columns, adding new column, etc.
I have been teaching myself PQ as source data I work with has many data challenges, all having a large amount of records. I also to tie many different sources of data together, each which have various ways providing common data fields, e.g., City/Country. I’ve been limiting myself to PQ/Excel due to lack of Power BI Pro licensing, which brings me to another question…
If I can do the .csv/date transformation above in Power BI (once finished modeling), can I export from Power BI to Excel (if needed, copy, paste special value the results in a new workbook in Excel)? My thoughts are, I could then build Pivot Tables and visuals in Excel for sharing when needed.
Mynda, you’re invaluable! Love your site and video’s!! You’re very efficient in your approach and delivery, always very informative, practical and succinct! I could go on for days how awesome you are!!
Mynda Treacy
Hi Sandy,
Thanks for your kind words! It’s great to know my tutorials are helpful 🙂
It’s difficult to answer your question about transforming dates in Power Query without seeing an example file, but I wonder if you’ve tried Column From Examples? If that doesn’t work, please post your question on our Excel forum where you can also upload a sample file and we can help you further.
Mynda
Sandy
I did try that as well. Thank you for the suggestions and quick response!! Have a wonderful holiday and take care!
Mynda Treacy
Thanks and you, Sandy!
Ranganath
How to change a date format to an number format, i have a number 5110-9-1 and in the excel it is populating as in date format , i have tried all the options, still no luck.
Philip Treacy
Hi Ranganath
You want 5110-9-1 to appear exactly in that format? Enter it as 511091 then apply the custom number format #-0-0
Regards
Phil
Mohammed Saleh
Marvelous site Step-4 is amazing and very easy to solve if your date data is text or general.
Hats-Off and best wishes from myside.
Mynda Treacy
Great to hear it was useful, Mohammed!
Rajiv Daga
Hi Friend, is there a way we can convert Oct 16, 2020, 1:20:05 AM to 10/16/2020 1:20:05 AM via formula in excel.
Mynda Treacy
Hi Rajiv, it depends if it’s text or not. It looks like it could already be a date/time serial number. In which case you’d simply apply a custom number format: mm/dd/yyyy h:mm:ss AM/PM
If it’s text, then you can use Text to Columns to split the date into two columns, then join them back together with a formula and the custom number format above. It’s tricky to give advice in the comments here. If you get stuck please post your question on our Excel forum where you can also upload a sample file and we can help you further.
Mynda
SteveT
Have just had this problem with dates that had a ‘General’ format in a list of numbers and dates. Made an Excel Table, then a Pivot Table, but could not Group. The entire list was in text imported from another program. Solved by adding a column to the Excel Table with the formula: =A1*1 (date in cell A1) which filled down automatically (love Tables) then copy and pasted as values on top of the errant dates. Reloaded the Pivot Table and all was calm 🙂
Thanks for your website Mynda and Phil; invaluable.
Mynda Treacy
Another easy way to fix dates, Steve! Thanks for sharing 🙂
konni
Hmm… gonna try this.
Another quick-fix method is to copy the data straight into an empty Google-sheet, then copy-paste the data back to Excel.
Google-sheets seems to recognise a data-type, regardless it’s defined as ‘text’ or ‘general’ without much fuss.
@Mynda – Hi, appreciate your website, nice resource. Thanks.
Mynda Treacy
Thanks for sharing your tip, Konni!
Sarah
Hi,
I have been trying to make my date in the excel the same but i cant do it.
For eg: the first and the secont one is 8/1/2020 and some are 1/8/2020 on the same date.
Can you please help me.
Mynda Treacy
Hi Sarah, If you apply a number format to the cell: dd/mm/yyyy then it will format all dates the same. Of course the dates must be entered as proper dates i.e. date serial numbers. If the dates are entered as text then the number formatting can’t be applied. If you’re still stuck please post your question on our Excel forum where you can also upload a sample file and we can help you further.
katy
That solved my problem in under 5 minutes. Thank you!!!!!!
Mynda Treacy
Great to hear it was helpful, Katy!
Khalid
How can change a date 25/06/2002 into 250602 in excel
Mynda Treacy
Hi Khalid,
You can use a custom number format: ddmmyy or if you want to convert the date to text you can use this formula: =TEXT(A1,”ddmmyy”)
Mynda
Lannette L Vaudreuil
I want to convert 08082020 to 08/08/2020 or 8/8/2020
Catalin Bombea
Hi Lannette,
Try:
= DATEVALUE ( LEFT( A1, 2) & “/” & MID(A1, 3, 2) & “/” & RIGHT(A1, 4))
Philip Treacy
Did you try Text to Columns? That works.
Phil
Derek Ong
How to convert 23-Jun-2019 to 2019-06-23?
Mynda Treacy
Hi Derek,
Assuming your dates are correctly entered as date serial numbers you can simply apply a number format: yyyy-mm-dd
Mynda
george papaliberios
HOW TO I CONVERT THIS NUMBER INTO DATE.
171030 WHICH REPRESENTS 2017/10/30
Mynda Treacy
Hi George, you can use method 4: Text to Columns to fix this date.
Mickey
How do you change a date generated by the system as:
Mar-20-2020
Dec-20-2020
to a short date
Excel does not recognize this as a date.
Thank you
Mynda Treacy
Hi Mickey,
Did you try option 4 – Text to Columns? That worked for me.
Mynda
Madison
Okay, let’s see if someone can help me…
The dates in my excel sheet are (examples) 1119 or 819
How can I change these to proper dates without having to insert a dash in each one? Do I need the day?
Philip Treacy
Hi Madison,
If you have 2 digits for the month i.e. 0819 then you can use Text to Columns to convert to dates. You will end up with the 1st day of the month by default e.g. 111901
You’ll need anther column to convert 3 digit myy to mmyy, use this formula
=IF(LEN(A1)<4,"0"&A1,A1)
then Text to Columns the results.
Phil
Lupiya
Good morning, does your response to Omar indicate that one should change to the Country where one resides or is English Australia the universal fix? secondly, is it possible to tell Excel to move the first two digits and swap them with the fourth and fifth values as am alternative to going to the control panel? say 20/02/2020 to 02/20/2020?
Mynda Treacy
Hi Lupiya,
The locale to select is the source data’s locale. In this case it’s English (Australia).
Yes, you can split the column by the forward slash delimited and then join it back together in any order you want, but it’s much easier to just use the Change Type Using Locale settings.
Mynda
Mohideen Khan Ghori
brilliant
solved my problem in a few mins
Mynda Treacy
Glad we could help 🙂
Jaco
Need to change date “Jan 7, 2020, 12:53:49 PM
” to dd/mm/yyyy h:mm:ss” in excel
Mynda Treacy
If your date-time is a date-time serial number then you should be able to apply the format as you’ve entered it above. If it’s not changing through the formatting then you’ll need to convert it from text to a date-time serial number using one of the techniques in the tutorial above.
Omar
While these are the most informative solutions I have found for the problem I am having, these still offer no fix for me.
The problem I’m having is I downloaded a CSV file from my bank for import into quickbooks. The dates came in as text (aligned to the left) as 28/01/2020 however, I need to bulk change all of the dates to mm/dd/yyyy (USA) format. I have done the Text to Column (did not work), =VALUE (gave a value error), =DATEVALUE (gave a value error) and find and replace is toooo tedious. I changed the format of the cells using the the date option under Numbers on the Home Tab, nothing. I copied the cells to Notepad and tried to import from text, nothing.
I’m going crazy. Can some pleaseeeeeeeeeeeeeeeeeeee save me.
Catalin Bombea
In the moment you open the csv, some dates will be wrongly converted: 2 jan 2020 (2/1/2020) will become 1 feb 2020 (1/2/2020), because of the date format differences.
Best is to go to control panel (Regional and language options) and change the date format of your computer to English-Australia instead of English-US.
When you open that file, you will no longer have any problems.
George
I was also facing this issue. Thanks, this solution helped me 🙂
james
You have gotten me out of a massive hole I was digging – many thanks
himanshu mehta
Very well explained.
Thanks for the information man! really appreciated.
Phyllan
HI Catalin,
I have this problem where the date format on my computer affects the datevalue of my excel attachments from outlook.
For e.g. with DD-MM-YY format on my computer, the date value would be 43745 which translates to 7 October 2019. However if I change my date format to MM-DD-YY, the date would be 43656 which translates to 10 July 2019.
In this case, there is no way to know which is the real correct date. What would you suggest for me to be able to get a consistent value no matter which computer I open the file from?
Phyllan
Catalin Bombea
Hi Phyllan,
Dates are indeed difficult to manage. If your system date format is different than the date format from the file, dates will be messed up, because excel converts automatically the dates when you open the file. This means that we have to extract data without opening the files (with power query for example and a few other options), to prevent excel from converting dates, or if you know the date format from the file, set your system to that date format.
For files that are shared across different date formats, it’s best to use an international date format: yyyy-mm-dd
Conor
this saved me today!
One minor thing I noticed – if you’re in Europe and the numbers you want to be dates are in US format e.g. 20180316 you should select YMD at the final date stage of your text to column and then it will see it and put it in European format 16/03/2018 – otherwise won’t work
(vice versa if you were in the US but they were 16032918 you’d need to select DMY)
etc
select the date order the incoming numbers appear to have been in before the format was lost
Catalin Bombea
Hi Conor,
2018-03-16 (YMD) is the international date format, not US format (which is MDY)
Brian
No mention of the Text() function to convert the numeric value to a string? Big omission.
Mynda Treacy
Hi Brian,
I haven’t included TEXT here because this post is about converting data that is already a text/string to numbers, not the other way around. I cover the TEXT function in this postTEXT function in this postTEXT function in this post.
Mynda
Emil
I want to convert this:
5/19/2016 8:18:45 AM
To this:
2016/5/19
But i dont understand how 🙁
Catalin Bombea
Hi Emil,
Is that date in a text string?
Try using: =DATEVALUE(LEFT(E5,10))
Format the cell with this formula as date.
lalit
hi sir ,i have one issue date is appearing as 09/02/2019 and 03-02-2019 in a single column
Catalin Bombea
Hi Lalit,
Is that text or the content is a properly formatted date?
If it’s a date, simply change the entire column format, to have the same format.
If it’s text, just add another step to replace “-” with “/”: Substitute(cell,”-“,”/”)
Pankaj Negi
Dear Sir,
Thanks for sharing Very informative Data.
I have a problem i my system when i open any excel sheet all numbers automatically converted in date format so kindly help on this any by default setting in excel stop numbers convert in date format.
Thank you very much
Mynda Treacy
Hi Pankaj,
I think this is a bug with the ‘Normal’ cell style. On the Home tab in the Cell Styles gallery > right-click on the ‘Normal’ style > Modify > Format > Number Tab make sure the category selected is ‘General’.
Mynda
Jessica C
thank you for providing multiple ways to solve this! the text to columns option worked for me.
Kristin
Tells me to put in date as like todays date 06/08/2019 but comes up 00/08/2019 tried every other way what is wrong
Catalin Bombea
Hi Kristin,
Can you upload a sample file on our forum so we can see what’s wrong?
Umasuthan Navaneethaiyer
Hi,
I got a huge data set in a tab-delimited file that contains some information (nearly 70 entries) that confuses the Excel by imitating dates. For instance, Sept7, Sept2, Sept10, March2, March5, March9 (Note that these are gene names in my study).
When I either open or import this data in(to) excel, excel’s automated date formatting function format these names and provide as follows: 07-Sep, 02-Sep, 10-Sep, 02-Mar, 05-Mar, 09-Mar. I tried many different ways to get rid of this unwanted auto-formatting. But, I was unsuccessful. Could you help me?
Uma
Catalin Bombea
Power Query is the way to go. There is no way to prevent this, each time the excel file is opened, it will try to convert those.
Or, if you create a connection to the text file, using Data>Get External Data>From Text, you can configure the data type for all columns, this will also prevent the automatic transformation.
Kim
Hi, On my excel spreadsheet, I have 2 different columns that are dates formatted X/XX/XXXX. I have used this same spreadsheet for my data for several years now. I opened it this morning and My columns are all in a text format. Instead of the date they all show numbers such as 42572. I have tried re-formatting back to the date but it will not let me. How can I get it to show the date?
Philip Treacy
Hi Kim,
As you probably know, a numbers like 42572 is the date serial number. But even if the data in the cell is text, changing the format to short date or long date should convert it to a date e.g. 42572 is 21/07/2016
When you say you try to re-format back to date “but it won’t let you” – exactly what happens? You get an error message? How will it not let you?
So, hard to say what is happening with you without seeing the workbook.
I don’t see how the data can just change from date to something else.
Regards
Phil
Kristine
Hi Mynda, I’ve exported some information from an LMS (learning management system) which includes the start date and end date for the training programs the individuals have attended. My problem is that the dates are exported to a CSV (that’s ok), format type is General (fine – i can convert it) but its what’s in the cells that has me confused. The dates are coming as a digits for example start date=1477526400 and the end date 147752600. I’ve tried saving the file as an .xls, changing the formats to a date format as well as your formulas mentioned above. How do I convert these to a recognizable date format.
Philip Treacy
Hi Kristine,
The dates look like Unix timestamps. These are the number of seconds since midnight on Jan 1st 1970.
You just need to convert to a date/time serial number using (assuming you are converting data in A1)
=(A1/86400)+DATE(1970,1,1)
and then apply the correct format to display as date/time.
Regards
Phil
Sara
Hi – I am trying to take a date from the format MM/DD/YEAR down to MM/YEAR. I have used the excel function to modify the format, but when I create a pivot chart, it is still recognizing the date as MM/DD/YEAR despite me wanting to roll up to the MM/YEAR level. Anyone have any suggestions on how to fix this issue?
Mynda Treacy
Hi Sara,
Formatting is ignored by the PivotTable. You can group dates into months and years by right-clicking on a cell containing a date in the PivotTable > Group.
Mynda
Dhrian
Hello. Can you help me? i’ve been in this trouble for some times.
So i have “Oxford” in cell A1 and “02 January 2019″ in cell B1 and i want to merge these two cells separated with a comma and single space. I’ve tried =A1&”, “&text(B1,”[$-421]dd mmmm yyyy;@”) and =concatenate(A1,”, “,text(B1,”[$-421]dd mmmm yyyy;@”)) as well as changing “[$-421]dd mmmm yyyy;@” simply into “dd mmmm yyyy” but none of them worked. Do you have any idea what was wrong with my formula?
Thanks alot!
Mynda Treacy
Hi Dhrian,
I’d say your date isn’t a date serial number that Excel recognizes. I suspect it’s text. If you enter this formula:
=A1&”, “&B1 do you get
Oxford, 02 January 2019
If you do then your date is already text and you can’t change its format using the TEXT function. Instead you need to convert your dates to date serial numbers using one of the techniques described in this post.
Mynda
Kate
Hello! I’ve been trying to figure this out for a while now.
Say I enter Nov 09 and I am half expecting that Excel will return the value Nov-09-18, which is the current year. The cell is under the Date format. I used to be able to enter just the first few letters of the month and the day without it converting it to 01/11/2009. However if I enter 09 Nov, it shows the correct date,
Is there any way to fix this?
Catalin Bombea
Hi Kate,
Try changing the date format from your system. From Control Panel -Regional and Language Options-Change date format. Try both English (US) ans English (Australia): first has a date format: mm/dd/yyyy, the second has dd/mm/yyyy as the default date format.
Let us know if that helps.
Regards,
Catalin
kenneth jones
My genealogy list of people and their birthdates in an excel sheet has changed itself to numbers like 37898 and I cannot get my dates back to a readable form dd mm yyyy.
Mynda Treacy
Hi Kenneth,
Sounds like it’s just a cell formatting issue. Have you tried applying the Date format from the Home tab of the ribbon?
Mynda
Scott
Solution 4 work perfectly. Thank you.
Mynda Treacy
Glad I could help!
Adrienn
Thank you very much! Solved my issue with 3. Find and Replace. 🙂
Mynda Treacy
Glad I could help 🙂
June
Thank you very much!! This is very informative!!
Number 4 is awesome!
Mynda Treacy
Glad it was helpful, June 🙂
Faith
Hi there
I would like to convert 20180711 to 17/08/2018, please help.
thank you
Catalin Bombea
Hi Faith,
20180711 is 11/07/2018, why would anyone convert it to a different date?
Hope you meant only format.
Use DATE(year, month, day):
= DATE( LEFT( A1, 4), MID(A1, 5, 2), RIGHT(A1, 2) )
Jo
Thanks for the tips. I am wondering if i can get excel to follow a pattern when the pattern is
2014/15
2015/16
2016/17
when i attemt it it only makes the pattern for the second date e.g.
2014/15
2014/16
2014/17 etc
Thanks
Mynda Treacy
Hi Jo,
No, it won’t follow that pattern. You could split the first year and second year across two columns and then join them back together with the forward slash e.g. if your yyyy is in column A and yy in column B you could use this formula:
Mynda
JR
Number 3 worked for me, thank you very much!!
Mynda Treacy
Glad we could help, JR 🙂
yousuf
hai sir, i have in excel pls help me, im write name in column A that name reflact on column F what that formula pls tell me …
Catalin Bombea
Hi Yousuf,
You want exactly what you type in A1 to be in F1? Simply put this in F1:
=A1
You have to be more precise if it’s something else you want.
Catalin
Arun
This was very helpful. One of the best articles explaining date – text issues in excel. Thanks!
Mynda Treacy
Thanks, Arun! Glad it was useful.
Catalin Alexandru
Hello.
My problem is this:
I have a cell that contains a date like this: 12/4/2018. This cell is formatted to Date.
If I change the format from date to general or to text, the cell will show: 43438
I want to copy firs 2 letters from left, or right…if I aplly the formula ‘=LEFT(A3,2), it will show 43 and not 12.
How can I resolve this problem in excell?
Thanks
Mynda Treacy
Hi Catalin,
In that case you want to extract the month portion of the date so you would use this formula:
=MONTH(A3)
Kind regards,
Mynda
William
As a follow up to Mynda’s response, it is good to note that dates are generally stored as numbers and you only see the date because that cell’s format is a date. Hence when you use a text function like left on the date, it will extract the number of characters from the underlying value.
That said, the rule of thumb is to stick to DATE functions when the value is a proper date, and work with TEXT functions if the value is formatted as a text
Pankaj Negi
Hello Sir,
thanks for sharing information
but i am facing problem when i open any excel all number automatically convert in date format so kindly help me on this case
Mynda Treacy
Hi Pankaj,
That sounds odd. Can I clarify some things; if you enter numbers in an empty file does it convert them to dates? What happens if you enter a negative number?
Perhaps you can post your question on our Excel forum where you can share some screenshots.
Mynda
Annika
Text columns worked awesomely for data with whole numbers – however for the early dates it was extracted as say 8042018 instead of 08042018 – How can I amend this please?
Mynda Treacy
Hi Annika,
Add a column to your source data that adds a zero to the front of the date value where it’s missing:
Then copy and paste this as a value, then use Text to Columns.
Alternatively, you could use Power Query to do the same, which is better if you’re having to repeat this task as new data gets added to your table.
Mynda
Dawood
Wed Jan 24 10:28:14 GMT+00:00 2018
Convert to date:
Assume above text is in Cell A1
= DATE( RIGHT( A1, 4), MONTH( 1& MID(A1, 5, 3) ), DAY( MID(A1, 9, 2) ) )
Result = 2018/01/24
Or
=TIMEVALUE (MID (B17, 12, 8))
Result = 2:39:35 PM
haranesh
Thanks a lot man
Tony
Text to column was a life saver. Thanks!
Mynda Treacy
Glad it helped, Tony!
SWAPNA
hai i am swapna
my problem is in excel cell i want text date string in 2 digit year 21.09.17
i have getting 2 digit year but inside it is showing 21.09.2017
Catalin Bombea
Hi Swapna,
if the cell is formatted as a date, that cell contains in fact a serial number, which is the numeric representation of the date. This number can be formatted in many ways, the number will not change.
If you need text in that cell, format that cell as text, and what you type there is what you will see. he only problem with the date entered as a text string is that you cannot use it in other formulas without converting it again.
The best choice is to format as a date, there are many formats for dates, you can format it as dd.mm.yy. Don’t worry if in the formula bar it is shown as the regional settings date format, this is not something you can change from excel, it can be changed from control panel.
S Ashok Kumar Das
Hello Sir/ Madam,
My problem is different, while i typing 20-7 it is not converted to date as 20-Jul-17, If i type 7-20 it is converted as 20-Jul-17 and inside of cell showing 7/20/2017. This is happening from 4-5 days before it is OK, whether i type 20-7 or 7-20 it will convert to 20-Jul-17 & inside of cell shows 20/7/2017.
I check & make the changes in control panel date format and in excel sheet as well as, but not working it is slowing my work.
I wish you could help me in this regard.
Thanking you,
Ashok Das
Catalin Bombea
Hi Ashok,
Looks like you have regional settings set to US date formats (mm/dd/yyyy), and you want to enter dates in dd/mm/yyyy format. Make sure you type dates according to computer date formats, otherwise your dates will be wrong. For example, if you type 11-10, this will be converted by excel as November 10 if regional date format is mm/dd/yyyy, and it will be converted to October 11, if dd/mm/yyyy is the system date format.
Catalin
S Ashok Kumar Das
Good Evening Catalin,
Thank you for your reply, as i told you before i already make the changes in control panel date format and in excel sheet as well as also. Even one more time i check it is in regional date as dd/mmm/yy but still it’s not working.
Still showing mm/dd/yyyy (7/29/2017) inside cell (from outside is showing dd-mmm-yy (29-Jul-17)).
But while i type 29-7 not converting else if i type 7-29 it’s convert. Why it is not 29-7 to date??????
Thanking you,
Ashok Das
Catalin Bombea
Hi Ashok,
Can you upload screenshots of your regional settings?
My regional settings are the same non-US settings: dd/mm/yyyy, and I am able to type 29-7 and it is properly converted as a date. You still seem to have US date format mm/dd/yyyy.
You can upload the images on our forum (create an account, sign-in and create a new topic)
Catalin
SASHOKKUMARDAS
Hi Catalin,
As per your suggestion i registered with in your forum and upload the images. Regional settings in Non-US Format.
Please see and suggest me.
ASHOK
Jan van Zyl
Hi Mynda,
Thanks for the handy tip on converting dates incorrectly entered by so-called “experts”. Please include my gmail address as below in your regular distribution.
Amiee
Hi,
I have been sent multiple columns of data that is organized as mm/dd/yyyy hh:mm:ss for example 08/24/1995 10:01:02. The data seems to formatted as text because it is left aligned, although I customized the cell format with “mm/dd/yyyy hh:mm:ss”. I need to find the time difference between two different dates and have it organized by “dd:hh:mm:ss”.
I was hoping you could help, as I tried all the suggestions listed above but receive “#value” in return!
Catalin Bombea
Hi Amiee,
If none of them worked, I suspect you have a different regional date format set in your system, other than US format: m/dd/yyyy, is your system set to dd/mm/yyyy? If yes, go to regional settings and change your region to US, a simple formula like =DATEVALUE(A1) will be able to convert that date.
The best way to handle dates is now Power Query: power-query-date-formats
Cheers,
Catalin
Anson
Even you can try below formula to convert the date format.
=IF (B1 = ””,”” , IF( DAY(B1) > 12, DAY(B1) & ”/” & MONTH(B1) & ”/” & YEAR(B1) , VALUE( DATE( YEAR(B1) , DAY(B1) , MONTH(B1) ) ) ) )
Benny
I have years saved as numbers. When I try to convert them to dates 1926 becomes 4/10/1909, 1927 becomes 4/11/1909, etc. How do I fix this?
Mynda Treacy
Hi Benny,
If your cell only contains the ‘year’ portion of the date then you can’t convert that to a date. You need the day and month portion as well. Please post a sample of your dates and the desired output on our Excel Forum so we can help you further.
Mynda
Nicky Gutteridge
I am not very techy – my problem is this:
Dates are as folllows in a csv file
1012016 is 1/1/16
21012106 is 20/1/16
22122016 is 22/12/16
Etc
In essence if the output is 7 digits the front 0 is missing.
I think this is a bit different to those examples I have read.
I need to sort a BIG list into date format.
Aaghhhh
Catalin Bombea
In a new column, add this formula: =IF ( LEN (A1) <8 , 0&A1, A1)
If the string has less than 8 chars, it will add a zero at the beginning of the old string, to make them all have same length.
Then you will be able to create a date from that new string: = DATE ( MID( B1, 5, 4), MID(B1, 3, 2), MID(B1, 1, 2 ) )
jason pare
I need help with my spread sheet! in my dates section it I have issue with them and I cant seems to understand why and how to prevent it often there is this (#####) showing up instead of my actual dates .
anyone can tell me on how to fix this and how t prevent it please and thank you??!!
Mynda Treacy
Hi Jason,
When a date displays at ##### and you have made sure the column is wide enough to display the date, then it indicates that the date is a negative value and you can’t have negative dates.
Mynda
Mark
I have an imported .txt file with “27-Mar” in cell A1 and “2015 1:11:59 PM” in cell B1. Is there a simpler way to combine these two cells into cell H1 as an Excel serial number. I am currently using =CONCATENATE((TEXT(A1, “MM/DD/”)),B1) but I am unable to get the serial number until after I cut and paste the values into my .xltx working file. If there is a way to get the serial number “42090.5499884259” before transferring files then I can easily apply my custom format of m/dd/yyyy, hh:mm:ss AM/PM to return 03/27/2015, 1:11:59 PM in the .xltx file.
Hoping this clearly explains my current situation. I am still rather green at this and am currently limited to Excel Starter 2010. Any suggestions would be appreciated. Thanks.
Catalin Bombea
Hi Mark,
Try this formula:
=DATEVALUE(A1&”-“&B1)
DATEVALUE can convert a text string into a date, joining the cells with a minus sign will create a text string like: “27-Mar-2015 1:11:59 PM”.
Catalin
Mark
Hi Catalin,
I like the simplicity of your formula but I can not get it to work for me. When I type it in I get an “#VALUE!” error and when I paste it in from your post with the slanted quotes (“-“) I get a “#NAME?” error. Not sure what is wrong, it could be that I have overlooked something quite basic due to my lack of experience.
Mark.
Catalin Bombea
Type the double quotes from your keyboard, the html editor is replacing the quotes, and they are not recognized by excel.
Catalin
Mark
Hi Catalin,
I have tried what you told me and everything else I can think of but I still get the “#VALUE!” error. I converted my .txt file to .xlsx and am getting the same error with that. I think that Excel recognizes A1 “27-Mar” as a date (42090) but B1 “2015 1:11:59 PM” only as text. Could this be an issue? If we could get this to function it could save me many many hours of work.
Mark.
Catalin Bombea
You can also check if the first cell has a numeric value, if yes then convert it to text:
= IF ( ISNUMBER (A1) , TEXT (A1, “dd-mmm” ), A1) & “-” & B1 will return 27-Mar-2015 01:11:59 PM, even if the first cell is recognized as a date or a text, to prepare a text string for DATEVALUE :
= DATEVALUE ( IF ( ISNUMBER (A1), TEXT( A1, “dd-mmm” ), A1) & “-” &B1)
Catalin
Kathy Schlough
I used Excel to open a .dat file. Used comma, delimited and General for the conversion. My data converted fine except for the dates. They are shown as being formatted “General” and are right-aligned. I’ve tried changing to a “Date” but the Short Date, Long Date and Time all show as “#######”. I’ve tried using the formulas above and none of them worked. The numbers in the cell show as “731953” (7/3/1953) or “12312003” (12/31/2003). Yes, I’m in the US.
Can you help?
Kathy S.
Mynda Treacy
Hi Kathy,
In step 3 of the Text Import Wizard try selecting the column containing dates in the ‘Data Preview’, then set the ‘Column data format’ to ‘Date’ and choose MDY.
Kind regards,
Mynda
Khisi Molefe
Hi Kathy,
Try to use left formula to return 8 numbers only.After that convert text values to numbers.It will work.
Kind Regards,
Khisi Molefe
Mynda Treacy
That won’t work with only 6 numbers in the cell as i Kathy’s example “731953”.
Mynda
Kamil
Thanks for your kindness.
However, I tried some of this on this sample date error: 21/08/2015 and some other ones but it does not work.
Can you help please.
Kamil
Catalin Bombea
Hi Kamil,
You have to use the ISNUMBER() function to determine if that date is a real numeric date, or is a simple text string that looks like a date. Keep in mind that Excel date system is numeric, but you can format that number to be displayed in many different ways. If a cell contains a text string, excel will return an error. In this case, use DATEVALUE function to convert the text string to a numeric date.
Cheers,
Catalin
Luisrael
I’m from Mexico and the Excel was initially working with Spanish language. Under this settings I was unable to convert dates in text format like Jan, Apr, Aug, and Dec to date format; since their initials are different in Spanish (Ene, Abr, Ago, and Dic), or it is my thought. Therefore, text like “26 Aug 2014” can’t be converted to date format like “26-08-2014”.
First, I changed the language as first step to look dates from other sources in their original language which is English, this didn’t work. Next, I tried replacing blank spaces in the date with “-” and with “/”, but none of those symbols worked to convert text to dates. I tried all the tips you listed, but none worked.
Any idea what else I can do to get the conversion?
Catalin Bombea
Hi,
Please upload a sample file with examples of your data to our Forum.
I will gladly help you
Cheers,
Catalin
ELINOR IGNACIO
Hi Mynda,
Please help me combine text like “As of ” and the date today, because i need to update the daily balances of many accounts in our company for reporting to management.
And i am very much thankful that I found this site that is very helpful in learning ms office especially excel and word.
Thank you and God Bless.
Catalin Bombea
Hi Elinor,
Try:
=”As of ” & TEXT( TODAY() , “dd/mm/yyyy” )
Catalin
Elinor
Thank you very much Catalin. God Bless!!
Catalin Bombea
You’re wellcome Elinor 🙂
Jose
Hi all. I am looking for some help.
In A2 I am copying a “date” from a external spreadsheet. A2=[transactions.csv]transactions.csv!
A2. The format is as 2/06/2015 and Excel does not recognize as a date format to use with filters, even if I setup the cell as a date format . Text to Columns works great to convert that to DD-MM-YYYY, but I am wondering if I could have a formula to automatically make this work as soon as I open this spreadsheet.
Sorry guys, but I am pretty new on Excel.
Thanks in advance.
L.E.: Sorry… Actually Text to Columns only works after copy this cell and paste as Value. Otherwise it sees the formula and not the value.
Catalin Bombea
You got it right Jose, you cannot split the result of a formula, just plain text.
Cheers,
Catalin
PRASAD
Hi
How to convert 02 years 11 months and 15 days in cell A1 to date format in dd-mm-yyyy
Mynda Treacy
Hi Prasad,
In order to convert that text into a date you’d need to make some assumptions about the year. i.e. 2 years since when?
Kind regards,
Mynda
Gerry
Hey! thanks for the tips! I have another problem though.. Apr2013 is formatted as text when i copy my data into excel..tried converting to date via the custom function in format cell but it doesnt work.. tried converting to date as well in format cell but neither does it work..is there anything else i can do?
Mynda Treacy
Hi Gerry,
Have you tried the DATEVALUE function? e.g. if your text ‘Apr2013’ is in cell A1 your formula would be:
=DATEVALUE(A1)
You then just need to format the result as a date.
Kind regards,
Mynda.
zenith
My challange is i have data stored as text in multiple sheets and i want to find easy way to convert this data to numbers at the same time instead of opening them sheet after sheet.
Note: I have the data stored in the same format(both in columns and row) so i was thinkg it should be easy if i group the sheets and apply the formular or other functions .
Zenith
Catalin Bombea
Hi Zenith,
You are saying that you don’t have dates in those multiple sheets? Just regular data? In this case, there is a method to convert data stored as text by typing in an empty cell the value 1 , copy that cell, go to Paste Special menu, choose Multiply. This will convert all your data from the selected range to General format. But this method does not work with multiple sheets, you have to create your own VBA procedure for this.
Catalin
Sarah T
Hi Guys,
I wonder if you can help. I’m creating a new spreadsheet for work and am stuck with date formats. Basically i have a ‘RIGHT’ formula in A1 looking up to this text “01/02/14-28/02/14” picking up the last 8 digits. I then have a vlookup looking up to A1 and a table on another tab with has dates in one column and the other information in the next. but my vlookup comes back with an error as the dates in the 2nd tab are shown as 28/02/2014. I have tried formatting but the vlookup still wont work. As I need a whole years worth of dates in the 2nd tab is there an easy way of doing this other than manually writing ’28/02/14, ’01/03/14 etc for every one? I tried doing concatenate but it brought through ‘41735.
Thanks in advance, just trying to save myself some time from manually writing out 365 cells 🙂
Mynda Treacy
Hi Sarah,
You need to establish if the dates you’re looking up are numbers or text. To check just select a cell containing a date and format the cell as General or Number, if it still looks like 22/03/14 (dd/mm/yyyy) then it’s text, but if it changes to 41720 then that is the serial number for the date 22/03/14 and you know that your cell contains numbers (this is best).
If your lookup table contains numbers for dates then you can use this formula to extract the last 8 digits formatted as a number:
=DATEVALUE(RIGHT(A1,8))
If your lookup table contains text then just RIGHT(A1,8) will do.
I hope that helps. Please let me know if you get stuck via the help desk (please include your Excel file or some sample data).
MF
Hi Mynda,
Can’t agree more that it is a commonly problem encountered.
I have two more ways to do it (as long as it is date stored as text scenario ):
1) Multiply the cell by 1. It should give me the same result as =value()
2) Copy an empty cell, paste special to the date (as text) cell, select Add as operation.
Either case, we need to format the cell appropriately to show the result.
Cheers,
MF
Mynda Treacy
Cool tricks. Cheers, MF 🙂
Leonid
Out of many ways to handle this problem with formula I’d highlight one using Text function: Text( A2, “####-##-##” ) and then format the cell as date or use TEXT( TEXT (A2, “####-##-##” ) , “dd/mm/yy” ) with any suitable date format pattern. It’s as intuitive as Text to Columns but more flexible.
Mynda Treacy
Thanks, Leonid, but your formula still returns a text string, which cannot be used in a formula or PivotTable. The aim of this tutorial was to convert the date text string to a number format in Excel.
Perhaps you meant a formula more like this:
Where A2 contains a date text string 01-01-2014
Kind regards,
Mynda.
Leonid
That’s exactly what I had, but lost “–” when copy/paste formula into comment.
Mynda Treacy
🙂 ah, the HTML editor in the comments can make a mess of formulas sometimes.
Juan
How great tips, they can’t be more timely for me, congratulations Mynda!!. Just this week a coworker asked me how to convert a number with a decimal character stored as text, so he can use it in a multiplication. For example: 30.00 (in Latin America the decimal character is “comma”) and I need to convert to 30,00
What a great coincidence that in our countries we use the date format DD/MM/YYYY!
Mynda Treacy
Hi Juan,
I’m glad you found this useful.
I believe only the U.S. enter their dates mm/dd/yyyy and the rest of the world use dd/mm/yyyy 🙂
Kind regards,
Mynda.
Antony Lambert
I’m trying to change the formatting in a textbox. Cell A1 is formatted as 14 October 2023, but the textbox has 14/10/23. Is it possible to change this?
Mynda Treacy
Hi Antony, presumably the text box is linked to cell A1. In which case, convert the date to text with this formula:
Then link the text box to the cell containing the formula.
Mynda