Dynamically check Excel sourced date field type to ensure it's NOT a Julian format.

Question

I have a file from a client that occasionally inputs a date field in Julian date format. Sometimes it inputs the date field as a date format, which is what we want. How do I dynamically check the incoming date data type, then translate it to a date/time format if it comes in as a Julian Date? Alteryx automatically reads a Julian date as "Double", which doesn't work with DateTimeDiff.

earth2joy · Answer

Thanks so much for your help, but that wasn't really the question, though it would work in a different scenario.

What ended up working was far simpler than I had anticipated:

The Julian date from the Excel file was being read by Alteryx as "Double". So all I had to do was use a formula tool that created a new column:

iif(IsNumber([Julian Dates]),ToDateTime([Julian Dates]),[Julian Dates])

clmc9601 · Answer

Hi @earth2joy,

I would use the formula tool to create a new column that will always contain the Alteryx-friendly date. You can use the following expression to test and see if the client's date column contains letters (this one tests for more than two letters) and if so, change it:

IF Regex_countmatches([client's date column], "[a-z]")>2

THEN Datetimeparse([client's date column], "%B %d, %Y") // or replace this string with however your Julian date is formatted

ELSE [client's date column]

ENDIF