SharePoint UTC date conversion over multiple time zones

[spbarnes]

I am using Alteryx to read dates from a SharePoint Online list and write the same dates back to the list. These dates were entered by users via the SharePoint date picker. As soon as the Alteryx workflow runs, the updated dates are off by 1 day. This is expected, as the SharePoint Online server stores all DateTime values as UTC.

Alteryx documentation for the SharePoint list output tool states that:

• 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.

However, when I apply the above, I get unexpected results:

• Original date entered by user into SharePoint: 01-Feb-2021
• After Alteryx workflow reads this date and writes it back to the list, this date shows as 31-Jan2021
• After applying DateTimeToUTC() to 01-Feb-2021, the date visible in the SharePoint list is 30-Jan2021
• After applying DateTimeToLocal() to 01-Feb-2021, the date visible in the SharePoint list is 31-Jan2021 (I expected this formula to result in 01-Feb-2021 as it would be converting UTC to local).

What formulas can I apply in Alteryx to ensure the date originally entered in the SharePoint list is the date that is visible in the list after one or more data refreshes from Alteryx? I am in UTC−08:00 but my users will be worldwide, including Asia and the UK.

One resonse by @danrh suggested a formula: DateTimeToUTC(DateTimeAdd([YourDate],1,'hour'))

Danrh said: If your data crosses multiple timezones, you'll likely need to use some sort of IF statement to give an integer to each time zone as it varies from UTC, and then use that in the above statement.

What would an if statement look like?  How would the if statement detect the timezone of the user and add or subtract the appropriate number of hours?

How would this if statement work with SharePoint server?  Would SharePoint apply a new UTC date based on the converted value from Alteryx, resulting in mind-numbing circular logic that would constantly change the date towards one direction?  Or would SharePoint play nicely?

Any help would be greatly appreciated!

Paul

[jamielaird]

Hi @spbarnes ,

I might be missing something obvious here, but if the data you're reading from Sharepoint comes in as UTC, and you want to output as UTC, why not just keep the time in UTC within your Alteryx workflow?

This might mean making sure that any datetime calculations you add don't introduce anything in your local timezone, which you can do using the DateTimeToUTC command.

It sounds like you've tried a few things here so maybe I'm missing something!

Jamie

[spbarnes]

Thank you @jamielaird for challenging my thought process.

My first goal is to ensure the end user, who entered the date in SP list, can return to the list at a later date and see the same date.  After the Alteryx workflow runs, the date in the SP list is 1 day earlier (see images below).  I'm not certain that adding 1 day will be an accurate solution, as my end users are worldwide, so their offsets from UTC are different depending on their location.  Or maybe I am thinking about this incorrectly. Perhaps the only offset that matters is the time difference between UTC and the Alteryx server I am using.  Not sure on this one.

