Inconsistent Date Format in excel file

[PBI_newuser]

Hi,

The excel file extracted from system has inconsistent date format.

If import to Power BI directly, Power BI will read those with "/" (eg. 8/31/2020) as MDY and those with "-" (eg. 08-11-20) as DMY.

Actually all should be read as MDY.

How can I solve this issue?

[PaulDBrown]

@PBI_newuser 

In Power Query, select the column, select "Replace values" in the ribbon and swap the "-" for a "/"

[v-yiruan-msft]

Hi @PBI_newuser ,

Assue that the date field looks like the ones in the below screenshot, you can create a calculated column to format the date field. Then set the proper date format for it.

---

Formatted date = IF (
    IFERROR ( FIND ( "-", 'Table'[Paperwork Complete Date] ), 0 ) > 0,
    DATE ( CONCATENATE ( "20", RIGHT ( 'Table'[Paperwork Complete Date], 2 ) ), MID ( 'Table'[Paperwork Complete Date], 4, 2 ), LEFT ( 'Table'[Paperwork Complete Date], 2 ) ),
    IF (
        IFERROR ( FIND ( "/", 'Table'[Paperwork Complete Date] ), 0 ) > 0,
        DATE ( RIGHT ( 'Table'[Paperwork Complete Date], 4 ), MID ( 'Table'[Paperwork Complete Date], 3, 2 ), LEFT (
            'Table'[Paperwork Complete Date],
            IFERROR ( FIND ( "/", 'Table'[Paperwork Complete Date] ), 0 ) - 1
        ) )
    )
)

Set the proper date formatBest Regards
Rena
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.

[PBI_newuser]

Hi @v-yiruan-msft ,

I follow your steps but I got the below error.
"Cannot convert value 'Ja' of type Text to type Integer.

After importing the excel file to Power BI, the data appeared to be as below.

Sample pbix:

https://wetransfer.com/downloads/fe73bebb842f94621d99ab533a13dcc220201022134225/ee50d29242a06bb91e5c...

[v-yiruan-msft · Accepted solution]

Hi @PBI_newuser ,

Please update the formula of calculated column [Formatted Date] as below:

Formatted Date = 
IF (
    IFERROR ( FIND ( "-", 'Sample'[Complete Date] ), 0 ) > 0,
    DATE ( CONCATENATE ( "20", RIGHT ( 'Sample'[Complete Date], 2 ) ), SWITCH (
        MID ( 'Sample'[Complete Date], 4, 3 ),
        "Jan", "1",
        "Feb", "2",
        "Mar", "3",
        "Apr", "4",
        "May", "5",
        "Jun", "6",
        "Jul", "7",
        "Aug", "8",
        "Sep", "9",
        "Oct", "10",
        "Nov", "11",
        "Dec", "12"
    ), LEFT ( 'Sample'[Complete Date], 2 ) ),
    DATEVALUE ( 'Sample'[Complete Date] )
)

Best Regards
Rena
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.

[Anonymous]

@PBI_newuser Similar scenerios have been handled here- 

https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-msoffice_custom-mso_2016/inconsist...

https://community.powerbi.com/t5/Power-Query/Inconsistent-date-format-in-source-data/m-p/1078746

Please check.

Appreciate your kudos!! Mark this post as solution if this helps.