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 23. Total comments: 940

  1. I need to show Century in my date format, but I'm not understanding how to do this. Example:

    Format is CYYMMDD
    C=Century
    Years 2000-2099 C=1
    Years 1900-1999 C=0

    If date is 10/15/2016 on excel spreadsheet must show as: 1161015

    If date is 12/31/2039 on spreadsheet must show as: 1391231

    1. =IF(LEN(TRIM(TEXT('01'!D7,"mm/dd/yyyy")))-FIND("/",TRIM(TEXT('01'!D7,"mm/dd/yyyy")),FIND("/",TRIM(TEXT('01'!D7,"mm/dd/yyyy")))+1)=2,"1"&MID(TRIM(TEXT('01'!D7,"mm/dd/yyyy")),FIND("/",TRIM(TEXT('01'!D7,"mm/dd/yyyy")),FIND("/",TRIM(TEXT('01'!D7,"mm/dd/yyyy")))+1)+1,2)&LEFT(TRIM(TEXT('01'!D7,"mm/dd/yyyy")),2)&MID(TRIM(TEXT('01'!D7,"mm/dd/yyyy")),FIND("/",TRIM(TEXT('01'!D7,"mm/dd/yyyy")))+1,2),TEXT(YEAR('01'!D7)-1900,"000")&TEXT(MONTH('01'!D7),"00")&TEXT(DAY('01'!D7),"00"))

      You can change the Column-Row (D7) as per your sheet where Date will be there in format mm/dd/yyyy

  2. how to change my default century year in excel...i cant read before 1900 year in poi

  3. I have Date data where it contain both type of date string
    26 08 2016
    25/8/16
    want to apply and convert it into one format.

  4. i want to conver the date formt "20160627T065908.000 GMT to dd/mm/yyy hh:mm:ss
    how can i achive this fuctionality ? please suggest asap

  5. Hi,
    I want to change the date format for my data from 10/11/2012 14.30.00 to 10/11/2012 14:30:00
    Thank you in advance

    1. Hi Marcellin,

      The easiest way is to use Excel's Find and Replace feature:

      - Select all cells with dates that you want to reformat.
      - Press Ctrl+H to open Find and Replace.
      - Type a dot (.) in the "Find what" box, and colon (:) in "Replace with".
      - Hit Replace All.

      Done :)

  6. How can I change the date format of "07/22/2018" into "2018-07-22"? I have tried right click, format but the slashes wont change into dashes. :/

    1. READER THE UPPER SECTION DEAR

    2. Hello Dan,

      Select the cell, and Press Ctrl+1 to open the Format Cells dialog. On the Number tab, select Custom from the Category list and type the following format code in the Type box: yyyy-mm-dd

      1. Hi, I have tried this in my CSV file and it does work, but once I save it and reopen the file, it has converted it back to MM/DD/YYYY. What can I do so that when I do the Custom and change to YYYY-MM-DD it keeps it once saved?

  7. i was calculating for days left between two dates but i wanted it to give only the number of days not the month it keeps giving me until number 31 and then goes back to counting from one .if it is left 65 days i wanted it to give me 65 days but it gives me 4 or 3 so any way to fix it in file format dialog box

  8. Hello,
    I am looking to convert 14/09/2016 09:47:06 and 2/9/2016 12:01:21 PM these two date format, listed in single column, to format "dd/mm/yyyy", in separate column.
    I am able to covert second format using functions like TRUC/TEXT etc. but getting error "#VALUE" wherever first format is listed in that column.

    Would appreciate prompt help.

    TIA
    Nitin Y.

  9. I need to format a date to read mmddyyyy with no slashes. Can you help me with this?

    TIA

  10. Hi
    In excel 2016 where we are using the pivot table from base data , how we can change the date formate. e.g. I have used date format 01-09-2016 in base sheet and while creating pivot , date format changed to 01- sep. As I am going to use pivot for H-lookup, its not finding the value. Can anyone suggest solution?

  11. HI i have a column containing dates in 2 formats i tried converting them to 1 format using Format cells Date option but it was not successful. Can you please provide me a solution for this.

  12. how to find the difference between Mar 3 2016 11:54:11 and Mar 2 2016 11:53:55 in hrs

  13. Excel date 01/04/2016, while converstion for upload a file to VAT,it changes as 16-/1-4, so error occured in in uploaind to VAT, Plz reply how to format date

    Regards
    Shrirang

  14. Hi,

    I have an issue getting the result in "if" function as a date. my formula is =IF(NOT(ISERROR(DATEVALUE(TEXT(A1,"YYYY-MM-DD")))),"YYYY-MM-DD","NULL"). when the result is true I want the formula to give me the result as the actual date as A1. Instead it is giving the result as "YYYY-MM-DD". Please let me know how to fix this issue.

    Thanks Mfathima

  15. hi to convert this from "26.08.2016 14:02:55" dd.mm.yyyy hh:mm:ss to this format "08/26/2016 14:02:55" mm/dd/yyyy hh:mm:ss in excel... thank you

  16. while copying excel cell into words file , date changes into consolidated /equivalent number, which does not make sense on ordinary reading , how to avoid this change of formatted date into its equivalent integre number

  17. I have format of date is 03/apr/2015 but i want 03/04/2015 so how can i do ? tell me

  18. I can't find any information on how to have a date range to be recognized as a date.

    For example: September 1-3, 2016

    When I sort multiple dates but there are some date ranges (i.e. September 1-3, 2016) those date ranges sort to the bottom and aren't recognized as an ACTUAL date. How do I get excel to recognize it as a date?

    I tried formatting the cell in multiple different ways including:

    mmmm d-d, yyyy
    mmmm d, yyyy

    But nothing worked. Can you please help? I just need to be able to sort my spreadsheet by date (oldest to newest) and can't if the date ranges aren't being recognized as a date.

    Thanks!

  19. I need 3 formulas in excel.

    1. one that does a negative countdown in months for a 3 year time-frame from a list of dates.

    2. one that does a negative countdown in days for a 3 year time-frame from a list of dates.

    3. one that calculates age in terms of years and months from a list of certain dates.

    I have a system using the =DATEDIF command but in Excel 2013, I'm getting errors for the current formulas I'm using. Any help would be greatly appreciated for a non-math, non-Excel wizard.

  20. Hi Svetlana,

    I have a huge data set ranging from 2013 to 2016 and want to sort my data based on months, so have all the Januaries, Februaries, Marches, etc. together regardless of year. Is there a simple way to sort the data such?

    Many thanks.

    1. Hi Zianca,

      Here's a simple solution that comes to mind:

      1. In an empty column next to your Dates column, enter the formula to extract a month, e.g. =MONTH(A2)
      2. Apply Excel's AutoFilter and sort by the Month column, from smallest to largest.
      3. Delete or hide the Month column.

      1. Thank you very much! That is very helpful!

  21. I HAVE A EXCEL DATA FILE CONTAINING DATE IN FOLLOWING FORMATS

    4/1/2016 i.e. date/month/year how can i change it back to 1-4-2016

    1. Hello Salman,

      Just use the following format code to create a custom date format: m-d-yyyy

  22. Hi Svetlana,

    How to convert text (specifically, July 5th 2016) into a date? I tried using Datevalue function etc. I am looking for an answer for any text date where in dates are mentioned in "st", "th" and "nd" format. (e.g. July 5th 2016
    , June 1st 2016 and May 2nd 2016)

    Thanks

  23. I have a problem when I enable the date autoformat. When I key in 1/20 or 1-20, the excel autocorrects to 20-01-2016 as per my autoformat setting.
    The problem happens when I key 20/1 or 20-, the date is not autocorrected as a date..

    Please help. Thanks

    1. Hi Quek,

      To prevent this from happening, preface the fraction with a zero and a space. For example, instead of 20/1, type 0 20/1. Or, type a space or apostrophe before 1-20, like '1-20. The apostrophe won't be visible in cells, only in the formula bar.

  24. I have fields with dates displaying as 20-JAN-2016, for example. The format in the cell is actually 1/20/2014. (The number format of the cell is Custom). I would like to use the formula =MID to create a column to display the month (JAN) but since the format is slashes instead of dashes, it does not work. The fixes I see on this page don't actually change the format in the cell, only the display to dashes. Can you help?

  25. 7/18 - 8/5 in one cell

  26. Cheers !!! It really helped me out !!!!

    Keep it up !!!

  27. Hi,

    How can I change the format of the following dates
    07/01/2016 to 1st of July 2016
    07/02/2016 to 2nd of July 2016
    07/03/2016 to 3rd of July 2016
    07/04/2016 to 4th of July 2016
    and so on? Greatly appreciate your help on this.

  28. How do I change the date format that displays as: 10/30/2013 12:00:00 AM (it is now all in one cell)? I know how to do text to columns using space and / to separate but it doesn't seem to work. Ultimately I want only the YEAR in one column.

  29. Hi

    How do i change 02/01/2015 00:00:00 to date only?

    I tried text to columns but its not working

    Thanks

  30. Very good article.
    It helped me a lot to understand the error messages for some formula which includes date formates.
    Also, I refer this site for anything in connection with excel.
    Please continue to do this good work.
    All the very best.
    And, thank you very much.

  31. Can you tell me how to put the days of the week in order when making a chart?

    Issue
    have a list in SP. Created chart with field (day: which tell what day of week the a date fell on) I made the chart (chart: what days do people stay late at work the most - 180days.

  32. Date is 01-05-2016 i.e. 1 may,2016 but excel sheet is considering it 05/01/2016 i.e 5 Jan, 2016, how can i get my date?

  33. Hello,

    How do i change date from 6/6/2006
    to
    sixth june two thousand six

  34. when i typed like 01012016 means January 1 2016 the result is 46701020?

  35. Hello,

    I am currently updating my company's Excel files. I wanted to add the day's date in a cell if a project is marked completed. ive' used the following formula: =IF(ISTEXT(U:U), NOW(), ("")). it worked but I am facing a problem. the projects that were marked completed had the date in the cells next to them but the dates keep changing everyday. can you please help me with telling me the right formula to add an unchanging date using IF function?

    Thank you,
    Maan

    Reply

  36. Hello,
    How would you convert this date?

    06/6-7/2016

  37. Good afternoon Svetlana

    Is it possible to convert 01/01/16-31/12/16 in Excel to display as
    1 Jan 16-31 Dec 16 , please?

    Kind regards,

    Mark

  38. HELLO...I have a problem in my excel sheet.

    I have an SCADA system which generate and excel file with some values.

    But problem is that my excel file not showing the date& time format in sequential order that is from old to new order. There is not proper sequence.
    How to fix this problem permanently.

  39. Go online find the cheap jerseys for football,
    basketball and baseball. And the wholesale hockey jerseys,too.

    More from :cheap nike authentic nfl jerseys

  40. Please help .

    I had few dates in mm/dd/yy format n the new date entered is I. Ddmmyy format how can I make into stander format

  41. Dear Svetlana,

    How to convert this data in to a general numbers?

    15.64
    16.jan
    15.27
    13.76
    13.74
    13.36
    15.jan
    nov.41
    okt.31
    jul.79
    15.sep
    jun.81

    Thank you

  42. Dear Svetlana,

    I have downloaded Adwords report in Exel file and in column Average Position where should be the general number, I got a date (for exemple jan.1 instead of 1,1. My problem is that I don't know how to convert date in to a general number. The option formatting doesn't work as I got some number like this 42370... Please help.

    Thank you. Damjan

  43. Dear Madam,
    I entered the value 23.5.2015 or 23.5.15 in a cell but how to change the value to date format(23-05-15) easily.

    1. Dear all,
      With reference to the above question, I found the answer from your link (Find and replace formula . to /).
      Thanks to Svetlana Cheusheva

  44. Please help me, I am going crazy. In the following list of dates you will see two formats of dates, I want all of them to be arranged mm/dd/yyyy. When I try to format it doesn't work for some reason, here is a sample list:
    03-10-16
    02-11-16
    03-11-16
    03-11-16
    03/14/2016
    03/14/2016
    03/14/2016
    01/14/2016

  45. Hi,I have some numbers like "2-4" when I copy them to excel they appear like "4-Feb". I want the numbers keep their own format. Could you tell me how to convert?

    1. Hi Mehran,

      Try typing the numbers with a preceding apostrophe like '2-4.

  46. Hi Svetlana,

    Seems like you are the guru of Excel so here goes my question.
    I tried to add 7 days to my date format from a cell but it still comes us as #VALUE! For example my date was in A1 i typed =A1+7. No fixes around. Changed the whole worksheet's format to date as well but still does not work. Hope you can help.

    Thanks

    1. Hi Sam,

      It's a very simple, and correct, formula. You can test it on a new empty sheet and make sure it works fine. So, most likely the problem is with the source date in A1. An immediate reason that comes to mind is a date formatted as text. If it's the case, please try to convert it to date as explained in this tutorial:
      https://www.ablebits.com/office-addins-blog/excel-convert-text-date/

  47. I have doubt,can any one help me how to alter the date which is included in exisiting formulae.

  48. Its not working please give other formula or something else.

  49. We need to change format
    From to
    04-02-2016 02-04-2016
    04-02-2016 02-04-2016
    04-04-2016
    04-04-2016
    04-05-2016
    04-05-2016
    04-05-2016
    04-05-2016

    Please help me for the same

    1. Hello Binod,

      Select all the cells with your dates, press Ctrl+1, select 'Custom' under Categories, and type the following format in the Type box:
      dd-mm-yyyy

      Assuming that in "02-04-2016", 02 is the day and 04 is the month.

  50. Hi,
    In my country we show the date as yyyy/mm/dd or dd/mm/yyyy. My region is set correctly, but when I input a date such as 1 May, I insert 1/5. The cell automatically changes to 2016-01-05 (which looks like 5 January to me. My settings all appear to be correct.

    Any suggestions?

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