utcNow in Power Automate is getting day after instead of today's date

Viewed 39

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

  1. Run a recurring trigger for (Monday-Friday at 0600, 1000, 1200)
    1. 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.
  2. Get my user profile - retrieve only ID and Mail
  3. Create two variables: Start time UTC and End time UTC
    1. Using these functions respectively: formatDateTime(utcNow(), 'yyyy-MM-dd"T"22:00:00.0000000Z') and formatDateTime( addDays(outputs('Start_time_UTC'), 1), 'yyyy-MM-dd"T"08:00:00.0000000Z')
  4. Get all the Shifts in my Team using List all Shifts and limiting the results to the above Start and End UTC times
  5. Filter the Shifts array to only ones matching the user profile ID

Now this is where it gets tricky:

  1. If the Shift for the user ID display name equals Home then:
    1. Get all the calendar entries for the day, with the filtering of: StartsWith(subject, 'WFH') and the Start / End UTC time from step 3
      1. If there already is a calendar entry --> do nothing (we dont want to double or triple up on the same entry)
      2. If there is no calendar entry --> create one with the subject WFH - ${fn_DATE}
  2. If the Shift for the user ID display name does not equal Home then:
    1. Get all the calendar entries for the day, with the filtering of: StartsWith(subject, 'WFH') and the Start / End UTC time from step 3
      1. Loop over the results and delete them from the calendar

Image of flow

I have manual variables in this image since I want to test without waiting for the Recurrence time to trigger.

workflow image

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.

0 Answers
Related