Answered

Working with datetime - different time in SharePoint and Planner

(0) Share

Report

Posted on by DavidDostal

My solution takes SharePoint Task list item and creates / updates Planner task item using this flow action:

As you can see, I take SharePoint list item date value as read by Power Automate in previous step and simply write it to Due Date Time field of Planner task item, with no formatting or any data operation on it.

Now, having this SharePoint task list item dated 30.04.2020 ...

… results into this Planner task

… and if we export both records to excel and compare datetimevalue, I get 7:07 or 6:06 difference, which leads me to some time-zone problem.

I have found that both, SharePoint and PowerApps are set to same timezone, can not find any setting or post how to handle it within a flow.

Many thanks for any advice or link to solved solution which I did not find.

David

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (1)

Sort by

ChristianAbata 8,953 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

hi @DavidDostal the principal reason is the time, see a new day starts in planner at 00:00:01 so what you need to do is add a time inside your day for example 04-30-2020T07:01:00Z

Was this reply helpful? Yes No
DavidDostal 50 on at

Like (0)

Report
Copy link

Link copied!

Thanks @ChristianAbata,
yes, probably time issue. The source datetime value in SharePoint contains time information (30.4.2020 17:00) the resulting Planner Representation of that is NEXT day with no time info. I am not sure if I understand you, what time should I add to what time. Might be I am not getting something, but the formula should look like PlannerDateStamp = SharePointDateTimeStamp - X:XX, if the solution is about time manipulation as Planner value is currently higher than SharePoint value?

Was this reply helpful? Yes No
v-litu-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @DavidDostal,

I guess you're in the UTC+7 time zone, right?

If so, it is caused by the when create the Planner task, the Due Date Time is considered as a UTC time:

For time adjust, the timestamp will add 7 hours base on (30.4.2020 17:00).

As a workaround, you could use the addHours() function to sub the Finish date 7 hours.

addHours('TimeStamp',-7)

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Was this reply helpful? Yes No
DavidDostal 50 on at

Like (0)

Report
Copy link

Link copied!

Many thanks, @v-litu-msft.

as seen here, there are two difference values 07:07 and 06:06 (hours:minutes)

Which motivates me to put together more general solution than adding some specific datetime value (my solution might be used in various timezones). I am going to try following solution:
1. Get SP-DateTimeOriginal value (in local timezone)
2. Set variable SP-DateTimeConverted (to Planner UTC)
3. calculate difference between SP-DateTimeOriginal and SP-DateTimeConverted
4. Write PL-DueDateTime = addHours(SP-DateTimeOriginal, result of 3)

I will let the community know.

Was this reply helpful? Yes No
Verified answer

DavidDostal 50 on at

Like (0)

Report
Copy link

Link copied!

Resolved. My previous plan did not work as Flow reads SharePoint's datetime local value already in UTC (e.g. Due Date in SharePoint 2020-07-17 is read as 2020-07-18 by flow.

Therefore I read timezone info using HTTP request to SharePoint, take Bias value, convert it to hours and using addHours expression as recommended by @v-litu-msft and @ChristianAbata.

It works:

Was this reply helpful? Yes No