Answered

Power Automate - SharePoint List - Date /Time Issues

(0) Share

Report

Posted on by MikeLockwood

197

OK, first off I know many people have posted about date and time issues. I have looked through them and none of them seem to fix my issue.

I am pulling data from an API and writing it to a SharePoint List. Pretty straight forward. I am using a Try, Catch and Finally model to catch and write errors to a different list. In this flow, I want to get the start and end time of the flow to record the flow run. I have tried using a variable with the function convertFromUTC(uctnow(),'Pacific Standard Time), the Convert Time action, and the Convert Time Action plus Compose. Everything in Flow then is showing the correct time, i.e 4/2/2021 9:34:22 or 2021-04-02T09:35:08.xxxxx. Great. But when it writes to Sharepoint, if flips everything back to UTC (see screenshot). Then in the SharePoint list, the time is off by 7 hours (not 8 for Pacific Time). I just cant figure this out.

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (1)

Pstork1 69,127 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

First, check to see what location your SharePoint data center is in. SharePoint will default Time and Date to whatever the site collection is set to in Regional Settings. You can check this in Regional Settings in Site Settings for the Site Collection.

Second, since you are only seeing an hour difference between the recorded time and what you expect. It might just be the difference that comes in for Daylight savings time. The Convert time should take that into account, but it is something else to check.

Was this reply helpful? Yes No
MikeLockwood 197 on at

Like (0)

Report
Copy link

Link copied!

I have checked the regional settings for the site, which is set to Pacific time. Also we are talking a 7 hour difference, not 1 hour. In the screen shot above the start time is 9:34: 22 am but in SharePoint it is 2:34 AM. A 7 hour difference.

Was this reply helpful? Yes No
Verified answer

Pstork1 69,127 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

Based on the output of your flow you are inputting 9:34 am in time zone independent mode, but SharePoint is taking that to be UTC. (Note the Z at the end in the Output). That is expected because SharePoint stores dates and times in UTC but converts them for display to local timezone. Since you are inputting the time as timezone independent it is storing that time in UTC and then converting it for display to local time. Due to Daylight savings on 4/2/2021 Pacific time is 7 hours behind UTC. So 9:34 AM UTC is 2:34 AM Pacific (7 hours behind).

When saving Dates/Times from a flow its best to convert Dates/times to UTC because flow doesn't translate like SharePoint does when saving.

Was this reply helpful? Yes No
MikeLockwood 197 on at

Like (1)

Report
Copy link

Link copied!

OK. So SharePoint regardless of the regional settings is always storing Date/Time in UTC. Thanks.

Was this reply helpful? Yes No
Pstork1 69,127 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

Correct, it always stores it as UTC, but displays it in SharePoint according to the regional settings. The problem is Flow deals with the backend data source and not the front end where the automatic translation takes place.

Was this reply helpful? Yes No