SharePoint List Input and Output and Dates

Hello,
I am extremely frustrated with Alteryx and SharePoint List Dates Column. Relates to UTC and local date/time.

Tried following these advice published by Alteryx. However, did not work.
https://help.alteryx.com/current/designer/sharepoint-list-input-tool

When writing to SharePoint, convert your system's timezone to UTC with the DateTimeToUTC() function.
When reading from SharePoint, convert from UTC to your system's timezone with the DateTimeToLocal() function

I am in Eastern Time or UTC -5.

If I follow instructions, dates are in written correctly in SharePoint. Tried export data to a spreadsheet and just uploading to list. Dates were off by a day. (minus 1).
Used this formula: DateTimeAdd([Dates],+1,'days') to correct the date issue. Now dates read properly in SharePoint.

However, now have an issue when I read the data from Alterx the dates are Plus 1 day now.

Catch 22: Either the date appears wrong in SP or right in Alteryx or right in SharePoint and wrong in Alteryx.

Thus, had to insert DateTimeToLocal([Dates]) to correct the date upon reading the data.

Anyone have any advice on this issue?

Wish SP and Alteryx would play well together.

Date Time

Help

Sharepoint

Accepted answers

kgordish1

Identified a resolution.

Must write DateTime when uploading to SharePoint not Date.
1. In Select Tool converted Date to DateTime
2. In Formula Tool used the ToDateTime() sets time to midnight.
3. In Formula Tool used DateTimeToUTC(). This must be downstream of ToDateTime()

Basically avoid Date and use DateTime when writing to SharePoint. This finally resolved my data issue.

All comments

kgordish1

Identified a resolution.

sumeetD

This works but cannot account for EDT vs EST differences which results in some dates being off.

ericlaug

I applied a kluge - not something I'm happy to do.

My SharePoint site is in Eastern so I added five hours (the max difference when in Daylight Saving time) to get it to GMT and then wrote that into the SharePoint list. It displays okay and when I download the data later, seems to work okay too.

In this example, Report_Month is the first.

DateTimeAdd([Report_Month],5,"hours")

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335