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

  1. HOw do i change/convert the existing date format 21.02.2017 into 21/02/2017 (in the same cell)?

  2. Thanks for the article and the numerous informations it provides.

    I will just correct one formula :
    =DATEVAL("1/1/2015")& TEMPSVAL("6:00 AM")
    It will not give the right date (january 1st, 2015) but january, 20th, 3050.

    The ampersand has to be changed to a plus sign :
    =DATEVAL("1/1/2015")+ TEMPSVAL("6:00 AM")

  3. HI

    I want to convert this 22/12/2016 03:22:39 PM to 22-Dec-2016 how do i do it in excel.

    Any advise is appreciated

    Thanks in advance.
    Yunus

  4. how to change a value for example 201001 to read as Jan-2010 in excel?

  5. Hello...I would like to change only date from 1/1/17 to 31/1/17....When we drag as per instruction..... it is displaying like this
    1/1/18
    1/1/19
    1/1/20....

    in my case year change instead of date....please give a solution....

  6. Hello...I would like to change only date from 1/1/17 to 31/1/17....When we drag as per instruction..... it is displaying like this
    1/1/18
    1/1/19
    1/1/20....

    in my case year change instead of date....lease give a solution....

  7. Hi I need the format of this data (6/25/2010 10:59:09 AM IST)
    which I downloaded as dd-mmm-yyyy. Please give a solution

  8. HI,, IF I ENTER 21/2015-16 I WANT TO CHANGE 21 WILL BE CHANGED LIKE 22,23,24 ONLY SERIAL NUM WE CHANGE HOW TO CHANGE THAT TYPE PLESE HELP...I NEED SHOW IN THE FORMAT LIKE 22/2015-16,23/2015-16

  9. If i enter a date in MM/DD/YYYY format it automatically change to DD/MM/YYYY format within the date of 1 to 12. if the date after 12, it shows correct format in MM/DD/YYYY format.

    My format Cells has the format as MM/DD/YYYY. I need all the date must show in the format of MM/DD/YYYY.

    Note: I dont want to change my regional language settings.

    Please tell how to fix

  10. How to ADD below with same format-

    1d,4h,30m,0s (which I get from [=INT(E3-D3)&"d,"&HOUR(E3-D3)&"h,"&MINUTE(E3-D3)&"m,"&SECOND(E3-D3)&"s"]
    2d,8h,30m,0s (which I get from [=INT(E3-D3)&"d,"&HOUR(E3-D3)&"h,"&MINUTE(E3-D3)&"m,"&SECOND(E3-D3)&"s"]

    Please help me...

  11. im trying to put conditional formatting for highlighting cells with the formula in Rule of Conditional formatting, =(TODAY()-$G$2)>7 ,where $G$2 is of date format " 1/18/2017 6:35:45 AM".
    But formula is not working, is that it is returning a data format something like this "1/1/1900 12:47:19 AM".

  12. Hello Svetlana

    I'm trying to format this: 20490521 into date (2049 year, May 21). When I format it as a long date format it shows ##################. and wrights me that when dates are negative or too long it is shown with ########.

    How can I solve this?

    Thank you in advance.

    1. Hello Beka,

      Excel displays hash signs when there's not enough room in the cell to show a long date, in this case you just need to make the cell wider. In your case, Excel may not recognize the number as a date, and you will have to convert it as explained in How to convert 8-digit number to date in Excel.

  13. I have two cells one is represent date (dd-mm-yyyy) and another is time (hrs:mm:ss) I have to bring both in one cell and in another cell I have to add 1.30hrs in time to show different, as time change if date changing is possible, please help in this regards

  14. Hi Svetlana!

    Can you help me convert the date from the following to the ending day of that week:
    16 Wk 28
    16 Wk 29
    16 Wk 30

    I am trying everything but I can't figure it out! Please help as that is the date that comes in from a Pivot table that can't be adjusted :(

    Thank you!

  15. i have a column of dates from a program i use, when trying to sort them they sort numerically by date, then alpha by month (so all the 1st are together, then the 2nds, etc....i'd like the actually sorted by date oldest/newest yet i always have to convert them manually (converting column doesn't change/fix anything as it seems to already know it's a date...but won't alter it by formula) is there another simpler way? (i am able to use other columns with the formulas to convert to julian then back to standard date, but this seems tedious and it really seems tedious with the macros i use....TIA.

  16. How to Convert 01.01.2016 to 01/01/2016 date format

  17. How do you get the following date to change on every Sunday automatically

  18. Hello Svetlana,

    I have dates in 2 columns and in 1 column when highlighted - I can see same value in both the cell and the formula bar.
    case-1
    cell - Mon 21-Nov-16 Formula Bar - Mon 21-Nov-16

    but in another cell
    case-2
    Cell - Mon 21-Nov-16 - Formula Bar - 21-11-2016

    can you please advice whats the difference in both and how to use them either as case 1 and case 2?

    Also, i am interested to know, as to why if the value is taken under case 1, when we add formula of Cell (with date say cell A2) =A2+2 doesn't give me Wed 23-11-2016

    Please help.

    Thanks,

    Manish

  19. I am trying to write "08 - 16" which denotes "8 to 16" range but it is automatically converting 08 - 16 into August 2016 date format which I dont want. Please help me on how to keep the format intact.

    1. Hi Saurabh,

      Type it with the preceding apostrophe like this: '08-16

  20. I need a date and time calculation. For example 11-1-2016 is current date previous date 23-03-1992 is equal to =23 years and 3 months and days 23
    its use for age calculation ,,.
    please help me .

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

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

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

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

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

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

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

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

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

    TIA

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Keep it up !!!

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

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

  49. Hi

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

    I tried text to columns but its not working

    Thanks

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

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