Date Values Showing As '1900-01-09'

[aditi-dahal]

Hi all,

I am currently building a workflow and when I try importing an .xlsx spreadsheet, all 6 of my date columns show up as '1900-01-09' values. As a workaround, I changed the File Format of the Dynamic Input to .xls instead. Is there a way we can import the correct dates through a .xlsx input instead? With an .xls input, I do not have the option of specifying which row the data begins so I have to use the Sample and Dynamic Rename tools to set the correct row as the headers. I also have to get rid of the commas of my numeric amounts before changing the data type from String to Double, otherwise the numbers after the comma get cut off. We currently have a SFTP that lands our spreadsheets in the correct folders, so I am thinking the encryption is causing the issues. Any input would be appreciated.

 

  

Another question I had was: is there a way we can use one DateTime tool for multiple columns? I have to use 6 separate tools for my 6 date columns so I was wondering if there was a more efficient way of converting the string values to date. 

Thank you!

[wdavis]

Hi @aditi-dahal 

I have attached a workflow which hopefully can address the two issues.

The first around the date formatting when bringing in xlsx. I'm not 100% why it is reading as that particular date, but we can use a formula to first work out how many days away from the expected date it is. This value is then appended to all records for a second formula which will add the number of 'days' to each 'effective date' which is bought in.

With regards to the multiple DateTime tools, one option would be to see if the formatting you are doing in here can be replicated in the Formula Tool (https://help.alteryx.com/20221/designer/datetime-functions) and then you can use a multi-field formula to apply to 6 fields at the same time.

Hope this helps.

Kind regards,

Will

[aditi-dahal]

Thank you for the response, @wdavis 

My date values turn to NULL when I use the Multi-Field Formula option. Also, some of the dates I need cannot be derived through a formula (ex: when a reconciler performed the reconciliation process) since there are so many varying values.

[ArtApa]

Hi @aditi-dahal - A data sample will be helpful.