0

I have a SharePoint Online calendar (set to EST in the Regional settings), and an Intake Form that requests vacation, time-off, etc. When this is submitted, then subsequently approved, I have a Power Automate flow that creates that entry in the calendar. The entries are all off by 5 hours. For my flow when a new item is created, I have two actions to convert the Start and End dates, then it creates the item after the approval response.

For an example, I created an item requesting time off from 12/11-12/22, 8:00am (start date), 5:00pm (end date). When it gets populated in the calendar, it displays 3:00am, and 12:00pm - 5 hours earlier. I see the correct times in the Item List, Approval flow, and Email notification. It just registers the wrong time in the Calendar.

enter image description here

enter image description here

It seems like the correct times are registering everywhere, except the Calendar item/entry.enter image description here

Does anyone know what my issue is here? Again, the SP site collection's regional settings is EST.

Thank you.

1 Answer 1

1

This is by design, SharePoint stores dates in UTC format. The site regional settings allow to display the same date and time with the desired offset.

If you want to create an item for 9am UTC-5, SharePoint will store 2pm instead. But, for the user point of view, the date will be displayed as 9am.

For Power Automate, you have to use the DateTime connector to convert date (from UTC to UTC-5) if you want to display the SharePoint date in an email for example. You can also use expression like this: convertTimeZone('2018-01-01T80:00:00.0000000Z', 'UTC', 'Pacific Standard Time', 'D')

Be careful, when you want to filter items, you also have to use UTC date.

But, in a scenario where you want to create an event based on another item (and date/time column), just use the UTC date time without converting anything. I test this:

  • Create an item with UTC+1: time is 8 AM sharepoint item

  • SharePoint stores the time as 7 AM (UTC)

  • Create a Power Automate cloud flow to create an event with this time (7 AM UTC)

time utc

  • In SharePoint, the event is displayed as 8 AM.

sharepoint event

3
  • Thank you for the reply. I updated the Source Time Zone line in the Convert Time action to "(UTC) Coordinated Universal Time", and the Destination time zone to "(UTC-05:00) Eastern Time (US & Canada)", but still produced the same result. Could you please explain how else I could convert this? Where would I apply that expression you suggested? The date/time looks good until the calendar entry populates in the calendar (5 hours earlier)
    – Chris
    Dec 4, 2023 at 22:06
  • But you do not have to convert anything! I added screenshots in my answer.
    – jleture
    Dec 5, 2023 at 7:23
  • 1
    Thank you! Looks like I was making it harder than it had to be. If I don't use a Convert action, the times appear to match up in the calendar.
    – Chris
    Dec 5, 2023 at 14:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.