Comments on: How to change date format in Excel and create custom formatting

The first part of our tutorial focuses of formatting dates in Excel and explains how to set the default date and time formats, how to change date format in Excel, how to create custom date formatting, and convert your dates to another locale. Continue reading →

Comments page 13. Total comments: 940

  1. Hi
    I am adding 10.8 days to a date, and it is returning as 1/18/2020 4:55:54 AM and I cannot use vlookup again this returned date. How do I format the return date to only date? So I can use as only date format for other formulas. Thank you.

    1. Hello Avarma!
      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 let me know in more detail what you were trying to find, what formula you used and what problem or error occurred. It’ll help me understand it better and find a solution for you. Thank you.

  2. Hellow Ablebits Team,
    Have Changed the US date format(mm-dd-yy) to English (U K) (dd-mm-yy).Existing dates have changed successfully But with new entries in dd-mm-yy it does work, however it is OK with
    mm-dd-yy entries. What to do to have both entries & display in dd-mm-yy UK format.
    Best Regards
    Abdul Aziz Khan

  3. Hi,
    I am wondering if I have information in mm dd hh:mm:ss yyyy, is it possible to transfer it to yyyy/mm/dd hh:mm:ss.
    Anne

    1. Hello!
      Please go to Format Cells, choose Number -> Custom Format and set
      yyyy/mm/dd hh:mm:ss

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

      1. Hi Alexander,
        I have the same issue as Anne, and change the format using the custom format option.

        However, I was wondering if there was any way to make the change as permanent, as every time I open an excel file, I have to change it, and this happens several times a day, so it gets a bit frustrating at times.

        Thanks in advance for your help

        1. Hello Tabish!
          When you enter a date or time in a cell, it displays in the default date and time format. This default format is based on the regional date and time settings specified in the Windows Control Panel and changes when you configure these settings in the Control Panel. Set the format there. I hope it’ll be helpful.

  4. Hi,
    This appears to be a great source of information.
    I have a date with a time as :
    05/12/2020 23:59
    My excel sheet is interpreting as 5 December 2020 23:59 however it is meant to be 12 may 2020 23:59. It is data that is downloaded and both time and date come a single entry in the table I download. is there any way to convert it to be correct. I have now been trying for over 5 hours!!
    help is needed please.
    Best regards,

    David

    1. Hello David!
      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.

      1. Hi,
        Thank you for getting back to me. The problem is that the the website generating the data gas a glitch and is presenting the day and month in reverse. So if it was the 13 May 2020, the cell contains 5/13/2020 but yet the excel sheet thinks it is the 5 of the 13th month which obviously causes problems. They are working ona fix to the site now but i would like to know if there is anything i could do to convert it in the meantime in case they cannot fix the issue.
        Hope to hear from you soon,
        David

        1. Hello David!
          Please try the following formula:

          =DATEVALUE(REPLACE(REPLACE(G1,1,2,MID(G1,4,2)),4,2, LEFT(G1,2))) + TIMEVALUE(REPLACE( REPLACE(G1,1,2,MID(G1,4,2)),4,2,LEFT(G1,2)))

          Please go to Format Cells, choose Number -> Custom Format and set
          dd mmmm yyyy hh:mm:ss
          I hope this will help, otherwise please do not hesitate to contact me anytime.

  5. hi
    i have problem in my excel sheet in date column. I have calendar period column in which date shows like 201810 in which last 2 digits are month and 1st 4 are year. i want to convert in date format like 10-2018. how to do this please help me with this.
    Thank You

    1. Hello Aman!
      Please try the following formula:

      =DATE(LEFT(A13,4),MID(A13,5,2),1)

      Please go to Format Cells, choose Number -> Custom Format and set
      mm-yyyy
      Or instead of the date in the cell, you can write text and not use custom format:

      =TEXT(DATE(LEFT(A13,4),MID(A13,5,2),1),"mm-yyyy")

      I hope it’ll be helpful.

  6. Great info, thx! I have the same question about capitals

  7. Hello Sir.
    How can we format the date using capital letter in excel for example 31 MAY 2020 instead of 31 May 2020?

    1. Hello Willy!
      The date format in Excel does not use capitalization. But you can use the formula

      = UPPER(TEXT(A1,"dd mmmm yyyy"))

      I hope it’ll be helpful.

      1. Hi Sir, your formula really helpful!!.. Thanks

  8. I need Year-Month-Date how to Possible?

    1. Hello Rabiul!
      I hope you have studied the recommendations in the above tutorial. Please let me know in more detail what you were trying to find, what format you used and what problem or error occurred. In that case I will try to help you.

      1. Dear Sir,
        In navigation problems we have situation where we have to find the duration between 2 times, e.g 23:00 and 01:25 then unable to do it

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

          =IF(A1 > A2,A2-A1+1,A2-A1)

          where A1 is 23:00 and A2 - 01:25
          I hope this will help, otherwise please do not hesitate to contact me anytime.

  9. Hi, I have entered in excel date as 3/01/2020 but it is displaying as 01/03/2020 i have tried date formats still it is not coming but, I need this date as United States format mm/dd/yyyy how can I change this help me.

    1. Hello Akhila!
      Please go to Format Cells, choose Number -> Custom Format and set
      mm/dd/yyyy;@
      I hope this will help, otherwise please do not hesitate to contact me anytime.

      1. This is not change have tried kindly help with some other way

  10. How to change 9/1/2019 format to Sep-19 in excel???

    1. Hello!
      Please go to Format Cells, choose Number -> Custom Format and set
      mmm-yy;@
      I hope this will help, otherwise please do not hesitate to contact me anytime.

  11. Thank you dear lady, very useful article

  12. How can i change this Date format 1/14/2019 12:00:00 AM in excel

    1. Hello Chetan, Please to Format Cells, choose Number -> Custom Format and set

      [$-en-US]m/d/yyyy h:mm:ss AM/PM;@

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

  13. Hi,
    I have entered different dates in first column. Just want to set 1 year validity for all the cells in that column and if it is more than 1 year the entered date must change its color. Waiting for your reply.

  14. Very helpful, thanks!

  15. Hello,
    In excel previous years. When I input Month and Day, and press enter, it will automatically input year today. So if input is Feb 5. It will show Feb 5, 2020.

    But now,same input, Feb 1, it takes "5" as year and gives me Feb 1,2005.
    Do you know how to change this format?

    Thanks

    1. Correction:
      But now, same input Feb 5, it takes "5" as year and gives me Feb 1, 2005.

  16. My date format is 02.26.2020.0it begins with month, date then year. Help me change it to begins with date, month then year

  17. My date format is like this 20200215 how can I change the date format without using TEXT TO COLUMN ( is there any formula for changing this one) Thanks

  18. I want to use like this 23-01-2020 but it automatically getting changed like 23/1/2020

    1. What's up doesn't work because of date format. Plz kindly help me change it

  19. When I sort my data by year it orders it by the months first, then day, then year, for example:
    01/12/2017
    01/13/2017
    01/13/2018
    01/15/2017
    02/01/2016
    Obviously, this isn't chronological. I would like it to order my data by year, then month, then day, then time. yyyy/mm/dd h:mm
    I typed this in the custom field and it did nothing. Is there another way to accomplish this?

  20. How to convert 10/26/2019 2:27:50 PM int DD/MM/YY format.

  21. Could you please convert the value in date format ,value is 46325635 ,date is 01.03.2019
    Please help to do by using formula

    Thank you
    Sanoop

  22. Hello, is there a way to save a custom format so that it may be accessed from all new spreadsheets? I export a file from another program each week, and create a format dddd, mmm-dd-yy and it works great, but I have to recreate dddd, mmm-dd-yy every time. Older versions of excel used to save my custom formats, but this one does not seem to do so. Excel 2010 Version 14.0.7232.5000
    Thank you.

  23. 23.12.2016 to 23/12/2016

  24. Hi, I want to change date that excel is treating as text to date mm, dd, yyyy across different cells that have correct dates in between. So, 5 cells in a column are treated as text the next 3 are correct and my formula is working then the next 4 are treated as text. So on and on, I want to correct for all at once not manually. Please help

  25. i want to change date to month format.pls help.

  26. Hi,
    Any ideas how to change dates like 20JUN19 into 20.06.2019 or any date format without mass replace operation? Maybe a macro with months and their ".06." counterparts in digits? There are two columns with such dates...
    Thank you

  27. Hi,
    Does anyone know how we can changed the date format from 08/02/2019 (English Caribbean) to 02/08/2019 English (United Kingdom)?
    When changing the format for 08/02/19 it is appearing as 08 Feb 19.

  28. I am trying to enter date in excel i.e. 03/01/2019 but its 3/1/19 how to change this I also tried through format cell option but no use.

  29. I tried to change the date format from 24/09/2019 into 24/09/19, I want this format to be shown in formula bar too. I tried with format cell but could not have desire result....
    kindly help....

  30. I have a date in the format below coming in excel.
    2019-08-10T21:27:09.000-04:00

    How do I convert it into 08/10/19 21:27:09 ?

  31. I need to change date from 03/01/2019 to 03/01/2017; this change is for the whole month. How can I change the year only if there are several field to change all at once.

  32. Hi,
    I'd like the first year in a chart to read "1999" with four digits, and all the later years just as "yy" with two digits. Is that possible with a custom format?

  33. Can i change the date format from-mm/dd/yyyy to yyyy.

  34. How to change the Date format - "mm/dd/yyyy" to "dd-mm-yyyy" ?

  35. Hi
    I am Mohammad Hafeez how can i fill cell date of last data entry so please help.

  36. how to convert date like this 26.04.2007 to format like this 04/26/2007..Anyone can help me?

    1. Replace . with / by choosing the required cells (shortcut key is CTRL + H)

  37. I need that by format put the number but show the month without formulating in another column
    I punt in custom format mmm but if I put 2 or 5 instead showing feb or may keeps showing jan.

    Hope someone know how fix it.
    Thanks in advance

  38. i want to convert Mmm dd yyyy to dd/mm/yyyy format, i tried all means and i am unable to do it. can any one help me please.

    1. Hi Sai,

      Try these steps:
      1. Select the cells with your dates that you want to reformat.
      2. Press Ctrl+1 to open the Format Cells dialog.
      3. On the Number tab, select Custom from the Category list and type the following format code in the Type box: dd/mm/yyyy
      4. Click OK.

      If this does not work, then most likely the original values are text, not dates. The following tutorial explains how to distinguish text-dates from normal dates and convert them to the Date format: How to convert text to date in Excel.

  39. Hi

    How do I change 21/05/2019 to 2019/04/11 in excel 2010.

  40. Hi all,
    I have a problem in my excel sheet. When i enter "time" in excel, it is coming along with date for Ex: 05-01-1900 7:12:00 AM. How can i sort out this issue. Even i try to change in custom date format, i could'nt reach to my aim. So please give me the correct answer.

    Thanks
    Thujesh

  41. How to format, date from 24.03.2017 to 24/03/2017
    Its not working despite having tried the custom format options.

  42. Good Info

  43. I calculated a formula for tenure and I want to custom format the tenure like this (e.g.: 05 Years, 08 Months and 15 Days).

  44. I need this format date:
    2019 111 (January, 11, 2019)
    The format yyyymmdd does not work for me
    20190111 (January, 11, 2019) It is incorrect
    My solution is
    year(SomeDate) & format(month(SomeDte),iif(month(SomeDate)<10," #","#")) & day(SomeDate)
    My question is then, is the correct way to do it?

    thank you

  45. Can I convert 04.05.2019 to 04/05/2019 i.e. from (.) to (/) separator. Anyhelp would be appreciated.

    1. format(SomeDate,"dd/mm/yyyy")

  46. how to change the date text of a cell written March, 2019 as Mar-19

    1. format(SomeDate,"dd of mmmm, yyyy")

  47. In excell if i enter number it is auto changing into date format, how to arrest it ?

  48. MS Excel and it's auto-date configuration.

    Anybody have a way to get around this problem?
    We have a few million rows of alphanumeric data that ranges from AAA0001 to ZZZ9999.
    When I export the data into Excel it converts some of these into dates.
    APR1899 = APR1899 <- Good
    APR1900 = Apr-00 <- Bad
    APR1901 = Apr-01 <- Bad
    and
    AUG1899 = AUG1899 <- Good
    AUG1900 = Aug-00 <- Bad
    AUG1901 = Aug-01 <- Bad

    The work around is to format the column as text before pasting it but I'd like to STOP Excel from auto formatting as a date.
    Anybody have a way to fix this?

    Thanks,
    Kenny

  49. How can I store my own date-time format so it's available in the drop down list?
    I'm constantly exporting data from SQL and have to convert it from this 47:10.7
    to this 03/08/2019 12:47:10.670.
    I'd like to have a saved date-time format as an option in the drop down.

    Thanks,
    Kenny

  50. How do I change 10/4/2002 to 10042002? Please help.

    Thank you

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 :)