Create Event - Time Zone / All day event

[Tamras]

I created an approval flow to start when a new item on SharePoint (online) is created -- the condition is in addition to sending email, i also want to create a calendar event. The flow works except it is using a different time zone on the calendar - create event. Additionally, I need to know how Flow recognizes if the SharePoint item is marked as All Day Event.

For example:
On SharePoint, the user selects:
Start Time: 03/01/2017 @ 9:00 AM
End Time: 03/01/2017 @ 12:00 PM

Here's the SharePoint list item:

Here's the flow:

...and the Calendar Event result

Any help is appreciated. This is my 1st day using Flow 🙂

[Tamras]

Found a solution to the time zone.  In the create event window, there are 2 fields to indicate the time zones - where I entered Pacific Standard Time -- seems to work but now I have another issue: All Day Event.  I need to figure out how Flow would recognize if SharePoint item is marked "all day event."

[v-micsh-msft]

Hi @Tamras,

What is the AlldayEvent Column type under your SharePoint list?

I just tried to create the flow, but it seems that the IsAlldayEvent field(under Create Event Action,  bottom of the Advanced options) won't accept the dynamic contents.

So there comes to another workaround, add a condition (Document reference ) to check the AlldayEvent Value from SharePoint list, then create the corresponding Event, see screenshots:

If you need any further help on this issue, please post back.

Regards

[Tamras]

Thanks for the reply.

I created a calculated field (All Day) to indicate if its an all day or not.  The return value is a single line of text (Yes or No). I actually attempted inserting a condition but with value Yes (without single quotations marks). I tried below and it still results in a "no" condition.  I created a simple flow to test the condition to just send me an email. For the Value -- is there any documentations when to enclose it with a single quotation marks? Thank you

[Tamras]

I got my test email above working but when I apply it to create event. it doesnt work again.

Here's what I did to get the email test above to work:

• Modified my calculated field on SharePoint to enclose the results in double quotation marks:
  =IF(NOT([End Time]=[Start Time]),IF(MOD(ROUNDUP(([End Time]-[Start Time])*24,0),24)=0,"TRUE","FALSE"),"FALSE")
• Then replaced the condition value to TRUE (no quotation marks).

Now when i applied the same to the create event, if its an all day event  the flow doesnt run the Condition 2 and Update Item. But it works on partial day events.

The flow should be:

• If new SharePoint list is created > Send approval email >
• If approved > 1) Send email to requestor approving the request, 2) create an event on requestor's Outlook calendar;
• If not approved > Send email to requestor not approving the request
• And finally update the SharePoint list.

Here's what my flow looks like:

The following are not shown in the above image:

CONDITION 2 > Create Event > Advanced Options.

IF YES:

Is All Day Event = Yes

End Time Zone = Pacific Standard Time

Start Time Zone = Pacific Standard Time

If NO:

End Time Zone = Pacific Standard Time

Start Time Zone = Pacific Standard Time

Any help is greatly appreciated. 

[v-micsh-msft]

Hi @Tamras,

I followed your steps and the flow works at my side:

Event is created successfully under office 365 calendar, with All day checked.

Could you please share the Details for SharePoint->Update item Action when it won't work?

In addition, please take a try to re-create this flow from stratch, see if this would help.

Regards

[Tamras]

testing it now, will post result soon.

[Tamras]

It is still not working. I started fresh using the exact same flow but it is still not working.  I did a work around by creating 2 additional calculated fields on SharePoint -- it worked on both partial day and All day events.  But, I really would like it to work without creating the 2 fields so I will keep trying.  

Not sure if it has something to do with Microsoft Flow free version?

I'd like to request my company to subscribe to MS Flow but I need to get this to work first (without extra workaround on SharePoint) for my justification. 🙂

Also, on your screenshot, how do you get those green circles on the upper right corner of each box? 

[v-micsh-msft]

OK, @Tamras, thanks for the update.

To check flow run status, follow:

Troubleshooting a flow

Regards

[Tamras]

Thank you.  I tried it and got this result -- 

"status": 400, "message": "Your request can't be completed. The start and end times for an all-day event need to be set to midnight.", "source": "127.0.0.1"

[Tamras · Accepted solution]

I fixed this issue. Now that I know what was wrong with it, I just inserted 3 compose actions and used them if the "all day event" condition is met.  I had to create 2 compose actions for the Start Time. It initially gave me an error message when i used the [StartTime] SharePoint column directly into the calendar event -- eventhough it was already midnight.  I think it has something to do with the Output -- it was missing the extra .000000Z

[Anonymous]

I'm attempting to use the same solution by typing in "Eastern Time Zone" but unfortunately it is not working.

Does anyone have a solution that will work?

[Anonymous]

you will need to type "Eastern Standard Time"

[Anonymous]

I was having a similar issue. I was using a field that was only date because I wanted all events to be all day. I was manually adding T00:00:00Z to the end but I would get the same error. Then I realized that it was converting that since I was specifying Eastern Standard Time. I ended up adding T04:00:00Z and verifying that the flow was functioning correctly.

Then I realized it was not necessary for me to add the time manually and that I would have to calculate Daylight Savings Time... Ugh. I removed the specification of the time zone and the manual addition of time. Now I get an even that is all day but is still labeled as UTC.

[Tamras]

I'm actually using another solution rather than what I posted initially.

Keep in mind that the time is being converted to UTC so change the -7 to whatever the difference between UTC and your time zone. PDT is -7, PST is -8  (wish there's an easy way to do this since once pacific daylight saving time is over, the formula will need to be modified).

1. I created two compose actions for the start and end time format since I'm also using these in the email body.
   
   Compose: Start Time Format
   Input: "@if(equals(triggerBody()?['fAllDayEvent'],bool(1)), formatdatetime(addhours(triggerBody()?['EventDate'],-7), 'MM/dd/yyyy'), formatdatetime(addhours(triggerBody()?['EventDate'],-7), 'MM/dd/yyyy hh:mm tt'))"
   
   Compose: End Time Format
   Input: "@if(equals(triggerBody()?['fAllDayEvent'],bool(1)), formatdatetime(addhours(triggerBody()?['EndDate'],-7), 'MM/dd/yyyy'), formatdatetime(addhours(triggerBody()?['EndDate'],-7), 'MM/dd/yyyy hh:mm tt'))"
   
   
2. Another 2 compose actions to convert the dates to midnight for Outlook Calendar.
   
   Compose: Start Date Midnight
   Input: "@startOfDay(outputs('Start_Time_Format'))"
   
   Compose: End Date Midnight
   Input: "@startOfDay(outputs('End_Time_Format'))"
   
   
3. Use the 2 midnight compose output for the Start and End Time in Outlook Calendar and I did not specify any Time Zone

[adinnouti_ci]

I was trying in all shapes and forms to have the daylight savings being automatic. I end up adding a new number column in the SharePoint list with the default value being the difference between UTC and my timezone, and using as a variable in your Compose formula, note that you have to cast as int(), the number field comes as a float.

[cbbrown]

@Tamras - Would you mind sharing the calculated column formula that you used to create the All Day value?

[Tamras]

@cbbrown Unfortunately, my Flow account (trial) expired and I get a blank page when I log on to Flow. But, my [all day] column was a boolean so if I remember correctly, I set a condition in Flow to assign the start and end time to midnight as indicated in earlier posts.