US date to European Date and still use European dates

[bobbydave]

I have an excel sheet that I am loading.

Date(P, 'DD/MM/YYYY') as [First Detected]

It takes in the European date from Row P of an excel sheet.

However, within Row P, some dates are in US format MM/DD/YYYY

Is there a way of keeping all consistent in European format 'DD/MM/YYYY'

[bobbydave]

The previous excel source sheets are in European format.

And when I load this months data from an excel sheet, the majority of dates were missing and when I looked at the source, I saw the dates were all skewed so I'm assuming it was US/European dates.

I suppose I should really go back to the Source guys and get them to fix their source data but I wanted to have a 'catch' for events like this.

[sunny_talwar · Accepted solution]

I mean you can try something like this... but I am just concerned that doing this might incorrectly catch some dates as European and some as US... try this

Date(Floor(Alt(Num(P), Date#(P, 'MM/DD/YYYY hh:mm:ss'))), 'DD/MM/YYYY') as P_Date,

TimeStamp(Alt(Num(P), Date#(P, 'MM/DD/YYYY hh:mm:ss'))), 'DD/MM/YYYY hh:mm:ss') as P_TimeStamp,

[bobbydave]

So it was down to formatting of the Excel sheet on further inspection.

The dates on the left had General formatting.

The ones on the right were DD/MM/YYYY HH:MM:SS custom formatting.

However, it was coming out in US format.

So the issue lies with the source file so I had the originator look at their source.

Thanks everyone.