How to convert text to date and number to date in Excel

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
  • Right-aligned by default.
  • Have Date format in the Number Format box on the Home tab > Number.
  • If several dates are selected, the Status Bar shows Average, Count and SUM.
  • Left-aligned by default.
  • General format displays in the Number Format box on the Home tab > Number.
  • If several text dates are selected, the Status Bar only shows Count.
  • There may be a leading apostrophe visible in the formula bar.

How to distinguish dates from text strings in Excel

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. To convert such serial number to date in Excel, all you have to do is change the cell formatting. For this, select a cell or a range of cells with the numbers you want to convert to dates and press Ctrl+1 to open the Format Cells dialog. On the Number tab, choose Date, select the desired date format under Type and click OK.
Converting numbers to dates in Excel

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:

=DATE(year, month, day)

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:
Converting number to date in Excel

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:
Convert text to date using the Excel DATEVALUE function.

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:
Using the Excel VALUE function to convert a text string to date

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

Converting text to dates via mathematical operations

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

Replacing a custom delimiter with a slash

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.

Converting text strings with custom delimiters to dates

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.

  1. In your Excel worksheet, select a column of text entries you want to convert to dates.
  2. Switch to the Data tab, Data Tools group, and click Text to Columns.
    Switch to the Data tab and click Text to Columns.
  3. In step 1 of the Convert Text to Columns Wizard, select Delimited and click Next.
    In step 1 of the Convert Text to Columns Wizard, select Delimited and click Next.
  4. In step 2 of the wizard, uncheck all delimiter boxes and click Next.
    On step 2, uncheck all delimiter boxes and click Next.
  5. 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.
Choose the format corresponding to your dates, and click Finish.

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.

  1. Select all text strings to be converted to dates.
  2. Click the Text to Columns button on the Data tab, Data Tools group.
  3. On step 1 of the Convert Text to Columns Wizard, select Delimited and click Next.
  4. 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.
    Select the delimiters your text strings contain.

    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.

  5. 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.
    Make sure all columns have the General format and choose where to insert the columns.

    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.

    Text strings are split into 3 different columns.

  6. 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, year is in column E and day 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:


    For the MONTH function to understand it deals with a date, we put it like this:


    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:


And voila, our complex text strings are successfully converted to dates:
The 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:
    Quick conversion of text dates using Paste Special
  • 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:
Error indicators in Excel

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.
Fixing text dates with two-digit years using Excel Error Checking

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.

Turning on Error Checking in 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.

