Serial Date Conversion anomaly

[Original_Yodies]

Ive searched community and have not found anything describing the conversion anomaly Im experiencing, or the background cause.

We all have seen serial dates from excel, dates represented as numbers.

I have a set of data where often dates are represented as serial numbers from the source excel, but often are represented in "yyyy-MM-dd hh:mm:ss" format.

Easy to use the DateTime Parse tool.

This is string data so I looked to convert those in the "yyyy-MM-dd hh:mm:ss" format first, then catch the others that do not convert, Serial Date, and convert them using something along the lines of a formula like...

        DateTimeAdd('1900-01-01',ToNumber([Name],1,1)-2,'days')

The anomaly is that certain groups of "numeric only" strings will convert with the DateTime Parse tool set on "yyyy-MM-dd hh:mm:ss" mode.  

I have found these and think it strange they convert to all Jan 1, yyyy with that tool configuration.

10-54 convert to 2010-2054

55-99 convert to 1955-1999

1400-9999 convert to 1400-9999

Any dev's know why?

Any designers know the best work around?

(workflow attached)

[atcodedog05]

Hi @Original_Yodies 

What exactly should the numbers be representing. Are you trying to convert number to date based on excel conversion. If you can use ToDate() or ToDateTime() function to convert number to date like below.

Workflow:

If you give a sample of your scenario, expected output and logic behind it i can help you out.

Hope this helps : )

[Original_Yodies]

Actually I found it because I had a few Serial numbers that were mistakenly recorded as year 1919 rather than 2019. thats how i found it.

Ive updated the workflow and it covers the three scenarios.

the first 2 are what id say "good to go"

the bottom, in the red outline Container is where i recognize the issue where the 6949, 6991, 6998 records, I feel should not covert because they convert to a year 6ooo years into the future, rather than converting to the year 1919 like they should.

Remember these should have been recorded at year 2019, not 1919, and if so would fall in place chronologically with the others via Month-Day.

I included a "Should be_Date" in the bottom container.

[jrgo · Accepted solution]

Hi @Original_Yodies 

If I understood you question correctly, you're date column from Excel contains a mix of proper formatted date values and serial values and you want to create logic that will only parse the values that are in the serial format. If so, the formula below should do what you're asking. 

IF REGEX_MATCH([date], '^\d{4}-\d{2}-\d{2}(?: \d{2}:\d{2}:\d{2})?$')
THEN [date]
ELSE DATETIMEADD(TODATE('1900-01-01'), TONUMBER([date])-1, 'days')
ENDIF

Uses REGEX to find the YYYY-MM-DD with or without the time parts. If the pattern is found, it keeps the value. If it's not found, it performs the DATETIMEADD() function. 

If you date field though has other date/time patterns though, this will break, but you can follow suite and expand the expressions based on other date formats found.

[Original_Yodies]

Really like this answer for the designer. Thanks

Am still interested why Alteryx will convert these strings into years on irregular basis, though.  

One question, Should formula be 

IF REGEX_MATCH([Name], '^\d{4}-\d{2}-\d{2}(?: \d{2}:\d{2}:\d{2})?$')
THEN [Name]
ELSE DATETIMEADD(TODATE('1900-01-01'), TONUMBER([Name])-2, 'days')
ENDIF

example, with yours, 44439 give tomorrow not today.

Something about Excel not recognizing year 1900 was not a leap year (or some other annoying b.s.)

Maybe...

IF REGEX_MATCH([Name], '^\d{4}-\d{2}-\d{2}(?: \d{2}:\d{2}:\d{2})?$')
THEN [Name]
ELSEIF TONUMBER([Name])<60 THEN DATETIMEADD(TODATE('1900-01-01'), TONUMBER([Name])-1, 'days')
ELSE DATETIMEADD(TODATE('1900-01-01'), TONUMBER([Name])-2, 'days')
ENDIF