Forum Discussion
Inconsistent Date Format in excel file
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 ...
Hi Anonymous ,
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.
Hi Anonymous ,
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:
Hi Anonymous ,
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.