453 responses to "How to convert text to date and number to date in Excel"

  1. Izzy says:


    I have a column with 'Apr 7, 2016 10:39 AM' type data in it. How do I convert this text into a date/time format so I can use it to do further calculations? I.e Find the difference in time elapsed between 'Apr 7, 2016 10:39 AM and Apr 14, 2016 09:21 AM'?

    I've tried text to column (which I don' want to do in order to protect the data source). Is there a formula for this?

    • Hi, Izzy,

      activate the cell with a text you want to transform, then under the Home tab find Number format box, and choose Long Date or Short Date to your liking. If it already says Date – your data is ready for calculations.

      • Chris says:


        I know this is an old, thread, but I've tried everything so far, so I'm gonna give this a shot as well.
        I have the same issue as Izzy and I've tried what you advised in your reply as well as the options in the article but as a I result I only get either the date "2/1/2020" or only the time "00:15:00". Regardless of formatting, I can't get them both as Date+ time or "2/1/2020 00:15:00". Any ideas?

  2. Joshua says:

    Leading 0 issue
    my formula works for dates with no leading zero
    =DATE(RIGHT(E2,4), LEFT(E2,2), MID(E2,3,2))

    10021980 10/02/80 -> worked
    03301980 09/01/82

  3. Joshua says:

    Good morning!

    my date data is in custom data type '00000000'
    changing it to string loses the leading zero and using the formula

    =DATE(RIGHT(E3,4), LEFT(E3,2), MID(E3,3,2))

    also does not work with the leading zero. any thoughts?

    example below from my worksheet

    10021980 10/02/80 (great)
    03301980 09/01/82 (not good)

    • Hello, Joshua,

      could you send us your sample workbook with the data and the result you expect to get to It's a bit difficult to help you right now, since we need to take a look at the way your data is formatted and arranged.

      • Dusty says:

        one way around is
        =DATE(RIGHT(E3,4), LEFT(E3,len(e3)-6), MID(E3,3,len(e3-5)))

        this way it can change the spot it starts reading the number from depending on the length of the number string since it doesn't see the lead 0

        • Michael says:

          I am not sure if this website is still checked, but can I get help writing a formula for the following number format. 00102998, the first number would be the year, so 2000 in this case. It actually goes 1 – 0 for 1990 – 2000. Then the second two numbers are the month. The day is not important and can be any 2 digit number.

    • Mark says:

      I had a similar problem to Joshua's with leading zeroes. I was able to solve it using an "if" argument with the "len" argument to adjust for where the date formula picked up the month and day numbers from the string.
      My data were, for example:
      14.09.74 (in cell e2, format mm.dd.yy)
      my formula was: =DATE(RIGHT(E2,2),MID(E2,IF(LEN(E2)=8,4,3),2),LEFT(E2,IF(LEN(E2)=8,2,1)))
      I hope this helps anyone searching for help on this particular problem with leading zeroes.

  4. Jenny says:


    I am using Google sheet. I have a column in which the date is entered as text "Apr 21,2017 - Apr 25,2017". Now, I need your help to convert this to 04/21/2017 - 04/25/2017.

    Please help me, if possible.

    Thank you in advance.

  5. sam says:

    all failed
    i had to retype dates
    best way

  6. Shaun says:

    Thanks for the help, great job!

  7. Aleandro says:

    Please help me, If I have in a cell this: 2015-06, and I need to have Jun-2015, How should I do?

  8. Yang says:

    hi team,

    i am trying to convert excel date format from 5/29/2017 to 2017-05-29 but not working. can anyone give me a hand?


  9. Pete says:


    I have a bunch of date/time stamps that look like this:


    How do I convert them to real dates & times?


    • Hi, Pete,

      could you please specify what are these values stand for?

      It's just the very last date in Excel (December 31, 9999) is stored under the 2958465 number. The stamps you provided simply can't represent the integers of the dates for Excel.
      So, for us to help you better, please specify what these values should transform into exactly.


  10. iliya filamilo says:

    please how do i convert 25/5/17 "twenty-fifth may twenty seventeen" to a format excel recognizes? thank you

  11. Lily Robison says:

    this is awesome. thank you!

  12. Peter says:

    you're awesome

  13. Prabu Sundararajan says:

    How to convert below date format into "DD/MM/YYYY HH24:MI:SS"

    Jul 1, Sat 2017 6:30:01:236
    Jul 1, Sat 2017 6:30:01:330
    Jul 1, Sat 2017 6:30:01:421
    Jul 1, Sat 2017 6:30:01:564
    Jul 1, Sat 2017 6:30:01:678
    Jul 1, Sat 2017 6:30:42:303

    Please assist.

  14. sudha says:

    Good Day!
    How to convert complete no of entries in ss from 2017-07-03 12:47:47 IST to 2017-07-03 12:47:47, i need to remove text(IST) in all the date entries.

  15. Carole says:

    Love, love, love your site. It helped me so much. So easy and fast finding help. I will recommend to everyone. Thank you

  16. junaid says:


    calculate the days with current today date

  17. Pavan Kumar says:

    Very good solution. Converted the below format date into standard format by following the above steps. Thanks alot.

    Feb 1, 2012 2:39:39 PM AST

  18. vivek says:

    hey i want to convert date into next date..
    Example:- 1/4/2015= 2/4/2015

  19. excel says:

    how do i convert: "september 10, 2017" and "march 12, 2016" to dd/mm/yyyy?

  20. Emilie says:

    Here's my problem:
    =CONCATENATE("Campaign Performance"," ",+Procedure!D1)

    which results in:
    Campaign Performance 43021

    When I want:
    Campaign Performance 10/13/2017

    +Procedure!D1 is a formatted date field
    Anybody have any ideas?

    • Woo says:

      Little bit late, but for posterity:

      You can use the TEXT function like so:
      ="Campaign Performance "&TEXT(Procedure!D1,"dd/mm/yyyy")

  21. cris says:


    I would like to sort below date format but it is not formatted as DATE. How do i change this to a DATE format so I can sort in chronological order? thank you in advance.

    Jan 01, 2016
    Mar 10, 2016
    Feb 11, 2016
    Feb 07, 2016
    Jan 22, 2016
    Feb 12, 2016
    Jul 07, 2016
    Jun 27, 2016
    Jul 07, 2016
    Jul 02, 2016
    Jul 17, 2016
    Jul 16, 2016
    May 29, 2016
    Dec 06, 2016
    Feb 17, 2017
    Jul 16, 2016
    Jul 12, 2016
    Feb 08, 2017
    Feb 16, 2017
    Jun 14, 2016

  22. Padman says:

    Hi there,

    You can call me Padman. I find your page is different than others in the net. I managed to learn a bit here.

    My problem is unique to me, haven't seen the solution here or in any other sites.

    I downloaded a bunch of data, one of them is date - "1/13/2017 8:10:00 AM" (left aligned in the cell), when I try to format that, I get "#VALUE!" error, when I use the formula "=DATEVALUE(TEXT(B__,"mm/dd/yyyy"))". For some data, like "1/12/2017 10:50:00 PM", it's converted to "12-Jan-2017". Need your help to resolve and get the dates converted.

    I'll post more appreciation once my issue gets resolved. Message me if there's any question. Thanks


  23. Gaurav says:

    I want to restrict user to use date format in mm/dd/yyyy. However when i send the excel to user. He can enter date in mm-dd-yyyy. Can you please tell what can we use in data validation to restrict user to only use mm/dd/yyyy

  24. chris says:

    If E day is 25 December 2017 and I have a column that is E + or - the number of days from E day, how do I make the next column show the calendar date from the E day

  25. Charlotte Simms says:

    Hi, I'm trying to convert 11/9/2017 to a date but when I charge the format it comes up as #########. How do I convert it to a date?


    • Hello,
      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

    • Leslie says:

      you need to make the column width wider

      • leslie says:

        sorry: this just happened to me when I tried to use the number format to correct the issue of a date displaying as text with only numbers (ie: 20200816)
        1. see if the date is actually text (either by using the filter function and then trying to sort or by grabbing a few cells and seeing if it tries to add it up..)
        if the info is actually 'text' and you want it to be 'date'
        Data > Data tools > text to columns - separate by /
        then once it's in the columns, but it back together again as =DATE(YEAR,MM,DD)
        copy and paste as values.

  26. ronald says:

    Hi, I am trying to convert 25/12/1875 to 25-DEC-1875

    • Hello,

      If I understand your task correctly, please try the following formula:


      Please note that the formula above works only with dates prior to January 1, 1990.
      Hope this will help.

  27. Manu says:

    This saved my day!
    Many thanks

  28. ada says:

    Hi, I have date in number 43012, how can I convert it to dd/mm/yyyy.


  29. Kinjal Dave says:

    Hi ,

    Need an urgent help !!!

    Date for system gets extracted in whole numbers format i.e., 20182602

    How would i convert the same into "DD/MM/YYYY" or "YYYY/MM/DD"

  30. Rich says:

    Trying to convert 17-JAN-18 AM to 1/17/2018. Thanks.

  31. Calum says:


    How would I be able to convert "WED-14-JAN-1976 00:00:00" into 14/01/1976?

    Thank you in advance!

    • Hi, Calum!

      Please note that you can convert your text string into a date using the standard Excel Text to Columns feature and the DATE function. Please see Example 2 in this section of the article above.

      As an alternative, you can first format the column where you're going to paste the resulting dates into the necessary Date format and then enter the following formula into the first cell of this column:


      Where A1 is the cell that contains your text string.

      After that you can copy this formula down along the column.

      • Vlad says:

        Hi, No, but what if I need to convert serial into a date which is not in a separate cell, but being a part of the text line, so the that Date format can't be applied to this cell.

        • Doug says:

          It sounds as if you will need to use the Text-to-Columns tool first, split out the serial value and then convert the resulting serial value using the Format Cells/Date process.

  32. nj says:

    Highlight "even days of weeks": sat , mon , wed

  33. Windseaker says:

    Closer to my current issue is "half" dates.
    I have 1-Jan CSV file report on export from system.
    The 1-Jan cell value should have been 1-01( this was equipment ID #'s).

    My resolution was special paste and split data then manual correct. I would prefer a simple formula conversion is anyone can see one.

  34. Joy says:

    I thought this would be simple but I can't find it anywhere. I have months listed in column A. I want to create a date that's the first of that month. Something like =date(2018,A5,1), where the A5 cell says, for instance, "April", so it becomes 4/1/18. Except that I can't get it to recognize the text in A5 as a month, even trying many tricks I've seen here and on other sites. Thanks in advance for your help!

    • klokovitz says:

      have the month column (A) format to custom "mmmm" to show the month only. but be sure the content data still complete 1/1/18 and so forth. In the other cell, input =TEXT(A1,"mm")&"/01/18"

  35. manikandan E says:

    Please help to concern 31122018 into 20181231
    if 31122018 is in cell A1 I want it to be 20181231 in cell A2

  36. PRAKASH says:

    how to change this Format 01-Jun-2016 to 01-06-2016 Plz Send me Value

    • Doug says:

      Right click the cell then choose format cell then choose Date from the list and select the display format you want.

  37. Caro says:

    How do I change the date format from dd/mm/yyyy (eg. 01/01/2018) to dd-mmm-yy (eg. 01-Jan-18)? None of the formatting tools in Excel are allowing me to change the format, and when I click on "Show Formulas", the dates (which have been prepopulated by an accounting programme and then converted into an Excel spreadsheet) are the same as they appear in the spreadsheet (eg. 01/01/2018) and they appear in the formula bar as '01/01/2018. Please help!

    • Doug says:

      Right click the cell then choose format cell then choose Date from the list and select the display format you want.

  38. Khuharshree says:

    Hi Friends,

    I am looking for help to convert values



    05/01/2018 01.15 AM

    Appreciate your help on this


  39. Adisesha says:

    Please convert 050818 (Text format) to 05/08/2018

  40. Shane says:

    How can i easily convert the following text strings into dates that I can use in formula's

    Friday 30 June 2017
    Thursday 29 June 2017
    Wednesday 28 June 2017
    Tuesday 27 June 2017
    Monday 26 June 2017
    Sunday 25 June 2017
    Saturday 24 June 2017
    Friday 23 June 2017

    I have checked they are text and not dates.

    • Doug says:

      Hello, Shane:
      AbleBits has done a great job of describing how to convert data that is exactly in the state your's is in. Rather than re-type all the steps I'll point you to it. Please see Example 2 in this section of the article above.

  41. suresh says:

    how to convert '08/01/2018 to 08/01/2018...

    • Doug says:

      You can easily change this text into a date by removing the "'" in front of the date. Excel will then recognize it as a date and you can then modify the display in Format Cells.
      If you need the ellipse in the date remove the "'" go to Format Cells and in the Custom Option in the field enter "@*."

  42. gary says:

    How do I convert a fractional year to a month, day, year format? i.e. 2016.72

    • Doug says:

      I think this will do it. Where the fractional date is in A11, enter this in an empty cell and format the cell in the format you need.

  43. Vince says:

    How can I covert
    a) 0820 (mmyy) into 31/8/2020
    b) 0216 (mmyy) into 29/2/2016?

    Note: the dd is always the last date of the month.

    Anyone can help?

  44. Amaral says:

    Thanks so much!!!

  45. Sox says:

    Anyone help on how to convert a date from a conventional format like 12-03-2018 to look like 12032018?

    Thank you

  46. moin says:

    Hi, I'm trying to convert excel date format from 20110328 to 28/03/2011, I used this formula "=DATE(LEFT(B4؛4)؛+MID(B4؛5؛2)؛+RIGHT(B4؛2))" and it was useful. on the other hand I had a lot of dates that it's about daily return and I get these returns to choose the monthly return and their dates. so, I
    used this formula "=RIGHT(E8;5)" as select the last return of a month but it showed number like "40631" .How do i change to get "/mm/yyyy"?

    • Doug says:

      Have you checked the formatting for the cell that holds the 40631? The format should be the mm/yyyy and it looks as if it's General or Number.

      • moin says:

        I changed the format in customs by mm/yyyy but no change takes place in result!?
        another question, somewhere I haired that when you change the 20110328 to 28/03/2011, you could do this changes with this format "yyyy/\mm/\dd".
        I do this work but it doesn't change.Do you know the correct form of last format???

  47. Doug says:

    Dates and Excel can be a real pain.
    I took your sample "20110328" put it into cell A22 and entered this formula in B22 and formatted it Custom dd/mm/yyyy. Let me know if it works for you.

  48. Doina says:

    How can I conert a text date like “Thu 5 Jul 2017 11:59 AM” to date ?

    • Doug says:

      You might be able to right click the cell that holds the data and choose Format Cells then Date then the format you want.
      If you need to split that time away from the date, I would use the Text-to-Columns tool.
      Highlight the cell then under Data select the Text-to-Columns tool. In that window choose the Fixed Width button then OK. Next click on the lines you don't want to use to split the data then OK. Then highlight the column that holds the date select the Date dropdown and choose the DMY option then OK.
      Now you can right click the cell with the date and choose Format Cells and choose the Date option and select the date format you want.

  49. Horacio says:

    Any help in trying to convert 01/01/2018 into January 1st 2018 please?
    Many thanks

  50. Sidney says:


    How do I split the Month Day and Year without using Text to columns? or at least convert the date to just the day. Example 08/08/2018 to just 08 (day)?

  51. chandra says:


    i want to convert 'Aug 8 2018 4:02AM" in date format dd-mm-yyyy hh:mm

    can any one help

  52. Dhivya says:


    Please help. I want below text fields in format dd-mon-yyyy

    09011958 like 01-SEP-1958


  53. Kurt Sutherland says:

    Hey Guys,

    Great job on this thread! How do I convert Jan/31/18 08:42 PM to 01/31/2018 ?

  54. jayant sb says:

    Hi I have Excel text column having value as 'Wed Feb 07 13:39:40 PST 2018' I want to convert to another column with date/time type with value as 2/7/2018 1:39:40 PM , how to do it?

  55. santosh says:

    How to convert Thu 11:47 PM to date format. Thu 11:47 PM is the email received date.

  56. Julie says:


    I have some dates that I can't seem to find any formula for. My data exports strange, I don't know why it does it, but my dates populate like this:

    1022012 (supposed to be 1/02/2012)

    and the months with two digits populate like

    12232012 (12/23/2012)

    I don't know what formula I can use to formulate this as a normal date. My file is nearly 600K lines and there's no way I can manually change the date for them. Please help!!

  57. Jim says:

    This is the BEST website I've ever visited for Excel help. What incredibly clear and worthwhile information.

    Thank you! Changed my world.

  58. Lisa Smith says:

    I have a downloaded spreadsheet that lists the dates as dd-mm-yyyy and dd/mm/yyyy in the same spreadshet. I am able to change the format for dd/mm/yyyy but for the dd-mm-yyyy I cannot format. The dd-mm-yyyy dates are "General" and
    dd/mm/yyyy are "dates". Does anyone have an idea on how to fix the "General" ones so that I can format them to dates? I really need help with this. I fix it manually but it is so time consuming because each month has over 100 transactions.

  59. Red says:

    what formula should I use to convert 41255 into 12/12/2012

    I know that if cell is number , I can change the cell type to date and will have 12/12/2012

    But I need a formula to use inside /outside excel to convert a 5 digit number to dd/MM/yyyy

    • Hello!
      You can convert 41255 into 12/12/2012 by using the TEXT function:
      =TEXT(A1, "dd/mm/yyyy")

      Please note that the result will be stored as text.
      If you need the result to be a date, you should apply the custom format: dd/mm/yyyy

      Please let us know if you have any other questions.

  60. vguidos says:


    I would to express my sincerely thanks to you for this tutorial.-

    Thank you.-

    From El Salvador CA.

  61. Dimba says:


    then use text to column with "," as a delimiter and then use the wizard to fix the date to the correct format

  62. Alan says:

    Thank you! The DateValue function was exactly what I needed.

  63. Emma says:

    I'm not having any luck with VALUE or DATEVALUE.

    I have a column with dates that show as 1/18/2017 and say they're formatted as "general". I first tried just changing the formatting to "dates" but nothing changed, they still are basically text.

    I then tried using DATEVALUE and VALUE in a new column but it just returns #VALUE!

    I'm at my wit's end. If they had even written them YYYY/MM/DD I could at least have sorted them as text.

  64. Georgemtz says:

    I imported a text file into a blank Excel sheet. One of the columns has four characters values, i.e. JAN1 or MGG3, .... However, it seems that the format of the cell changes from General to Custom (d-mmm) = 1-Jan. If I change the format to Text, it changes to a number 43101. How do I get it back to JAN1? I have others that happen to be similar to other months JUN5, MAR9...

    • Hello, George,

      Please try applying the Text format to cells before entering the values. Thus, your initial records JAN1, JUN5, or MAR9 will be kept as they are.
      Otherwise, Excel will treat them as dates and convert right away accordingly. And I'm afraid the only way to get them back is to apply the Text format and enter the correct records manually.

  65. Vishal says:


  66. Vinay says:

    how to convert date 2012019 into mm/dd/yyyy

  67. Random User says:

    I am highly experienced, advanced and skilled. This is a fine, intelligent article, and written in a comprehensible level for novices while not so dumbed down that it is not an efficient resource for gurus. Extremely well done.

    Frankly, I never grasped the benefit of the seemingly silly or inert VALUE(), though I had stumbled onto the (equivalent?) utility of =A1+0 very early on. I've been doing A1+0 for decades; I man now replace that convention with VALUE() as a matter of style. Using A1+0 is sort of saying "This is a stupidly designed product, but I'm even stupider, that I am resorting to something as ludicrous as this."

  68. Random User says:

    I am highly experienced, advanced and skilled. This is a fine, intelligent article, and written in a comprehensible level for novices while not so dumbed down that it is not an efficient resource for gurus. Extremely well done.

    Frankly, I never grasped the benefit of the seemingly silly or inert VALUE(), though I had stumbled onto the (equivalent?) utility of =A1+0 very early on. I've been doing A1+0 for decades; I may now replace that convention with VALUE() as a matter of style. Using A1+0 is sort of saying "This is a stupidly designed product, but I'm even stupider, that I am resorting to something as ludicrous as this."

  69. Sarvottam says:

    Awesome Article. Thank You so much.

  70. Liaqat says:

    All world data delete number 03415744310 03016084001 03028927904 03104492453

  71. SmittyOttawa says:

    Humbly requesting help with this format, which I believe is text and already converting UTC to my local time (-4). How can I get this back to UTC in Excel?
    Many thanks in advance!

  72. Rey says:

    what is the formula to sum including alphanumeric of the following:
    3 yrs, 9 mos
    0 yrs, 2 mos
    = 3 yrs, 11 mons

  73. Random says:

    I want to use dates as period of time in one cell. e.g. 2019/05/01 - 2019/05/31.
    I use =eomonth function to get the date from other cell. e.g. =(EOMONTH(A1,-1)+1)&" - "&EOMONTH(A1,0)
    This only shows the date as value and don't know how to change into date format (long/short date does not work).

  74. Rushi says:

    I Want to convert date 18/05/2024 to 24/05/2018 plz help me

  75. Jim says:

    For people with ADHD, the scrolling ad on the bottom makes it darned near impossible to read the article. Thanks.

  76. Aju says:

    Dec 27 2018 3:55PM General format need help in converting to date and time format using excel

  77. Walter says:

    Thank you!!!!

  78. swanand says:

    I am looking for converting Thursday, January 01, 2015 date to UK date format. couldn't find a solution on the web but your website has helped. really saved a lot of time and frustration. great job. thank you

  79. Subhadeep says:

    How do I convert

    Aug 18, 2019 08:15CHECKLIST, which is in text format into date format in a separate column.

  80. Leah says:

    I am needing to convert a date from 20030331 to 03/31/03 or 20011204 to 12/04/01. How do I convert this?

  81. Dee says:

    I am trying to create passwords from user first name, last name, & DOB. I need just the 2 digit month and the 2 digit day from 9/10/2019 format. This is the formula I have working, but it drops the zero. =CONCATENATE(LOWER(LEFT(A4,1)&LOWER(LEFT(B4,3)))&MONTH(E4)&DAY(E4)) Result- abbb910. I need abbb0910. Thanks for your assistance!

    • Dee says:

      I figured out my own answer and wanted to share in case anyone else was looking. =CONCATENATE(LOWER(LEFT(A4,1)&LOWER(LEFT(B4,3)))&TEXT(E4,"mm")&TEXT(E4,"dd"))

  82. Greg says:

    How do I convert a series of negative date values to a date format - in particular; PS they are all French or Gregorian dates (because none are older than 20 Dec 1582). So just a straight conversion of a column of these negative numbers to a date numerical format (is ok).
    -82059 which is 29-04-1675 (or 29 Apr 1675)
    -83311 which is 24-11-1671
    Many thanks

  83. sean says:

    I want to use CONCATENATE function to combine data from multiple cells so it can be imported to a different program. However, when I run the CONCAT formula, the SHORTDATE value is returned as the TEXT value of the date.

    So, I need to convert a SHORTDATE format value to the same value but as text.
    For example:

    The text value '43721' returns a short date of '09/13/2019". But when I plug the cell with SHORTDATE of "09/13/2019" into my CONCAT formula, the result is returned at 43721.

    I need the SHORTDATE 09/13/2019 to be returned as text but still read 09/13/2019.
    Any idea how to do this?

  84. Abou says:

    with your post, i have save a lot of time.

  85. Will Clevenger says:

    Good day
    I have a question regarding the population of multiple cells based on the date range of cell a and b. So the example is, (AI21) is the user entry of the beginning date. (AI22) is the user entry of the ending date. Once this second date is entered, conditional formatting highlights this range of cells. I am trying to populate the highlighted cells with the drop down selection in (AI23). So if 4 cells are highlighted then the value selected in (AI23) will populate in those highlighted cells. I can send current state workbook if anyone could assist. Thank you again for your consideration. Regards

  86. kumar says:


    I have a column with 1560000000000 type data in it. How do I convert this text into a date format so I can use it to do further calculations?

    I've tried text to column (which I don' want to do in order to protect the data source). Is there a formula for this?

  87. Raj says:

    29/05/2000 ko twenty May two thousand

  88. Penn says:

    I've got a column of dates formatted as text using mmyy. I need to split them into 2 separate columns formatted as mm & yyyy. Any suggestions?

  89. Yensenbam Samiran says:

    20190924183744 this number is combined with date and time.
    how to convert this number to date and time separately by formula of command. I will be pleased if you help me to convert this.

  90. AN says:

    I have a date range in a spreadsheet:
    Dec 5-11 2019
    How to I convert that to show Thursday, Dec 5 to Wednesday, Dec 11?

  91. Ravindra Chaudhari says:

    when i pull the data from system date are come as below how we are convert this number in date please help me.

    Food Lic Issue Date Food Lic Valid Date
    0.1928125 0.151168981
    0.1928125 0.151168981
    This is very important to know me.

  92. Gaurav Kumat says:

    12/6/2019 : 2300, in This i need to extract only time with Time Format Like 11:00 PM

  93. sathish says:

    8.6750245000162E+26 i need this as ENROLMENT_NO_DATE lainst 8 digit date and remains are number
    kindly give a formula for better understanding.

  94. Tom says:

    Great article but i'm struggling with a data set I've imported from CSV.
    All data has been imported with cell formats as TEXT. I have date columns in the format 'MM/DD/YYYY HH:MM AM' and 'MM/DD/YYYY HH:MM PM'.
    I want to keep the original data values in sheet 1 and present these values in sheet 2 in the desired format (DD/MM/YYYY HH:MM) and have tried using the formula =VALUE(Text) and setting the cell format using custom to dd/mm/yyyy hh:mm.
    For some of the values this presents a date time format but as MM/DD/YYYY HH:MM (not what i want) but for other it shows a #VALUE! error.
    Examples (these covert to MM/DD/YYYY HH:MM) which is not the format i want:
    01/11/2020 10:07 PM
    01/11/2020 8:02 AM
    01/10/2020 5:11 PM
    These are not converted and show the #VALUE! error:
    01/13/2020 9:03 AM
    01/13/2020 8:50 AM
    01/13/2020 8:46 AM
    Seems that anything where the DD part of > 12 it does not like the value.
    I've searched everywhere for an answer to this and would really appreciate it if someone could help me.

    • Tom says:

      I actually found a solution to this myself in the end but thought i share it on here in case anyone else comes across this issue.
      It's not the neatest solution but it works.
      =VALUE(CONCATENATE(RIGHT(LEFT(TextDate, 5), 2), "/", LEFT(TextDate, 2), "/", RIGHT(LEFT(TextDate, 10), 4), " ", RIGHT(TextDate, LEN(TextDate)-FIND(" ", TextDate))))
      Then format the cell to be DD/MM/YYYY HH:MM

  95. Emmad says:

    Excellent article. Thank you.

  96. Peter says:

    could anyone help me with this one ?
    I need to extract week no. and year from text
    03-472018 should give 47-2018 or 47/2018
    02-522019 should give 52-2019 or 52/2019

    Thanks a lot.

  97. Shibbir says:

    May I convert 15-03-2020 to 1 5 0 3 2 0 2 0 for each value in different cell?

  98. Jesus says:

    I am trying to convert some results from my Left and Right formulas into dates and it will not work.

    Sample - Doe,J 20-01
    20-01 is read for our purposes as Year(2020)-Month(01). No matter how I have extracted the information I cannot convert it to date.

    Thank you in advance for your assistance

  99. Hazel Chow says:

    Hi, i extracted some data from sql, but my dates turns out as follows:
    How can i convert it into readable dates?

  100. Sheik says:

    HI , I have an issue with the date format when i copy data from server to my desktop.
    for eg :
    2017-02-01 11:00 PM converts to 1486008028000
    2016-120-01 11:00 PM converts to 1480651229000
    How do i retain my original date and time, what is the formula i can use.

    i have tried lot of formulaes posted in these forum but no luck.
    below are few i tried:
    =--TEXT(G1,"0000\/00\/00 00\:00\:00.000")
    =TEXT(LEFT(C4,4),"mm-dd-yy") & " " & TEXT(TIME(MID(C4,6,2),MID(C4,7,2),RIGHT(C4,4)),"hh:mm:ss")

Post a comment

Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)