My second goal is to use the dates entered by end users in the SP list in a Tableau dashboard. The problem here is that while the SharePoint list is showing a date that is earlier by 1 day, Alteryx is still reading and displaying the correct date!  (See #4 below as an example of this issue.)  How can I correct a date that shows correctly in Alteryx but incorrectly in the SP List? Won't any correction result in the Alteryx value be off by one day (i.e., a day later than the SP List value). This is the mind numbing paradox I was referring to in my first post.  Dates need to output to a .hyper file with the same value entered by the end user into the SP List.

Here are some images to help clarify:

1. Data entry date in SP = 01Feb21

2. Confirming date value in SP List:

and confirming the value in Alteryx by reading from SP List Input tool:

3. However, when I run read/write Alteryx workflow, the date in SP List changes from 01Feb21 to 31Jan21:

4. BUT, this date shows in Alteryx as 01Feb21, even though the corresponding value in SP is 31Jan21. I am most confused about this part. Why do the dates in SP and Alteryx differ? How can I correct one and not impact the other?

One final topic that has me perplexed as well: Why are UTC dates showing in the SP List as minus 1 day. I am in UTC-8, which means that UTC is currently ahead of me. Shouldn't UTC dates in SP show as being at most, one day ahead, and not one day behind? 

Thanks,

Paul

[jamielaird]

Making some assumptions about how both SharePoint and Alteryx are acting, the following sequence of events makes what you're seeing possible:

Top row represents the date you would see in each environment, and the bottom row is my assumption about how this is being stored/interpreted as a datetime.

Despite my full-time job involving me working across two time zones I found this one a bit of a brain twister so there might be an obvious flaw in the above!

[spbarnes]

Thanks @jamielaird for daring to imagine the unimaginable.  I, too, for a moment thought Alteryx might be converting the dates but this is not documented in the SharePoint List Input & Output Tool pages.  In fact these pages state the opposite: "When reading from SharePoint, convert from UTC to your system's timezone with the DateTimeToLocal() function." From these instructions it appears that I would need to apply the formula myself to make the conversion, and not rely on Alteryx to do this for me.

In your first two step you propose that "user enters date which is stored in SharePoint as UTC+0", and "Alteryx reads date and displays as UTC+0". Assuming the date I enter (01Feb21) is interpreted by SharePoint as a date relevant to me (i.e., a local date), then wouldn't a UTC+0 of this date be different than 2021-02-01 00:00:00? I'm confused about this.

I'll be chatting with an Alteryx tech representative from my organization tomorrow, so will circle back with any new info.

In the meantime, I agree with your image name--true UTC madness.

Regards,

Paul

[jamielaird]

Hope you manage to get this resolved @spbarnes! When you do, let us know via this thread so future generations can benefit...

Quick additional suggestion.... have you tried changing your system time zone to UTC+0 and running a few tests, just to see if you get the same behaviour?

[spbarnes · Accepted solution]

Hi @jamielaird I think I figured it out.  Your work was not in vain...the scheme you pulled together showing a both dates and times got me thinking. It's amazing what a night of sleep will do.

My mistake was to chose "Date Only" as a setting for the date column in my SharePoint list. 

I was also looking at the date only in Alteryx. I should have converted the field type to Date Time so I can view the times coming in from SharePoint. 

By choosing Date Only, both systems assume the time is 12:00:00 AM for each date value as it goes back and forth between SharePoint and Alteryx.  Any conversions between UTC and local time are "lost".

So, for example, with the time hidden in SharePoint:

1. User enters 01-Feb-21 into SharePoint.

2. Alteryx reads in the date and shows this as 01-Feb-21 00:00:00.

This is where the first error is introduced. If time were included together with the date coming from SharePoint, Alteryx would have read and displayed UTC time of 01-Feb-21 08:00:00 as I am in UTC-8.

3. Alteryx sends 01-Feb-21 00:00:00 back to SharePoint. As I am writing directly to the SharePoint server, SharePoint assumes this time is UTC+0.

4. Because I am in UTC-8, SharePoint (or my browser?) converts 01-Feb-21 00:00:00 to my time zone (UTC-8). This conversion introduces the second error, and changes the date from 01-Feb-21 to 31-Jan-21.

By simply showing the time in both SharePoint and Alteryx, the time values are included in the calculations and everything works out.

I need to work with the above solution and see if it pans out, but for now, it's looking good.

Regards,

Paul

[jamielaird]

Excellent! So I was on the right track with thinking it was to do with DateTime. I think you can mark your own response here as the Solution 😀

[KenParsons]

Hi,

I recently needed to convert from UTC over to local time captured by the user.

The following function worked for me let me know if it is useful in your use case.

datetimeadd([date],datetimediff([date],DateTimeToLocal([date]),'hours'),'hours')

Note:

My data is always captured in Eastern Daylight Time and then converted to UTC.

Using the flat +5 hour rule wasn't not working for me.

The function above also takes into account the "spring forward" effect for those months.