Building Planner Tasks from SharePoint Item Dates Formatting Issue

[WiggityZwiggity]

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.

From Flow - what appears to the correct dateWhat is created in Planner, back dated to 11/26

[faustocapellanj]

 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.

[WiggityZwiggity]

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

[faustocapellanj]

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
   
2. Convert Time Zone - End Date
   
3. Planner Task Outputs
   
4. Actual Dates in Planner Task
   

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

Regards,

Fausto Capellan, Jr

[v-xida-msft]

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:

 The flow works successfully as below:

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

[SandyU]

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

[SandyU]

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

[#8]

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

[faustocapellanj]

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

[WiggityZwiggity]

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.

[faustocapellanj · Accepted solution]

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)

[WiggityZwiggity]

Thanks @faustocapellanj that was the trick!

[faustocapellanj]

You're welcome @WiggityZwiggity