Inconsistent Date Format in excel file

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.