Covert invalid data from excel to numbers such as 01/02/4407

Question

Hello Community,

How to convert invalid excel dates to numbers in Alteryx?

Examples:

01/02/4407
03/12/4404
04/12/4404
14/11/4400
27/01/4407

I found posts on this topic but only works on valid data ranges such as 01-01-2018:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/How-to-convert-the-Excel-numerical-date-to-date-in-Alteryx/td-p/11184

You help is much appreciated.

Many thanks,

Lois

LoisQ · Accepted Answer

I think I have resolved this myself..

The post I was referring to had this formula DateTimeDiff([Date],'1900-01-01','days')+2

I tried to change the '1900-01-01' to '01/01/1990' because my date format is 01/02/4407 but it doesn't work.

I regex replaced '01/02/4407' to '4407-01-01' then it worked with the formula.

I'm wondering is there a formula to convert '01/02/4407' with one less step converting it to ''4407-01-01' first?

Lois