Forum Discussion

kgoldie's avatar
kgoldie
Copper Contributor
Jul 19, 2019

dates in excel wont format correct

i had a spreadsheet with dates formatted as yyyymmdd, I now need to change them to mm/dd/yyyy.  before starting this a user began keying all changes in the new format, so when you looked at the data some were yyyymmdd and some were mm/dd/yyyy, I went back and had the dates all set to yyyymmdd, but now if I go in and do a custom format for mm/dd/yyyy I get the ******************,,any thing I select either gives me the yyyymmdd or the ******************, I cant get any format to work now

I tried to break out the fields and conconcate them back but still cant get a format I need - I started looking for a function and still no luck - if I can get back to a good starting point i'm ok with that as long as the end result works - please advise thank you

Formulas and Functions
  • AB_BespokeExcel's avatar
    AB_BespokeExcel
    Copper Contributor
    Jul 19, 2019

    There are some funky logical problems with date number formats.

    Say for example your computers default date format is dd/mm/yyyy, you use the "Date" number formatting. Someone say for example an american opens with mm/dd/yyyy it'll be fine. The "Date" number format converts the serial number correctly. 

     

    If you use custom, say for example "dd-mm-yyyy", it will force that format. Dates like 12/12/2019 are super awkward with these "custom" formats. You must follow the custom formatting rule, it will not intelligently convert.

     

    Your date format "yyyymmdd" might also not auto apply the date serial.

    For example, type in 20190719 to a cell with that format it'll #### because it's assuming that number is the date serial, trying to convert that makes it too large. Even in a custom date format, you type in your normal date format (for example dd/mm/yyyy or mm/dd/yyyy).

    It's best practice to have an "Input" in these intelligent formats, and an output in your custom. If thats possible that it.

     

    If you've got data in that yyyymmdd you need to convert back, use "=Date" in a similar way.
    "=DATE(MID(A1,1,4),MID(A1,5,2),MID(A1,7,2))"

     

    kgoldie 

  • nabilmourad's avatar
    nabilmourad
    MVP
    Jul 19, 2019

    kgoldie 

    Hi

    This could be fixed in Power Query.

    1. Convert your data into a table (CTRL +T) >> Enter
    2. On the Data Tab >> Select From Table
    3. The Query Editor opens
    4. Select the Date Column having the Problem 
    5. Click on the ADD Column tab 
    6. To the right side >> Click on Date >> Select "Date"
    7. Now the Dates are fixed in the new Column
    8. Right Click the original Date column >> remove Column
    9. On the Home Tab >> Click Close and Apply

     

    Hope that helps

    Nabil Mourad

     

Resources