Alteryx writes 1/1/1900 to 1/2/1900 in excel output

[VJ_88]

I am facing a strange issue

• I have Date , String and Numbers columns
• if date column is empty I need to replace the Null with 1/1/900 in mm/dd/yyy format
• Alteryx browse tool shows correctly
• but when the same in written in excel 1/1/900 changes to 1/2/1900 
• to further investigate I made 02 fields (new) AD and CP difference AD output datatype in Alteryx is date whereas CP it is default V_String
• Changing datatypes is Necessary as Excel column should be in Date Format
• AD produces 1/2/1900 in excel output but browse output shows 1/1/1900
• CP produces 1/21/1900 in both  excel output and browse output

Kindly help

EXCEL has dummy input data in 1st sheet and screenshots in 2nd Sheet

[apathetichell]

This probably relates to excels handling of date systems and the 1904 issue -> https://support.microsoft.com/en-us/office/date-systems-in-excel-e7fe7167-48a9-4b96-bb53-5612a800b48....

A date is not a date -> it's an integer of days or seconds since a specific starting date. That is how it is stored.

My vague memory is they both have different start dates (12/31/1899 vs 01/01/1900) and perhaps the post 1904 adjustment gets these two to sync.

[VJ_88]

hi @apathetichell ,

I have a doubt if it's related to date system why all other dates are stored as is and only 1/1/1900 is stored as 1/2/1900 rest all are ok I mean 10/3/1991 is stored as 10/3/1991

[apathetichell]

YES - BECAUSE IT'S AFTER 1904.  Check if you are using the 1904 date system (you probably are) - toggle it to the 1900. rerun.

[SteveY02]

This is not a bug in Alteryx, but a known issue in Excel.

Excel incorrectly assumes that the year 1900 is a leap year (which it isn’t).

As a result, All dates from 1900-03-01 onward are shifted by +1 day. So 1900-01-01 becomes day 1, but Excel shows it as January 2, 1900 (1/2/00).

How to fix or avoid it:

Option 1: Replace 1900-01-01 with Null() or another default date in Alteryx.

Option 2: Convert the date to a string before exporting

[VJ_88]

Hi @SteveY02 ,

• cannot change to string it has to be in Date format as same is being used by other applications
• we are replacing Null with 1/1/1900 it's a requirement 

[binu_acs]

@VJ_88 

[apathetichell · Accepted solution]

@VJ_88my recommendation here -> go back to your original workflow. change where you set base values to  '1900-01-01' to '1899-12-31' -  understand why you made the change. recognize this is an excel limitation. mark this thread as a solution. close/go on to the next problem you have.

[VJ_88]

yes already did that but was trying to see if there a way