Comments on: 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 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. Continue reading →

Comments page 4. Total comments: 512

  1. Hello,

    How to convert 2020-04-17T10:58:58Z to DD/MM/YYYY
    Thank You

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =DATE(--LEFT(D3,4),--MID(D3,6,2),--MID(D3,9,2))

      The formula returns the date. You can use any date format you need.

  2. Hi!
    I have a question, I wanted to get a continuous string of yyyymmm, but if I do it I get something like 201913. For example, I started off with date 201906, and I wanted that to keep going into 202006 without me having to change the 201913, 201914, 201915 etc. Is there a way to do this? For my formula I just do the cell+ 1, which is why i get the 201913.

    Thank you!

    1. Hello!
      If you wanted the string "yyyymmm" then 201913 is not possible. After all, month 13 does not exist! So what numbers do you want? In addition, if you add 1 to the date, it means +1 day, not +1 month.

  3. Hello, I want to put 3 reminder dates at least in one cell, and then used those dates to determine the last activity date of all dates in my sheet, meaning there would be different cells selected which will have one single date. Formating for reminder dates would be (01/06/2020 (alt+enter) 06/06/2020 (alt+enter) 12/06/2020)

    1. Hello!
      In this case, your dates will be written as text. Explain what you want to do with these cells? Give an example of the desired result. It’ll help me understand it better and find a solution for you. Thank you.

  4. Hi,
    I need to convert dates in the following format - MMYY - from TEXT to DATE format. The dates are entered as follows:
    114 for 01/1/2014
    1015 for 10/1/2015
    616 for 06/1/2016
    The day is always understood to be 1 so it is never entered. The year is always understood to start with 20 so it is never entered. Zeros are never entered in front of single-digit months.
    Do you have a formula to convert?
    Any help would be appreciated!

    1. Hello Sandra!
      To convert text to date, use the LEFT and RIGHT functions

      =DATE("20"&RIGHT(A1,2),LEFT(A1,LEN(A1)-2),1)

      I hope it’ll be helpful.

  5. I have two columns with date and time stamp as strings ex. June-23-2020 12:38:42 PM.
    First I want to convert these to normal date and time format so that I can find time different between two columns. Please suggest.

    1. Hello!
      To select a date from your text, use the formula

      =DATEVALUE(MID(Z1,FIND(" ",Z1,1)-4,4)&"-" &MATCH(LEFT(Z1,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"}) &"-"&MID(Z1,FIND(" ",Z1,1)-7,2))
      To select time from the text, use the formula

      =TIMEVALUE(RIGHT(Z1,LEN(Z1)-FIND(" ",Z1,1)))

      Hope this is what you need.

  6. I took out an output from some tool which gave me the numeric value of the datetime -
    June 25, 2020 16:06:37 as 1593065197208, when i exported it in csv.
    i am not sure what 1593065197208 is and how to convert it back to

    1. ** correction **
      convert it to June 25, 2020 16:06:37 in excel. Can you please explain what that number is and how to convert it.
      Thanks in Advance.

  7. Glad to see that you have replied to almost all queries. I have tried to follow many options that have been provided but unable to get the text into date format. I have this number in text format that in reality is last-logged in date & time stamp in source system. When retrieved it comes as text as 132374551396990975
    How can i format it into date or date & time stamp.

  8. Hello,
    I'm trying to convert text to dates from text that has a variable number of digits i.e.

    28042020 = 28/04/2020
    29042020 = 29/04/2020
    4052020 = 4/05/2020
    5052020 = 5/05/2020

    Is there a formula that will recognise that there is a digit missing (the "0")?

    1. Hello Max!
      If I understand your task correctly, the following formula should work for you:

      =DATEVALUE(TEXT(M1,"##-##-####"))

      Hope this is what you need.

  9. Hi, Can you help me turn an identification into a date? I have a person's initial followed by their DOB, and want a column that reads just the DOB. For example, AB010107 to 1/1/07.

    1. Hello Mel!
      Extract numbers from text. Then convert these numbers to a date.

      =DATEVALUE(CONCATENATE(LEFT(CONCAT(IF(ISNUMBER(--MID(A1,ROW($1:$93),1)), MID(A1,ROW($1:$93),1),"")),2),"/", MID(CONCAT(IF(ISNUMBER(--MID(A1,ROW($1:$93),1)), MID(A1,ROW($1:$93),1),"")),3,2),"/", RIGHT(CONCAT(IF(ISNUMBER(--MID(A1,ROW($1:$93),1)), MID(A1,ROW($1:$93),1),"")),2)))

      Remember to apply the date format to the cell with the formula.

    2. Also the date format I want is month/day/year.

      1. Too bad you didn't say that before

        =DATEVALUE(CONCATENATE(MID(CONCAT(IF(ISNUMBER(--MID(A1,ROW($1:$93),1)), MID(A1,ROW($1:$93),1),"")),3,2),"/", LEFT(CONCAT(IF(ISNUMBER(--MID(A1,ROW($1:$93),1)), MID(A1,ROW($1:$93),1),"")),2),"/", RIGHT(CONCAT(IF(ISNUMBER(--MID(A1,ROW($1:$93),1)), MID(A1,ROW($1:$93),1),"")),2)))

  10. Hi! How can I convert 20th Mar 2020 into 20/03/2020 format? I've tried all the above suggestion but it still not working. Thanks!

      1. It doesn't work out. Please note that it comes in from an export file as a general format in a .csv excel sheet. Thank you!

        1. Hello!
          The formula I sent to you was created based on the description you provided in your first request. However, as far as I can see from your second comment, your task is now different from the original one. Hence, the formula fails to work. First convert your .csv file to Excel.

  11. Good morning,
    Could you please advise how could I convert data "202001" to JAN-20?
    Thank you in advance!

    1. Hello!
      If you want to convert data to a date, use the formula

      =DATEVALUE(RIGHT(A20,2)&"/1/"&LEFT(A20,4))

      and then apply a custom format

      Mm-yy

      To convert data to text as a date, use the formula

      =VLOOKUP(--RIGHT(A20,2), {1,"JAN";2,"FEB";3,"MAR";4,"APR";5,"MAY";6,"JUN";7,"JUL";8,"AUG";9,"SEP";10,"OCT";11,"NOV";12,"DEC"}, 2,0)&"-"&MID(A20,3,2)

      Hope this is what you need.

  12. I have used the DATEVALUE function in the past and it works fie for me when the date string is in d/m/yyyy format. Now I received files files with the date in m/d/yyyy format and the function is returning an error. I'm guessing that DATEVALUE expects the string date format to be per Windows region settings, or some setting in Excel. Is there a way to "tell" DATEVALUE what the date string format is?
    I am using Excel 2007 under Win 10-64.

    1. Hello!
      You need to change the date string according to the regional format of Windows. Use something like this for the DATEVALUE function

      =DATEVALUE(MID(B11,SEARCH("/",B11,1)+1,SEARCH("/",B11,SEARCH("/",B11,1)+1)-SEARCH("/",B11,1)-1) & "/"&LEFT(B11,SEARCH("/",B11,1)-1) & "/"&RIGHT(B11,4))

      Instead of the format m/d/yyyy you get d/mm/yyyy

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  13. how to convert date to text

  14. How do I convert Date A's format to Date B's format?

    Date A: 9/25/2019 21:52
    Date B: 01-07-2020 3:59:00 AM

    1. Hello Adam!
      I think this article will help in solving the problem of how to change the format of the date and time.
      I hope this will help, otherwise please do not hesitate to contact me anytime.

  15. Hi there!
    I have a narration in cell D2 like so "ELECTRICITY CHARGES Bill for the month of April 2019".

    Is there a formula which would return just the month i.e. "April"? I want to be able to use that for a pivot.

    1. Hello!
      To extract the penultimate word from the text, use the formula

      =MID(A1,FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-1- LEN(SUBSTITUTE(A1," ",""))),1)+1, FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)- LEN(SUBSTITUTE(A1," ",""))),1)- FIND("*",SUBSTITUTE(A1," ","*", LEN(A1)-1-LEN(SUBSTITUTE(A1," ",""))),1)-1)

      Hope this is what you need.

  16. Sir,

    If my date is in this format 05-17-2020 so tell me which for i use for change the format in dd-mm-yyyy

    1. Hello!
      If I understand correctly, you want to change the date format.
      Check what date format you have installed on Windows. Go to Control Panel – Time – Region Settings – Change data formats
      If you do not want to change the date format for all files, use the second method.
      Please go to Format Cells, choose Number -> Custom Format and set
      dd-mm-yyyy

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  17. hi,
    How Do to convert this text(250220) into date format(12-Feb-2020).
    250220 - DDMMYY to DDMMYYYY
    look forward your support
    regards
    satish

    1. If your system date format is DD-MM-YY, then you can use the formula to turn text into a date

      =DATEVALUE(TEXT(D1,"##-##-##"))

      The second version of the formula

      =DATE("20"&RIGHT(D1,2),MID(D1,3,2),LEFT(D1,2))

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  18. Hey Alexander,

    I'm trying to get a formula to turn "May 29th 2020, 23:58:18:000" (which comes in from an export file as a general format in a .csv excel sheet), into "2020-05-29" in one cell and "23:58:18" in a different cell both formatted into a date so that it can be filtered via pivot charts through the year, month, day, and time of day.

    I've tried a few different ways of doing this already, but am unable to get the formatting to work correctly, any suggestions?

    Thanks in advance, you seem very knowledgeable in excel!

    1. Hello Tim!
      If I understand your task correctly, the following formulas should work for you

      =DATEVALUE(MID(A10,SEARCH(" ",A10,1)+1, SEARCH("th",A10,1) -SEARCH(" ",A10,1)-1)&" "&LEFT(A10,SEARCH(" ",A10,1)-1) &MID(A10,SEARCH(",",A10,1)-5,5))

      and

      =TIMEVALUE(REPLACE(MID(A10,SEARCH(",",A10,1)+2,50), LEN(MID(A10,SEARCH(",",A10,1)+2,50))-4,4,""))

      I hope this will help, otherwise please do not hesitate to contact me anytime.

      1. Perfect,
        Thank you!

  19. Hi Alexander,
    How I can convert the below date with time to date only dd/mm/yy?
    2019-11-13 07:10:24.858757 -> dd/mm/yy
    Best Regards

    1. Hello Arni,
      If you want to change the appearance of the cell, but not change its content, use the Custom Format. Please go to Format Cells, choose Number -> Custom Format and set format dd/mm/yy.

      If you want to remove the time value from the date, then use the formula that will refer to this cell

      =DATEVALUE(TEXT(D1,"mm/dd/yy hh:mm:ss"))

      where mm/dd/yy - is system date format.

      Check what date format you have installed on Windows. Go to Control Panel – Time – Region Settings – Change data formats
      I hope this will help, otherwise please do not hesitate to contact me anytime.

    2. Sorted now.

  20. Hi,

    I want advice on a formula please, I want to to generate a future date 6 months in the future to be calculated from a start date and a word, so for example: the first date would be in one column, the word high in the next column would generate a future date 6 months on, but only generated when the word high is in the second column
    01/01/2020 High 01/06/2020

    Thank you

    1. Hello Cadi!
      If I understand your task correctly, the following formula should work for you:

      =IF(B1="high",EDATE(A1,6),"")

      I hope this will help

  21. How do you convert this kind of text to date? For example, 7111978, 3151980. I tried using the convert text to date but I am not getting anywhere.

    Thank you

    1. Hello Barbara!
      The formula with which you can convert tex to date, I wrote above for you. Please note that in order for this formula to always work, the number of digits in the month number must always be 2.
      If you, for example, have written 1112019. How can the formula determine whether it is November 1 or January 11?

  22. Hi,

    Can someone please help me with converting text to date e.g 7111978. I tried using converting text to date. I followed the steps but still not getting anywhere.

    1. Hello Barbara!

      If I understand your task correctly, the following formula should work for you:

      =DATE(RIGHT(B5,4),MID(B5,LEN(B5)-5,2),LEFT(B5,LEN(B5)-6))

      I hope this will help

  23. how can i convert below number into Date
    1052019
    Basically it 05th of October 2019 but i need to convert into proper date format so can you help me out to solve this issue.

    1. Hello FURQAN,
      Please try the following formula:

      =DATE(RIGHT(TEXT(A1,"###"),4), LEFT(A1,LEN(TEXT(A1,"###"))-5), MID(TEXT(A1,"###"), LEN(TEXT(A1,"###"))-5,2))

      however, the date of October 15, recorded as 10152019, this formula will not calculate correctly.

      it is necessary that the day is always written as a two-digit number, for example, October 5 as 10052019. Then you can use this formula:

      =DATE(RIGHT(TEXT(A1,"###"),4), LEFT(A1,LEN(TEXT(A1,"###"))-6), MID(TEXT(A1,"###"),LEN(TEXT(A1,"###"))-5,2))

      I hope it’ll be helpful.

  24. What formula would work to convert two separate date headers into one and then converting them into the MM/DD/YYYY format? In the example below I have the birthday in column "E" and and birth year in column "F". I think ideally I would merge that to show January 20th, 1930 in one column and then change the format after. I tried concatenating those two and then format cells --> date but it wouldn't let me change the date that way.
    Example:
    Birthday Birth Year
    January 20th 1930

    1. Hello Matt!
      Please try the following formula:

      =DATEVALUE(MID(A2,FIND(" ",A2,1)+1,2) & "-"&LEFT(A2,FIND(" ",A2,1)) & "-"&B2)

      Then go to Format Cells, choose Number -> Date and set format you need.
      Hope you’ll find this information helpful.

  25. Hi, I have dates input as a continuous number - yyyymmdd and I would like to have it as a date - ddmmyyyy.
    For example, I have 20180808 in cell D2 - I was trying the following formula based on the above guidelines. Could you tell me where I'm going wrong?
    =DATE(LEFT(D2,4), MID(D2,5,2), RIGHT(D2,2))
    Thanks

    1. Hello Henry!
      To convert a number to a date, you must first convert it to text.
      Please try the following formula:

      =DATEVALUE(LEFT(TEXT(D1,"###"),4)&"-"&MID(TEXT(D1,"###"),5,2)&"-"&RIGHT(TEXT(D1,"###"),2))

      Then go to Format Cells, choose Number -> Date and set format you need.
      Hope you’ll find this information helpful.

      1. =DATEVALUE(LEFT(TEXT(D1,"###"),4)&"-"&MID(TEXT(D1,"###"),5,2)&"-"&RIGHT(TEXT(D1,"###"),2))

        if there is any wrong in this formula instead of above
        =RIGHT(D1,2)&"-"&MID(D1,5,2)&"-"&LEFT(D1,4)

  26. We need to have formula to convert text to date using specified format - in the same manner as TEXT function works.

  27. I have a Problem.In Excel CellS5=IF(G5="","",">="&G5)this formula,when i enter a date in G5 as 01/04/20 the cell S5 shows the result as >=43922.What to do? Please help me.Thanks.

    1. Hello Nitin!
      Cell S5 formatting as a number. In cell S5 go to Format Cells, choose Number -> Date and set format you need.

      1. Thank you Alexander. Your advice helped me a lot! Have a great year!

  28. Hi,
    Good day!
    Could someone help me covert these,
    Example:
    1170217 to 17 Feb 2017,
    1180816 to 16 Aug 2018,
    930524 to 24 May 1993

    Thanks

    1. 30607061701471
      i need to covert this number to the below
      3
      060706 date of birth which will be from left 06 is the year (2006) 07 is the month (July) and 06 is the date
      and i don't need to change the rest of it

      1. I finally found it,

        =IF(LEFT(A2,1)+0=2,DATE(MID(A2,2,2),MID(A2,4,2),MID(A2,6,2)),DATE(MID(A2,2,2)+100,MID(A2,4,2),MID(A2,6,2)))

    2. Hello Richard!
      If I understand your task correctly, the following formula should work for you:

      =DATE(IF (LEN(A1)=7,"20" & MID(A1,LEN(A1)-5,2), MID(A1,LEN(A1)-5,2)), MID(A1, LEN(A1)-3,2), RIGHT(A1,2))

      Please go to Format Cells, choose Number -> Custom Format and set
      dd mmm yyyy
      I hope it’ll be helpful.

  29. 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.

    1. 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

  30. 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

    1. I posted a question in a comment section. I didn't look first sorry folks.

  31. 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!

    1. 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"))

  32. HOW TO CONVERT DATE 01/05/2019 INTO MMM-YY FORMAT (MAY-19).

    1. Press ctrl+1 in format cells , select customs and type: yyyy-mmm

  33. 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...

    1. 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.

  34. 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

    1. 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.

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

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

    1. Doina:
      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.

  37. Moin:
    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.
    =RIGHT(A22,2)&"/"&MID(A22,5,2)&"/"&LEFT(A22,4)

    1. THANK YOU!~! :D

  38. 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"?
    thanks
    moin

    1. Moin:
      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.

      1. 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???

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

    1. Gary:
      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.
      =DATE(INT(A11),1,MOD(A11,1)*(DATE(INT(A11)+1,1,1)-DATE(INT(A11),1,1)))

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

    1. Suresh:
      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 "@*."

  41. 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.

    1. 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.

  42. 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!

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

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

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

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

    1. =RIGHT(A1,4)&MID(A1,3,2)&LEFT(A1,2)

  45. 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!

    1. 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"

  46. Hi,

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

    Thank you in advance!

    1. 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:

      =DATEVALUE(MID(A1,5,11))

      Where A1 is the cell that contains your text string.

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

      1. 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.

        1. Vlad:
          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.

  47. 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"

    1. this is my issue too... did you find any solutions?

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

    1. Hello,

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

      =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"/12/","-DEC-"),"/11/","-NOV-"),"/10/","-OCT-"),"/9/","-SEPT-"),"/09/","-SEPT-"),"/8/","-AUG-"),"/08/","-AUG-"),"/7/","-JUL-"),"/07/","-JUL-"),"/6/","-JUN-"),"/06/","-JUN-"),"/5/","-MAY-"),"/05/","-MAY-"),"/4/","-APR-"),"/04/","-APR-"),"/3/","-MAR-"),"/03/","-MAR-"),"/2/","-FEB-"),"/02/","-FEB-"),"/1/","-JAN-"),"/01/","-JAN-")

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

  49. 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?

    Thanks!

    1. you need to make the column width wider

      1. 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.

  50. 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

    1. Hello, Gaurav,

      Please try using a VBA macro. We are always ready to help you, but we do not cover the programming area (VBA-related questions).
      You may try to find the solution in VBA sections on mrexcel.com or excelforum.com.

      Sorry we can't assist you better.

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)