Forum Discussion

Averheim's avatar
Averheim
Copper Contributor
Nov 03, 2020

Date formatting - mix of slash and dash dates

 


Since changing my laptop, all excel exports from our OM-system looks horrible in regards to the date format. The big issue is that all regions enter their dates differently in our OM system. I have no idea what my old Excel installation was set up to make it work, I simply can't get my head around how to format all dates at once without changing all slashes to dashes. Even if I would change the Slashes to Dashes I still have the problem of the order of the dates as "slash and dash-dates" has a different ordering. It would be nice if the formatting could be done in Excel and not by tempering with the date settings of my PC... An example file is attached. 

excel
office 365

2 Replies

Sort By
  • MindreVetande's avatar
    MindreVetande
    Iron Contributor
    Nov 10, 2020

    Averheim 

    (i'm using a non-English system . Guess what i mean if my translation guesses are totally wrong...)

     

    a formula solution might be:

    =DATEVALUE(B2)

    But that only works if your system (windows) is set to US-dates

     

    Another way that usually works: 

    Select column B

    Data->Text to Columns

    [Next]->[Next]

    Now you should be able to select DATE and the format MonthDayYear MDY  (in-data format. Not what you want).

    [End]

    If you are lucky the Text-dates (/) are transformed to real dates and the "real dates " are untouched.

    Chang the display-format If necessary (Ctrl+shift+3  Or right click. Format cells, Nubers format. Date)

     

    The problem:

    Some of your dates are "real" excel dates (-). And some are text (/)

    select column, Right click, Format cells, alignment, Horizontal=General

    Now you will se that the text is aligned to the Left and the "real" Dates to the right. 

    Or change display-format of the column t general (Ctrl+0). The text-dates will still be text but the real, calculable dates will show upp as days since 1900-01-01  (44161)

     

    You can use a formula that checks if its a date (number) or a text and only fix the text-Dates

    =IF(ISNUMBER(B2),B2,DATE(MID(B2,7,4),MID(B2,1,2),MID(B2,4,2)))

    But i think that should give the same result as method 1

     

    else. Use power query with "Locale": 

    https://support.microsoft.com/en-us/office/internationalization-power-query-d42b9390-1fff-413f-8120-d7df0ced20b9

    Expand: Use a non-default locale setting on a Change Type operation

     

Resources