cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Building Planner Tasks from SharePoint Item Dates Formatting Issue

I am trying to create a planner task when an item is created in a SharePoint List. 

 

I am using a date filed in the SharePoint item as the start date for the Planner task, and adding 1 day to the task as the due date.  When looking at the outputs from the succesful run, the dates look correct.  In this case the SharePoint date field is 11/27/2017 - and correctly shows.  It creates a task back dated to 11/26 however.

 

I tried offsetting from UTC for my timezone (-5 in my instance using @addhours) but that just throws the start date to 11/25.

 

Date In.PNGFrom Flow - what appears to the correct dateOut 1.PNGWhat is created in Planner, back dated to 11/26

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Building Planner Tasks from SharePoint Item Dates Formatting Issue

Hi @WiggityZwiggity,

 

Since you are not in a position to change the date field to Date and Time, you can try the expressions outlined below, which I tried on my tenant and worked just fine. You will see that I am adding 5 hours since I'm on Eastern Standard Time and Flow reads date and time in UTC; you will have to adjust the number of hours based on your time zone and the field name. The name of my fields are StartDateTime and EndDateTime. Please let us know if that works for you.

Start Date

addHours(formatDateTime(triggerBody()?['StartDateTime'],'yyyy-MM-ddTHH:mm:ssZ'),5)

End Date

addHours(formatDateTime(triggerBody()?['EndDateTime'],'yyyy-MM-ddTHH:mm:ssZ'),5)




Did I answer your question? Mark my post as a solution!

Proud to be a Flownaut!


11 REPLIES 11
Super User
Super User

Re: Building Planner Tasks from SharePoint Item Dates Formatting Issue

 Hi @WiggityZwiggity,

 

I tried the same thing and I got the same results. The funny thing is when I look at the Planner task, it shows the correct due date on the dashboard, but once I open the task, it shows one day less in both Start and End date. The first screenshot is the correct due date and the second screenshot is the wrong one.

DueDate.jpg

 

DueDate-1.jpg





Did I answer your question? Mark my post as a solution!

Proud to be a Flownaut!


Re: Building Planner Tasks from SharePoint Item Dates Formatting Issue

That is very strange.  Mine shows incorrectly in both locations.  I assume we could format for that difference by adding 24 hours to everything?  Smiley Happy  Wish it worked out of the box though.

Super User
Super User

Re: Building Planner Tasks from SharePoint Item Dates Formatting Issue

Hi @WiggityZwiggity,

 

Although we can format the date and add 24 hours to it, this shouldn't be complicated at all. I know all the dates and times in Flow are UTC, I don't understand why the date is off by a day. I tried using the Convert time zone action in the Date Time connector to set it from UTC to Standard Eastern Time, and it's still off by a day. See the screenshots below along with their description to see the steps I'm using in my Flow.

  1. Convert Time Zone - Start Date
    SPPlannerTask-1.jpg
  2. Convert Time Zone - End Date
    SPPlannerTask-2.jpg
  3. Planner Task Outputs
    SPPlannerTask-3.jpg
  4. Actual Dates in Planner Task
    SPPlannerTask-4.jpg

I will post this issue on the Bugs forum so the Flow team can pick it up.

 

Regards,

Fausto Capellan, Jr





Did I answer your question? Mark my post as a solution!

Proud to be a Flownaut!


Community Support Team
Community Support Team

Re: Building Planner Tasks from SharePoint Item Dates Formatting Issue

Hi @WiggityZwiggity,

 

Could you please show a bit more about Start Date Time and Due Date time of OUTPUTS part that the “Create a task” action returned?

I have made a test on my side and don’t have the issue that you mentioned. My flow’s configuration as below:2.JPG

 The flow works successfully as below:3.JPG

 

4.JPG

 

5.JPG

 

Please check if there is something wrong with Start Date Time and Due Date Time of OUTPUTS part that the “Create a task” action returned.

If the Start Date Time and Due Date Time of OUTPUTS part that the “Create a task” action returned are correct, I think there is something issue with Planner connector.

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

Re: Building Planner Tasks from SharePoint Item Dates Formatting Issue

Hi @WiggityZwiggity and @faustocapellanj!

 

I played around with this today, since time zones are my nemesis in general, changing them all the time as I do :-)

 

I think maybe the key is this statement in yesterday's blog post by Stephen Siciliano (https://flow.microsoft.com/en-us/blog/working-with-dates-and-times/)... "Note that all times output by any action are in UTC."  So since "Create a Task" in Planner is an Action, and the Start Date Time is an Output of that action, it's in UTC. Then your Planner automatically converts and displays it as your own time zone. 

 

As a test, I tried using a Start time in my SharePoint list where the column was set to Date & Time format rather than only Date, and I entered a time of 9am (ie not the default of 00:00 which is set when it's Date only). In that case, without any extra steps in my Flow, my Planner task ended up on the correct day. This is because the Start Date Time output of the Create a Task action was the date I set (Dec 2), at 09:00 (UTC). So when Planner displayed it in my own time zone (Pacific, as it happens), the date was still Dec 2 (but presumably technically 8 hours earlier, which is my difference from UTC).

 

BUT, here's an odd thing: I also tested using my Date+Time SharePoint column but actually entering a time of 12am (i.e. 00:00), and it came out of the Planner Create Task action as 08:00 (because I'm 8 hours behind UTC here), and thus was still Dec 2 when Planner rendered it in my time zone. However, if I use a Date Only field from SharePoint, it comes out of the Planner Create Task action as 00:00, and thus gets converted by Planner to the day before in my time zone. I guess what's happening is that when you manually enter a time of 00:00, it's in your time zone, so the UTC equivalent of that (e.g. 08:00 in my case) is stored in SPO. But when you use Date Only, it's stored as 00:00 in SPO.

 

