I'm building a Power Automate / Flow workflow which sets my calendar to booked if I am working remotely for that day.
The reason is that internal staff have access to my Teams status, but externals are not. So they do not know if they can schedule with me or if they are attending the office if I will be there. It's been working but it has been manual calendar entries until this point.
What I want to set up
- Run a recurring trigger for (Monday-Friday at 0600, 1000, 1200)
- This is so if my Teams Shift is set to Office at 6am, then it changes to Home before 10am then it will update, etc.
- Get my user profile - retrieve only
IDandMail - Create two variables:
Start time UTCandEnd time UTC- Using these functions respectively:
formatDateTime(utcNow(), 'yyyy-MM-dd"T"22:00:00.0000000Z')andformatDateTime( addDays(outputs('Start_time_UTC'), 1), 'yyyy-MM-dd"T"08:00:00.0000000Z')
- Using these functions respectively:
- Get all the Shifts in my Team using
List all Shiftsand limiting the results to the above Start and End UTC times - Filter the Shifts array to only ones matching the user profile ID
Now this is where it gets tricky:
- If the Shift for the user ID display name equals
Homethen:- Get all the calendar entries for the day, with the filtering of:
StartsWith(subject, 'WFH')and the Start / End UTC time from step 3- If there already is a calendar entry --> do nothing (we dont want to double or triple up on the same entry)
- If there is no calendar entry --> create one with the subject
WFH - ${fn_DATE}
- Get all the calendar entries for the day, with the filtering of:
- If the Shift for the user ID display name does not equal
Homethen:- Get all the calendar entries for the day, with the filtering of:
StartsWith(subject, 'WFH')and the Start / End UTC time from step 3- Loop over the results and delete them from the calendar
- Get all the calendar entries for the day, with the filtering of:
Image of flow
I have manual variables in this image since I want to test without waiting for the Recurrence time to trigger.
Issue
Because I live in Australia, and the time zone means we are +10:00 from UTC. When the Recurrence runs it seems to be pulling in different days (dd) which means it is adding calendar entries to the wrong day deepening on what trigger time runs.
Explanation table
| Tigger time - local | UTC date time | Start time UTC variable | End time UTC variable | Correct Date for use |
|---|---|---|---|---|
| 2022-09-16 01:00 | 2022-09-15 15:00 | 2022-09-15T22:00:00.000000Z | 2022-09-16T08:00:00.000000Z | Yes |
| 2022-09-16 10:00 | 2022-09-16 00:00 | 2022-09-16T22:00:00.000000Z | 2022-09-17T08:00:00.000000Z | No - see bold dates shows using the day after trigger |
| 2022-09-16 12:00 | 2022-09-16 02:00 | 2022-09-16T22:00:00.000000Z | 2022-09-17T08:00:00.000000Z | No - see bold dates shows using the day after trigger |
So because the UTC time at the 10:00 local time trigger has caused the UTC day to now flick over, the rest of the URC referenced times are a day ahead.
Question for Stack Overflow
How can I get the date to always be -1 day for Start and the Current day for End?
That way the running of the events and subsequent uses are always in line with the other filters.
