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 2. Total comments: 936

  1. Hi,
    I needhelp in converting the date format from Estonian to English US.
    In Estonia the date is written in this format 07.03.2024 ( March 7th , 2024).
    I need a way to convert this to 03/07/24 (English US format).
    I need this for an analysis I am doing using Pivot tables; they do not recognize the Estonian format.
    I have tried changing the format of the cells I am copying into, but this has not worked.

    1. Hi! All the necessary information is in the article above. You can change default date format in Windows Regional settings. You can also use a custom date format in the cell: "mm/dd/yy".

  2. Good afternoon,

    I am struggling as trying to convert some information sheets sent to me from a Date formula DD/MM/YYYY, Hour:Minute into just showing the date DD/MM/YYYY.

    I have tried using the DateValue formula, the Text formula and also Int formula.

    The cells I want to change are definitely showing as formatted as Date in the Format Cells menu. Howvever they also won't change if I try to change them to a number as if they are stuck in some way.

    Any help would be most gratefully received. I am currently using XLOOKUP formula's to check what we have been paid against what we have billed and need the date to just be the date (not time as well) so the two cells will match etc.

    Many thanks in advance
    Julia

    1. Hi! Your cells contain a text string. Therefore, the format of the cell does not matter. Try to extract the first 10 characters using the LEFT function and convert them to time using DATEVALUE.
      For example:
      =DATEVALUE(LEFT(A1,10))

      You can also find useful information in this article: Excel: convert text to date and number to date.
      I recommend paying attention to the Text to Date tool. This tool easily converts text strings of different formats to dates. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.

      1. "=DATEVALUE(LEFT(A1,10))" this formula doesn't work , I did it using =CONCATENATE("'",MID(B4298,5,3),MID(B4298,2,3),MID(B4298,8,5))

        This is needed because when you want to load data into the database, this format is needed in many cases

        1. Hi! The formula I suggested to you is based on the date format you wrote. In fact, you have a different date format.

  3. How to change 17.02.2024 to 17-02-2024 format? Please provide a formula

  4. When nothin works. do the as follow
    copy the all column in a new tab
    with reemplace tool, reemplace / or - for ,
    select the all column and go to data, text to columns
    select separate for comas
    next and finish
    you will have 3 new columns
    in a forth column write / or -
    y a 5 colunm write =CONCACT( and select the way that you want to place your data
    copy the concat values and then place where you want using special paste, values
    done!

  5. Is there a way to change the default year like you can in Numbers? I'm entering data from 2022 & don't want to enter the year for every entry; though, if i don't, it auto-populates 2024.

    1. Select the cell(s) you would like to format with a year other than your system’s year, if you want the default year to be 2022, you can enter:
      dd/mmm/”22″

    2. Hi! Excel cannot tell which year you want to use if you do not enter a year in the date. Therefore, by default, it uses the current year from the system date of your computer.

  6. How I can convert date like 1/31/2024(mm/dd/yyyy) into 01/31/2024(mm/dd/yyyy).
    I have already used mm/dd/yyyy formula but month not starting with 02.
    Any help?

  7. I have the date format updated as 11/29/2023 however it’s displaying 45259. When I edit cell I can see the date and also in the formula bar but when move it to another cell 45259 is the number I can see. The formatting cell shows date however the number is being displayed please help

    1. Hi! This problem is described in detail in the article above. When moving a date to another cell, change the format in that cell from General to Date.

  8. I format my column to date (mm/dd/yyyy). I type in a date and a different date shows up. Example 6/15/2023 and I get 6/9/2068 or
    12/14/2020 and I get 6/7/2232. What am I doing wrong?

    1. Hi! Unfortunately, I was unable to replicate your problem in my workbook. Check what default date format is set in your Windows local settings.

  9. How could i convert 202311131058 to 31/11/2023 10:58AM? Thanks

    1. Hi! Extract the desired digits from the text string using the LEFT and MID function, and use the DATE and TIME functions to create the date and time. The formula below will do the trick for you:

      =DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),MID(A1,11,2),0)

  10. Hi, I have dates that are in US custom format - 04/01/2023 13:00 the custom format is dd/mm/yyyy hh:mm.

    I need the dates to be in UK format, I've tried using the text to columns approach but this doesn't work as the dates are in a custom format not text. Even after changing the format to text his doesn't work

    Creating my own custom format - [$-409]dd-mmm-yyyy hh:mm AM/PM doesn't solve the issue that the days and months are the incorrect way around.

    Do you know how I can change the dates to be 01/04/2023 13:00 instead of 04/01/2023?

    1. Hi! If I understand your task correctly, try the following date format: mm/dd/yyyy hh:mm instead of dd/mm/yyyy hh:mm

  11. I am using formula =IF(E2,"2000") but I can't get to change the format cell to number. Seek your advise. TQ

    1. Hi! If you want to change the cell format to numeric, use this guide: Custom Excel number format. If you want to change the date format and show only the year, use the instructions in the article above. If I have not guessed, please explain what you want to do.

  12. How can I convert 202409 (that's already in the spreadsheet) to 9/24 or 9/1/24?

  13. Hi, I have a file some dates showing 2023-11-01, some showing 01/11/2023, but I cannot align them as 202-11-01 even i have try above methods, any one got any ideas on this?

    1. Hi! I'm assuming your dates are recorded as text. This has been discussed many times in the comments below. Use the substring functions LEFT, RIGHT and MID to extract the numbers you need and use them in the DATE function.
      For example, for text 01/11/2023 try this formula:

      =DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))

      I recommend paying attention to the Text to Date tool. This tool easily converts text strings of different formats to dates. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.

      1. Thanks for the reply. I have checked but the cell has been set up as YYYY-MM-DD format but it's just showing slash format no matter how. Thanks for this formula which helped to resolve the problem; just found another way to get this corrected - copy the date and then paste special-add also help.

        1. Great ... This helped me

  14. How do I convert date to number in months
    Start End Months
    24/10/2023 21/04/2024 6
    25/10/2023 24/11/2023 1

  15. How do I do this on excel?
    Disbursed On Maturity Date Months
    24/10/2023 21/04/2024 6

  16. Hi.
    How can i convert date in this format MM/DD/YY to weeks in a year i.e Week 30

  17. Hello, how can I convert from YYMM --> 2203 to become actual month and year --> Mar 2022 ??

  18. Hi, I'm looking to change my date format from mmmm dd to mm/dd/yyyy. I want to add 2000 as the year for all of my dates, but my original date format doesn't include a year.

    *Looking for formula to go from 'July 11' to '07/11/2000'*

    Thank you

    1. Hi! Please re-check the article above since it covers your task. Read the paragraph: How to create custom date and time formats. Format mm/dd/yyyy.

  19. I have a date format Aug 11, 2023 but i can't able to change the format as YYYY-MM-DD.

    Please let me know is there a way to fix this?

      1. Thanks for this.It was very helpful to fix my issue.

        Many thanks for the help

  20. Looking to use format dd/mm/yy/ddd to give, for example 03/10/23/Tue as I then want to use that with a formula to generate invoice number using first six digits (and surname from elsewhere). Day is important for other functions in the business.
    I cannot get it to accept this so end up using an older year's cell copied in and then change every time - that works but seems ridiculous that I can't figure out why it won't do it now.

    1. Hi! I don't really understand what your problem is. To concatenate a date with text, convert the date to text using the TEXT function. For example,
      TEXT(A1,"dd/mm/yy/ddd") & B1
      If this is not what you wanted, please describe the problem in more detail.

  21. How do I convert a date format from YYYY/MM/DD to MM/DD/YYYY?

  22. Hi how can I convert date stored as text in format 030723 into 03/07/23 stored as date
    Thanka

      1. It works! Thanks

  23. Why does INT(2023-01-06T00:00:00Z) lead to an error, instead of isolating the DATE?

    I can't find a way to remove the time from the timestamp; always get Error Value!

  24. IT,S REALLY GOOD WAY TO RESOLOVE THE PROBLEM,THANKS

  25. Using Format/Format Cells... to alter "mm/dd/yyyy" to Custom "d mmm yyyy" worked, but not for 19th century dates.

  26. how to change year format for example 01-sep-2023 to 01-sep-2024

  27. 23rd Aug 2023 convert to 23-Aug-23 format

  28. 15/02/20 19/55/12 how to change time form 19.02.2020 15:07:12

  29. Hi! Need your help pls. I downloaded a data with this sample date and time
    Fri, 04 Aug 2023 01:12:40 GMT
    However, when I opened the file it shows 1691111560. Please advise. Thank you!

  30. Hi Alex,
    My Excel show as 08-01-2023 18:00 and read as by excel as 8 Januari 2023 09:00 PM.
    How to change excel to read as 1 August 2023 09:00 PM ? I usually using text to column, but now its not working, maybe because there's hour number in that date.. I dunno..

    1. Use text to coloumn and change the mdy into dmy

  31. Can someone help how to fill the dates in series without changing the hh:mm?
    07-Dec-22 16:20
    00-Jan-00 16:22
    00-Jan-00 16:27
    00-Jan-00 16:36
    00-Jan-00 17:03
    00-Jan-00 18:09
    00-Jan-00 18:28
    00-Jan-00 18:38
    00-Jan-00 18:49
    00-Jan-00 19:06
    00-Jan-00 19:18
    00-Jan-00 19:28

  32. Can someone help how to fill the dates in series without changing the hh:mm?

  33. Is there way to change a mm/dd/yyyy date into the week of the month or week of the year it falls into?

  34. very good and easy

  35. Bagaimana kalau mau tulisan bulannya huruf kapital
    contoh JULI 15, 2023

    1. If you use "mmmm" in the date format, you will get the first letter of the month capitalized. To capitalize the entire month name, convert the date to text using the TEXT function and UPPER function. For example,

      =UPPER(TEXT(A2,"dd.mmmm.yy"))

  36. how to write date in format March' 2021

  37. What date would it be in 41.56 years from now

  38. How to conert date 01/01/2020 to 01.01.2020

    1. Go to replace: replace from / to .

  39. if you receive a file with dates in American format ("mm/dd/yy") and you want to convert them to European format,
    here's a simple method:
    suppose the date in cell B3 is: 4/15/2013 and you want to convert it to 15/04/23,
    you can do it in one formula:

    =LET(a,TEXTSPLIT(B3,"/"),DATE(INDEX(a,3),INDEX(a,1),INDEX(a,2)))

  40. Hi. I am used to inputting dates using the format MM/DD/YY or YYYY. However, after updating, it seems that Excel doesn't read this as a date anymore. It would only recognize a date if my input format is DD/MM/YY. How do i make Excel read dates inputted as MM/DD/YYYY? Thanks in advance!

    1. Hi! Pay attention to the following paragraph of the article above: How to change the default date and time formats in Excel.

  41. Hi, I am trying to format date in excel but a hashtag # keeps displaying from the output.

    1. Hi! Specify which date you are trying to format and which format template you are using. Also pay attention to the width of the column. Perhaps the column needs to be wider.

  42. Your articles are excellent – very clear and complete. I find them very useful. In this one the section "How to create a custom Excel date format for another locale" seems to omit a couple of details that interest me. My MSOffice language is UK English and I have a workbook where I want to optionally be able to display everything in German. For cells containing dates I find that, as you say, a format like "[$-407]d mmm yy" displays dates in German. But when dates are displayed in German it is logical to expect to be able to enter them in German too, but I am finding with (Excel 2010, Win10) that Excel does not apply the locale code in the format to input. For example, if I enter "3 okt" Excel does not recognise that as a valid date: it accepts only "3 oct". Is there any way to get Excel to parse input in German, or any other locale than the current MSOffice language? (If conditional formatting can do it, that would be a solution. I'm also competent in VBA.)
    A second point you didn't mention is that when my workbook is opened on a French computer the cell format I just mentioned now needs to be "[$-407]j mmm aa". Should Excel notice when a workbook developed on an English computer is being opened on a French one, and convert cell formats automatically? Or should I expect to have to do this myself in a VBA workbook-open event or suchlike?

    1. Hi! If you do not use the standard date formats, but use custom formatting and specify a country code, Excel will not change the code on its own. Also, the date entry format depends only on your computer's local settings.

  43. Hello,
    I downloaded an ebay report and want to convert this date/time format: Sep-26-20 20:29:03 PDT to: 09/26/2020
    I've eliminated the trailing characters with the =left function and that left me with Sep-26-20 but I cannot get that to convert to 9/26/20. I tried changing the format but that did not correct the format. I would appreciate any tips of help to move this forward. Thanks!

    1. Hi!
      Extract the required text strings with the MID and LEFT function. Create a date using the DATE function.
      I believe the following formula will help you solve your task:

      =DATE(20&MID(A1,8,2),MONTH(DATEVALUE(1&LEFT(A1,3))),MID(A1,5,2))

      1. Absolutely brilliant. Thank you!

  44. Thank You

  45. Hi. I want to change my this date format 31/10/2022 12:49 AM to 2022-10-31T00:49:00 this format in excel how can I do it? Help me find this out.

    Thanks in advance.

    1. I want to change my date from 3/27/2023 to 03/23/2023 the date is on left hand side instead of right hand side.

      Have read the article more than twice and tried all the suggestions but non work for what I want.

      Please kindly help.

    2. Hi!
      All the information you need for your task is in the article above. Here is the date format for you:

      yyyy-mm-ddThh:mm:ss

      1. But what I am trying to ask is this is the format in my first column 31/10/2022 12:49 AM (Assume column as A2).

        I want the next column to be linked with before column(which is A2) as get output as 2022-10-31T00:49:00.

          1. I'll see but can u share formula for the present one which I mentioned above?

  46. how to change number format to Date format Ex: 20230207 to07/03/2023

  47. Hi,
    I changed the date locale to German so the date I enter displays in the German format d/m/y. However, I still have to enter the date in the US way m/d/y.
    How can I change the cell so that I can enter the date in the German way?

    1. Hi!
      Follow the instructions in this paragraph above: How to change the default date and time formats in Excel.

  48. Hi Alex, I have this problem:
    A list with some dates in this format mm/dd/yyyy, others like this mm/yyyy, and others only like this yyyy. The first two excel can handle fine, but the yyyy can't, for example, I have 2022 and excel transform to 7/14/1905, but I neet to transform into 01/01/yyyy if no, I can't sort by oldest or newest.
    Do you know any solution to this?

    1. I have thousands of date data, some are aligned to right hand side as date, while others aren't and are at left side.

      The one on the left are in m/dd/yyy as against mm/dd/yyy.

      Have formated the cells, value which gives me #VALUE! , date and date value as #Value!.

      Please kindly help on how to make all my date mm/dd/yyy and to be on right hand side.

      Thanks.

      I

  49. I want to change date format from 04/26/2018 to 26/04/2018. I tried using all custom options but there is no change, please suggest some formula for the same

  50. how do I change a date format from 04.02.2023 to 04/02/2023 in a sales converter in excel?

    1. Hi!
      I kindly ask you to have a closer look at the article above. Have you tried the ways described in this blog post?

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