Not sure if that helps you, ultimately, since there isn't a way to manipulate the time AFTER the Create Task action. If your SharePoint field is Date Only, maybe you'll need to have the Flow add enough hours to account for your time difference from UTC...? Or if your field is Date+Time, you can still enter the date only, and leave the time to default to 12am, which should then work properly...

 

Hope this helps some, at least...

- Sandy



Did I answer your question? Mark my post as a solution!

Proud to be a Flownaut!


Super User
Super User

Re: Building Planner Tasks from SharePoint Item Dates Formatting Issue

Hi @WiggityZwiggity and @faustocapellanj!

 

I played around with this today, since time zones are my nemesis in general, changing them all the time as I do :-)

 

I think maybe the key is this statement in yesterday's blog post by Stephen Siciliano (https://flow.microsoft.com/en-us/blog/working-with-dates-and-times/)... "Note that all times output by any action are in UTC."  So since "Create a Task" in Planner is an Action, and the Start Date Time is an Output of that action, it's in UTC. Then your Planner automatically converts and displays it as your own time zone. 

 

As a test, I tried using a Start time in my SharePoint list where the column was set to Date & Time format rather than only Date, and I entered a time of 9am (ie not the default of 00:00 which is set when it's Date only). In that case, without any extra steps in my Flow, my Planner task ended up on the correct day. This is because the Start Date Time output of the Create a Task action was the date I set (Dec 2), at 09:00 (UTC). So when Planner displayed it in my own time zone (Pacific, as it happens), the date was still Dec 2 (but presumably technically 8 hours earlier, which is my difference from UTC).

 

BUT, here's an interesting thing: I also tested using my Date+Time SharePoint column but actually entering a time of 12am (i.e. 00:00), and it came out of the Planner Create Task action as 08:00 (because I'm 8 hours behind UTC here), and thus was still Dec 2 when Planner rendered it in my time zone. However, if I use a Date Only field from SharePoint, it comes out of the Planner Create Task action as 00:00, and thus gets converted by Planner to the day before in my time zone. I guess what's happening is that when you manually enter a time of 00:00, it's in your time zone, so the UTC equivalent of that (e.g. 08:00 in my case) is stored in SPO. But when you use Date Only, it's stored as 00:00 in SPO.

 

I think the reason your test worked, @v-xida-msft is because you used the Created field, which has an explicit time. Can you try it with a Date Only field?

 

Not sure if this all helps you, ultimately, @WiggityZwiggitysince there isn't a way to manipulate the time AFTER the Create Task action. If your SharePoint field is Date Only, maybe you'll need to have the Flow add enough hours to account for your time difference from UTC...? Or if your field is Date+Time, you can still enter the date only, and leave the time to default to 12am, which should then work properly...

 

Hope this helps some, at least...

- Sandy



Did I answer your question? Mark my post as a solution!

Proud to be a Flownaut!


Super User
Super User

Re: Building Planner Tasks from SharePoint Item Dates Formatting Issue

Hi @SandyU,

 

That is a great observation on having the SharePoint date/time field as Date and Time format and leave the time as default. I just tested that right now and it worked. My concern now is what if people don't want to have the time visible on the SPO list form to avoid confusion. What then? Not evevrybody will know this and will go crazy trying to find an answer. Not only that. If they also decide to leave the date/time field as Date Only, they will have to do some manipulation in Flow just like you mentioned. I will poke around more and see where this takes us.

 

Thanks for that excellent input. Your help is much appreciated.

 

Regards,

Fausto Cepllan, Jr





Did I answer your question? Mark my post as a solution!

Proud to be a Flownaut!


Re: Building Planner Tasks from SharePoint Item Dates Formatting Issue

Wow thanks for the dilligent answer there.  I assumed something like this was happening.  I do use a -5 (my EST UTC Offset) and still get the same results using a Date Only Field.  Unfortunatley I'm not in a position to change that date only field.  I use several other Flows that pull that date apply the offset and then use the date in some other fashion (whether that be an email, or another calculation) and don't have any issues.

 

It's only when it is transcribed to planner that I see this issue.  Perhaps by adding time I would correc this?  Will test and report back to the thread.

Super User
Super User

Re: Building Planner Tasks from SharePoint Item Dates Formatting Issue

Hi @WiggityZwiggity,

 

Since you are not in a position to change the date field to Date and Time, you can try the expressions outlined below, which I tried on my tenant and worked just fine. You will see that I am adding 5 hours since I'm on Eastern Standard Time and Flow reads date and time in UTC; you will have to adjust the number of hours based on your time zone and the field name. The name of my fields are StartDateTime and EndDateTime. Please let us know if that works for you.

Start Date

addHours(formatDateTime(triggerBody()?['StartDateTime'],'yyyy-MM-ddTHH:mm:ssZ'),5)

End Date

addHours(formatDateTime(triggerBody()?['EndDateTime'],'yyyy-MM-ddTHH:mm:ssZ'),5)




Did I answer your question? Mark my post as a solution!

Proud to be a Flownaut!


Re: Building Planner Tasks from SharePoint Item Dates Formatting Issue

Thanks @faustocapellanj that was the trick!

Super User
Super User

Re: Building Planner Tasks from SharePoint Item Dates Formatting Issue

You're welcome @WiggityZwiggity





Did I answer your question? Mark my post as a solution!

Proud to be a Flownaut!


Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 Release Wave 2 Plan

Power Platform 2019 Release Wave 2 Plan

Features releasing from October 2019 through March 2020

thirdimage

Flow Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Flow Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 335 members 4,852 guests
Please welcome our newest community members: