1/1/1990 Date Issue

[emenendez2]

I am running my WF with an excel document containing the date 1/1/1990 but after running the WF, the dates are automatically switching to 12/31/1899.

Is there a way I can fix the WF so that it keeps the correct date of 1/1/1990?

[TonyA]

Can you upload a workflow that shows the issue?

[emenendez2]

I attached an excel file with what the dates should be and then as soon as I input the file into Alteryx the dates flip from 1/1/1990 to 12/31/1899.

[Qiu]

@emenendez2 
I think you may have found a bug.

[emenendez2]

Any solution or workaround for the issue?

[Luke_C]

Hi @emenendez2 

Assuming your title meant 1900, not 1990 since that's what's in your data. This is actually an interesting one. Alteryx seems to be removing 1 day for days in 1900 up until march 1st. This is because microsoft excel incorrectly considers 1900 a leap year, when in actuality it was not (years divisible by 100 and not 400 are not leap years). Here's another post on the topic:https://community.alteryx.com/t5/Alteryx-Designer-Discussions/What-date-ranges-are-allowed-issues-re...

I guess the workaround here would be to do an if statement to check if the date is before 3/1/1900 and add a day using the datetimeadd function

[Qiu]

@Luke_C 
Nice shout 😁

[emenendez2]

I am thinking I should use the Formula tool for the IF statement. Do you know what the formula should look like to replace the 12/31/1899 with 1/1/1900?

[Luke_C · Accepted solution]

Hi @emenendez2 

That's correct. The below should work ([Date] being replaced with whatever your field is). This will update the one day in question. You could also do something like Date < '1900-03-01' if you have dates in Jan/Feb of 1900. I want to reiterate that this should be reviewed though as excel does not handle these dates correctly.

More info on datetime functions: DateTime Functions | Alteryx Help

IF [Date] = '1899-12-31'
THEN DateTimeAdd([Date],1,'day')
else [Date]
endif

[emenendez2]

Thanks this worked perfectly!