by 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 non-formula 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 text-to-date conversion technique most suitable for your data format and your preference for a formula or non-formula 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, 1-Jan-1900 is stored as number 1, 2-Jan-1900 is stored as 2, and 1-Jan-2015 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 8-digit number to date in Excel
It's a very common situation when a date is input as an 8-digit 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 | 10-Mar-2016 |
| 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 3rd and 4th 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 number-date (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 20th 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 text-to-date 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 non-formula solution that can handle this task and the next section explains the detailed steps.
Text to Columns wizard - formula-free way to covert text to date
If you are a non-formula user type, a long-standing Excel feature called Text To Columns will come in handy. It can cope with simple text dates demonstrated in Example 1 as well as multi-part 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 right-aligned 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 multi-part 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 self-explanatory:
DATE(year, month, day)
In our case,
yearis in column E anddayis in column D, no problem with these.It's not so easy with
monthbecause 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.
- Right-click 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 two-digit 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 upper-left 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 2-digit 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 one-click 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 text-dates 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.
710 comments
Thank you for such an informative article!
Everything one would wanna know about date settings in Excel has been provided beautifully in this article.
Hello there,
Hope you are doing well. We normally received our date in the format of 16-July-2021. Would it be possible to convert the original date into a value like YYYYMMDD (example 20210716)? I tried to use the custom formatting option to give me YYYYMMDD and while it does display the right value, the formula bar still shows the slash units (example 2021/07/16). Is it possible to remove the slashes in the formula bar?
Thank you for your time!
Hello there,
Hope you are doing well. We normally received our date in the format of 16-July-2021. Would it be possible to convert the original date into a value like YYYYMMDD (example 20210716)? I tried to use the custom formatting option to give me YYYYMMDD and while it does display the right value, the formula bar still shows the slash units (example 2021/07/16). Is it possible to remove the slashes in the formula bar?
Thank you for your time!
Hello!
You can learn more about convert date to text in Excel in this article on our blog.
=TEXT(D1,"yyyymmdd")
The formula bar shows the real value. If the value is written as a date, then you cannot remove the slash in the formula bar.
Tried various option to change text date and time to real date and time but no luck. 13/6/2021 9:30:00 PM
Can u help on it
Hello!
Read this comment.
I hope this will help
Hi there,
We get a date in text format (for example April 12 2002) - is it possible to covert this to the date format DD-MMM-YY (for example 12-APR-02.
Thank you
Hello!
Your question is answered in this comment.
Set the cell to a custom date format of your choice.
Hope this is what you need.
Hi sir,
i want to create a format for particular cells.
In that cell if i type 26062021as a text by pressing enter i want as a date 26/06/2021 in the same cell.
is it possible?
Hi!
This is possible with a VBA macro
Hi,
We get a date in text format in dd/mm/yyyy hh:mm:ss format from Application when we download it into excel. I need to convert this into Date in mm/dd/yyyy hh:mm:ss format. For example date is in text format (30/06/2021 11:53:32) (dd/mm/yyyy hh:mm:ss). I need to convert it into Date as 06/30/2021 11:53:32) into Excel. I understand that if date is coming from application in dd/mm/yyyy format and if your system time on which you are opening and using excel is not in dd/mm/yyyy format then excel does not recognize it as date. Do we need to change the system date format using Language preference to make it work?
Hello!
The formula below to solve your task:
=--(MID(A2, SEARCH("/",A2) + 1, SEARCH("/",A2,SEARCH("/",A2)+1) - SEARCH("/",A2) - 1)&"/"&LEFT(A2, SEARCH("/",A2,1)-1)&MID(A2,SEARCH("/",A2,SEARCH("/",A2)+1),100))
We have a tool that can solve your task in a couple of clicks - Ablebits Tools - Text to data. 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.
Thanks Alexander for your quick reply and the formula. I tried the formula that you shared but it looks like it gives result in text format only. I need that results in Date format. Can you please help?
Hi!
The formula returns the date. You need to change the format of the cell. You have to do it yourself: How to change Excel date format and create custom formatting
I have tried to do format cell with the help of Custom formatting with mm/dd/yyyy hh:mm:ss. but it did not work. As I understand to distinguish date and text date is date will be right aligned while text date will be left aligned and for text dates If several text dates are selected, the Status Bar only shows Count. which is the same in my case as after applying the formula that you shared and applying format cell on that. If I tried to run other Date functions on that cell it is giving Value error.
Earlier I was able to get the desired results but for that I need to use three cells with different formula to get the Date in mm/dd/yyyy hh:mm:ss in Date format. That's why I requested your help if that can be done with single formula.
Hi!
My system date format is mm/dd/yyyy. The formula works for me and returns the date. Check if the formula was copied correctly if there is a double minus at the beginning. Use the program that I recommended to you.
Hi Alexander,
my system date format is m/d/yyyy. I missed to add double minus at the beginning. After adding double minus it is showing the result in Date format however it shows the date in the same format for date till 12/06/2021 hh:mm:ss like dd/mm/yyyy hh:mm:ss and when I choose custom formatting to dd/mm/yyyy hh:mm:ss it converts in into 06/12/2021 hh:mm:ss.
As soon as I choose date 13/06/2021 hh:mm:ss and later dates, formula starts giving value error. so it looks like it is not picking date greater than 13th. considering 13 as month?
Hi Alexander,
After changing Language preference to English (United Kingdom) which changes my system date to dd/mm/yyyy which is matching with text date dd/mm/yyyy hh:mm:ss on excel, Formula is working as expected. Thanks a lot for your help and providing feedback patiently.
Hi my question is simple, please solve this!
Problem: number format = 05/03/2017
when you change the above no. format to U.S. date format MM/DD/YY, the result is 03/05/2017. This is 05th of March 2017 as per the format, but the actual date for me is 03rd of May 2017. I want to convert the number 05/03/2017 to US format MM/DD/YY that should give me 05/03/2017 and not 03/05/2017, I hope my question is clear? Awaiting for any help!
Hello!
I didn't really understand what is your start date and what date you want to get. But this formula should help
=DATEVALUE(TEXT(A1,"dd/mm/yyyy"))
or
=DATEVALUE(TEXT(A1,"mm/dd/yyyy"))
Need to convert to date
15AUG21V40528
Hello!
I’m not sure I got you right since the description you provided is not entirely clear. However, it seems to me that the formula below will work for you:
=--LEFT(A1,7)
Set the date format in the cell
I have given date of 20200911043955
How to change it to date?
=DATE(LEFT(B1,4),MID(B1,5,2),RIGHT(B1,2)) this should help assuming given is a text form of "yyyymmddhhmmss"
Hi,
I have a column of data. One column named Data Changed but the values under are 2.02009E+13. How can I changed this to Date Format?
Hello!
Pay attention to the following paragraph of the article above: How to convert number to date in Excel - How to convert 8-digit number to date in Excel. Your number is too long to display in normal format. Look in the formula bar and you will see it in full. You can use the formula from this paragraph.
Hello , i have dates like this ; 12/03/2021 22:49:46 and 13/03/2021 02:59:02
this are written in text.
When i use formula : =TEXT(Test!A2648,"yyyyddmm,hh:mm:ss") i am getting 20210312,22:49:46 and it is correct
but if i use same formula on 13/03/2021 02:59:02 i am getting the same text, no like on the first one.
i tried to change yyyyddmm to yyyymmdd , tried to add / but nothing. can you help me?
Hello!
Your dates are written in dd/mm/yy hh:mm:ss format as text. The date of March 12 turns into December 3 for you. The second date does not change since month 13 does not exist. Use the guidelines above to convert your text to the correct date. And then apply a custom format.
I recommend reading this guide: I recommend reading this guide: How to change date format in Excel and create custom formatting
If something is still unclear, please feel free to ask.
I have this problem:
I received a file with this date format 13/04/2021,which i need to be shown as 4/13/2021.
Text format wont allow me to change it.
I tried the formula =DATE(YEAR($A1),DAY($A1),MONTH($A1) and it worked on a string like 12/4/2021, but when April appeared as 04 it didnt work.
Hello!
If your date is written as text, I recommend using the instructions in this article: How to split text in Excel using formulas.
=DATE(RIGHT(A2,LEN(A2) - SEARCH("/", A2, SEARCH("/", A2) + 1)),MID(A2, SEARCH("/",A2) + 1, SEARCH("/",A2,SEARCH("/",A2)+1) - SEARCH("/",A2) - 1),LEFT(A2, SEARCH("/",A2,1)-1))
Hope this is what you need.
We have a tool that can solve your task in a couple of clicks - Ablebits Tools - Text to data. 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, I am trying to convert this type into a date format in Excel.
9/9/2020 12:53:42 PM -05:00
I used to use this: =DATEVALUE(MID(C2,1,10))+TIMEVALUE(MID(C2,12,8))-TIMEVALUE("5:00")
but it isn't working for me. Any thoughts?
Hi
I have applied this formula: =DATE(RIGHT(A4,4),MONTH(DATEVALUE(LEFT(A4,3)&"1")),MID(A4,5,FIND(",",A4,5)-5)) but it has applied to first 2 dates
Jan 2, 2017
Jan 02, 2017
I want to write a single formula to convert them into an appropriate date format for all the dates. Please suggest.
Hi,
Unfortunately, it is impossible to convert all your values to a date using one formula. Your values contain a different order of dates.
Hello!
I believe the following formula will help you solve your task:
=DATEVALUE(MID(C2,1,8)) + TIMEVALUE(MID(C2,10,11)) - TIMEVALUE("5:00")
I hope it’ll be helpful.
Hello,
I want to write a single formula to convert them into an appropriate date format for the below all the dates.
Jan 2, 2017
Jan 02, 2017
Jan 2 2017
Jan 02 2017
Jan 2, 17
Jan 02, 17
Jan 2 17
Jan 02 17
2 Jan 17
2 Jan 2017
02 Jan 17
02 Jan 2017
2, Jan 17
2, Jan 2017
02, Jan 17
02, Jan 2017
Could you please suggest me the single formula for the above dates.
Thank you so much in advance for the help.
Hello!
I hope you have studied the recommendations in the tutorial above. It contains answers to your question. Also read the comments and answers to previously asked questions. For example, this comment. Or this one.
Hello,
If I want month only in these 3 dates of 5.6.2012, 05.06.2021,31.1.2021 which actually seems as date but it is not. It is in text format. so ,how can I found the mid value (Month) from Mid formula. Please suggest.
Hello!
I recommend reading this guide: Split first, last and middle name. Middle name in your case is a month.
For example:
=MID(A2, SEARCH(".", A2) + 1, SEARCH(".", A2, SEARCH(".", A2)+1) - SEARCH(".", A2)-1)
I hope I answered your question. I
Hi
I have this number 20210413090001. I want to convert it to date and time,
2021/04/13 09:00:01
please advise the formulae to use.
thank you
Hi,
The answer is in this comment.
Thank you v much. Appreciate your precious time to guide me
how to covert below number in date
132627705569531000
Hi!
I have a some letters. Every letter represent a range of dates. The letters have priority between themselves. The ranges may overlap themselves.
In result I need to obtain the (dates/ranges in correct order: from earliest date - to latest) of letter with priority is higher than other letter present in same day/range.
We ignore letter with lowest priority, in exchange for the best.
https:// i.imgur.com/ZYmSLO4.png
I made an example in excel, image is above (link).
This what im asking may br hard to realize, but i would thankful even with an advice, how to do that.
(I think the main problem is to separate the rages, and regroup them)
Thanks anticipated!
Hello!
Based on your description, it is hard to completely understand your task. However, I’ll try to guess and offer you the following:
In cell L7, write down the formula
=IF(SUM((F7:F19="B")*(G7:G19>J7)*(H7:H19
Thanks !
Ill try.
Hi, I have the below date value in cell 16-JUL-2018 - 09:00. But, excel consider this as text value and it is left aligned. I want to convert this to date format as 16-JUL-2018 - 09:00. Appreciate your help.
Hello!
Try the following formula:
=--SUBSTITUTE(A2,"–","",1)
Use custom date format :
dd-mmm-yyyy – hh:mm
Hope this is what you need.
Sorry Alexander ! May be i did not explained my requirement correctly.
My requirement is, imported date is in text format, 05-Mar-2021 - 09:00 and i want to convert that to Date in the same format.
Appreciate your help.
Hi,
The formula works for me. I don't know which dash is used in your text. Replace the dash with a short one.
=--SUBSTITUTE(A2,"-","",1)
HI
DATE 01/04/2021 CHANGE IN TO 01-04-2021
HOW CAN I CHANGE THIS FORMET
Hi,
Please have a look at this article — How to change Excel date format and create custom formatting
Hi Guys,
In the report the its '1/31/2021 12:00 AM'
'1/31/201 8:20 PM'
I want to convert that into '2021/1/21 00:00'
'2021/1/31 20:20'
Please help how to get this
Hi,
I think your question contains typos. Describe the problem in more detail and without errors.
Hello,
I need to convert a four digit-number "2020" to a year. I've tried to use the the Custom + yyyy method and it keeps printing 1905. I have no other date information - month/day, just the year. Please help!
Hello!
You need to convert number to date. Use the DATE function
=DATE(2020,1,1)
Then use the custom date format "yyyy"
How do I covert this date format ; 18/02/1900 to text (numbers)
Hello!
Here is the article that may be helpful to you: Convert date to text in Excel
I hope this will help, otherwise please do not hesitate to contact me anytime.
Am working on staff pension how do I calate the due date of retirement, retire ment age is 65. Staff date of birth is 02/5/1958. Which formular can add 65 to date format to get expected date of retirement
Hello!
To add 65 years to the date use the formula
=DATE(YEAR(A2) + 65, MONTH(A2), DAY(A2))
Please have a look at this article — How to subtract or add years to date in Excel
I hope I answered your question.
how to convert a text "Sun Jan 12 2020 05:29:59 GMT+0530 (India Standard Time)" to a date
Hi,
when i paste this
"48618130 21-2-17 00:11:53 sell 1.00 XAUUSD 1789.19000 1826.6 1785 21-2-17
01:47:41 1787.84000 0.00 -1.79 135.00"
from my trading journal (you can see source here http s://imgur.com/a/JRNOF5J)
into my excel it change date to 21.2.2017, but correct is 17.2.2021. I tried set cell to be TEXT but not working.
Can you give me advice how to format it or code for macro or how to turn off this autochange ?
date is in 1 cell...
Hi,
In your trade journal, the date format is "yy-mm-dd". In your Windows settings, the date format is "dd-mm-yy".
Option 1. Change the default date format in the Windows Control Panel.
Option 2.
=DATE("20"&LEFT(A1,2),MID(A1,4,FIND("-",A1,FIND("-",A1,1)+1)-FIND("-",A1,1)-1),RIGHT(A1,LEN(A1)-FIND("-",A1,FIND("-",A1,1)+1)))
I hope it’ll be helpful.
hi wanted to ask you help on converting Sep 27 2018 to dd/mm/yyyy in excel .. super thanks
Hello!
This problem has already been resolved on our blog. Read here.
Hi, could you please help me to converting date & time like 20210205062248 into mm/dd/yyyy hh:mm:ss AM/PM format
Hello!
I recommend reading this comment.
Hi there,
Could I get some help with converting a date like this 19660218 to yyyy/mm/dd
Thank you,
Robert
Hello!
To get the date use this formula —
=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))
Use custom format "yyyy/mm/dd"
Hello Alexander how would I convert 2014-01 to January using a formula?
Hello!
I recommend using the VLOOKUP function to select the name of the month
=VLOOKUP((--RIGHT(B1,2)), {1,"January";2,"February";3,"March";4,"April";5,"May";6,"June";7,"July";8,"August";9,"September";10,"October";11,"November";12,"December"}, 2,0)
I hope this will help
Hello Alexander-
Unfortunately that did not work! Is there another possible solution for 2014-01?
Perhaps something with a right or left or trim function?
Hi,
The formula works. I write in B1 - "2014-01" and get "January". What doesn't work for you?
When I enter the formula I get #VALUE!
I even copy and pasted to make sure I am entering it in correctly.
Hi,
Unfortunately, without seeing your data it is impossible to give you advice.
Unfortunately, I don't understand the reason for your error. The formula works for me. Could you please describe your task in more detail and send us a small sample workbook with the source data and expected result to support@ablebits.com? Please include the link to your blog comment.
We'll look into your task and try to help.
Hi Alexander, how do you convert number 1 to January 30, 2021, and 1.25 to February 7, 2021, 5 to May 31, 2021, so on and so forth. is this even possible?
Hello!
I believe the following formula will help you solve your task:
=EOMONTH(DATE(2021,1,1),A1-1)+(A1-TRUNC(A1))*31
I hope it’ll be helpful.
Thank you so much! this is very helpful!
How do i convert numbers from 11:07 to 11.07?
Hello!
I guess 11:07 is the time. You can convert it to text -
=TEXT(A1,"hh.mm")
I hope I answered your question. If something is still unclear, please feel free to ask.
Is there a formula I can use to get 041620 (MMDDYY) to generate to April 16, 2020?
Thank you!
Hello!
I believe the following formula will help you solve your task:
=DATEVALUE(TEXT(A1,"##-##-##"))
Set the date format in the cell.
I hope it’ll be helpful.
How do I make it so that when I type 05082017 it automatically switches to 05/08/2017? Without needing to separate the number with “.” “,” “/“ or anything
Hi,
Your problem can only be solved with a VBA macro
Hi,
Is there a way to convert a cell value that is actually a year, but formatted as a number, to an actual year that responds correctly to formulas?
For instance,
Column C displays manufacture years as : 2019, 2017, 2020, etc.
These are all currently just numbers, but should be years so that they can be used in other formulas and tables.
Thanks in advance.
Hello!
You can convert the year number to the start date of that year.
=DATE(C1,1,1)
You can learn more about using DATE function in this article.
Hello!
At work I have encountered a recurring problem with several dates extracted from a SCADA program, that have a structure such as:
2020 - 12 - 31 19:00.106. For my monthly reports, I would like this date to look like: 31.12.2020 19:00, aka the dd/mm/yyyy hh:mm format.
However, whenever I try to convert the date into the format I wish to convert it into, using the Format Cells Menu, nothing changes.
Could you please provide a solution for my problem? I have tried multiple commands, however, converting it into the desired format only works if I manually delete the digits after the . (As in 19:00.106). I would greatly appreciate your help, for I would like to simplify my work.
I have made a mistake in typing, for which I apologize. The date format I export from the SCADA program is: 2020 - 12 - 31 19:00:01:106. I forgot to type in the seconds.
1944 to 2020
helpful, thank you!
4/12/2021- 4/16/2021 to Monday 12th April 2021 - Friday 16th April 2021
How to get Monday 12th April 2021 to Friday 16th April 2021
Hello Alexander,
Looks like you've provided great information so far. My question is no doubt simple, but I can't seem to find an answer.
I need to convert 3-character month text ("JAN" ... "DEC") into a 2-digit text month name ("01" ... "12").
Basically all I need is to add a leading zero to months less than 10 (Jan~Sept), but need a formula to work with any month text.
Can you help, please? Thanks so much.
Can Someone help me convert this text to date?
Nov 26, 2020
Hi,
Your question has already been answered in this comment above.
Hi, Good day!
Can you help me to convert day into date, eg. Saturday, 6:30 pm (as shows in outlook email), need to change it as 21-Nov-2020,6 :30 pm in excel.
Also if not possible, kindly help to split day and time into 2 separate columns in excel.
This would help me to find the duration of the task completed.
Thanks dear
Hello!
Saturday, 6:30 pm cannot be converted to a date since there are 52 Saturdays in the year.
Please check out this article to learn how to split text in a cell.
Hello guys.
I have an excel with dates. I have a several dates like: nov/7/2020, then when I convert to dates with dd/mm/yyyy format it works fine but bellow the last date with november, the october's dates start, and those dates like this: oct/1/2020, doesn't work the date conversion via "Text" to Column".
I need help
Bests regards. Thanks a lot.
Hello!
I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
It’ll help me understand it better and find a solution for you. Thank you.
Hi,
Can someone help with following format please? I need change the format for date e.g 21/09/2020, but it comes up as 5 digit number e.g 44095. How to convert this number to date please? thank you
Hello!
You need to set the date format in the cell.
Please have a look at this article
Hi,
Can someone help with following format please? I need change the format for date e.g 21/09/2020, but it comes up as 5 digit number e.g 44095. thank you
Hey,
Could someone help me covert following:
Oct 30, 2020 (as a text from a .cvs file) - > date
Aim will be to sort the dates from oldest to newest at the end (what is not possible at this point)
Many thanks in advance,
Hello!
Please try the following formula to convert text to date:
=DATE(RIGHT(D1,4),MONTH(1&LEFT(D1,3)),MID(D1,5,2))
I hope my advice will help you solve your task.
Hey, what can I do so that if I enter the number 240402 in A1, I get 24/04/02 in the same cell?
Hello!
I hope you have studied the recommendations in the tutorial above. Pay attention to the following paragraph of the article above — How to convert 8-digit number to date
You can change the value in a cell either manually or using the VBA macro
Hai Alex,
Can you help me?
I want to convert "September 30th 2020, 17:00:08.680" to "October 1st 2020, 00:00:08.680", so i want to change to +8 with automatically change the date and time.
Any idea how to do that automatically?
Thank you Alex.
Hello!
Please have a look at this comment.
Add to date 1. If your data is written in cell D1, and the new date is in D3, then you can use the formula
=TEXT(D3,"mmmm d yyyy")&MID(D1,SEARCH(",",D1,1),50)
I hope my advice will help you solve your task.