Date Format formula

[Hamster]

Hi, I have two import files that have different date styles (see below). I'm after a formula that will change these date styles to one date format (dd-mm-yyyy).

10/Dec/2020
2021-03-31

44217

Cheers

Graham

[AkimasaKajitani · Accepted solution]

Hi @Hamster 

"10/Dec/2020" AND "2021-03-31" are able to be Date Type by DateTimeParse function.

But 44217 is Serial Value of Excel Date, so you have to calculating it.

IF Contains([Field1],"/") THEN DateTimeParse([Field1],"%d/%B/%Y")
ELSEIF Contains([Field1],"-") THEN DateTimeParse([Field1],"%Y-%m-%d")
ELSE
DateTimeAdd("1899-12-30",ToNumber([Field1]),"days")
ENDIF

Result:

In this case, I have created an equation that assumes there are three different dates in a single data stream.

[DavidP]

Hi @Hamster 

Here's a formula that will convert the dates to your format

[Hamster]

Thanks very much, worked like a dream.