Answered

Created event in utc time converted to Daylight savings

(0) Share

Report

Posted on by NWEASD

174

I have an automate that I am creating items on a list. I take the time from the original. Item and create a new one on a different day using that information. When the item is created during daylight savings time, it is not an issue. The issue comes after the cross over, all the items are an hour behind the time they are supposed to be.

To start the conversion, I change the time to UTC and then create the item using the new calculated date joined with the time to pass to the sharepoint list. But when I view the list item it is not the correct time. Any thoughts on this? Sorry if I am not explaining it correctly.

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (1)

wskinnermctc 6,519 Moderator on at

Like (0)

Report
Copy link

Link copied!
Date Time conversion can be tricky.

Can you show a screenshot of your flow

Show the source data

Show how the source data appears in the flow (like put it in a Compose without changes)

Show what the outputs look like in the flow

What kind of columns are being used for the source and destination? text/string? datetime? dateonly?

Also what timezone you are in.

The more you can show the better because I could basically recreate it and have a solution that matches yours.

Was this reply helpful? Yes No
NWEASD 174 on at

Like (0)

Report
Copy link

Link copied!
Sure thing.

Here is the source of the information
{ "Name": "", "Email": "", "Phone": "", "GroupName": "Girls Volleyball", "Title": "Girls Volleyball practice", "SpecificUse": "volleyball practice", "AccessTime": "5:00 PM", "StartTime": "5:00 PM", "EndTime": "7:30 PM", "Building": "", "Inside": [ { "Value": "Gym" } ], "Outside": [ { "Value": "None" } ], "Other": "", "Equipment": [ { "Value": "" } ], "Chairs": "0", "Tables": "0", "AddInfo": "", "AthleticAAApproval": true, "AthleticsDirApproval": true, "BuildingPrincipalApproval": true, "ApproveStatus": "In Progress", "AudTechApproval": true, "StartDate": "10/30/2023", "EndDate": "11/1/2023" }
Convert from EST - UTC
Creating a new date with same time:
Adding into sharepoint:

The columns in the sharepoint list is Date/Time

When you view the sharepoint item it is showing in EST however it is an hour behind because of the change in Daylight savings.

Was this reply helpful? Yes No
Verified answer

wskinnermctc 6,519 Moderator on at

Like (0)

Report
Copy link

Link copied!

I made an example flow that hopefully will give an example of what to do with dates and times. Fortunately, daylight savings time is next week, so my example will cross the time change.

I'm in U.S. Central Time zone so I had to keep it like that for the example to reflect appropriately. You will just need to change this to your local timezone to work for you.

I have a single SharePoint list that I will use for this example just to keep it simple. There is an item with a Start Time that is written in a single line text column in the format of 5:00 PM. There is a Start Date column that is a date time type column which only has the date value with the date as 11/2/2023. (The sharepoint list display format of a date value is MM/dd/yyyy but the time is stored in the system as ISO 8601 yyyy-MM-dd.)

There is a third column that is a date time type column which has both date and time. I will update the item with the combined Date and text time value.

SP List Item with a text time and a date columnSP List Settings to see the column types.

I have an example flow that is going to do two things.

Scope A - The flow is going to get an item that has a text time value and a date value and put them together into a SharePoint datetime type column. So this will be a single item that has a usable datetime value.

Scope B - This part of the flow is going to use the first item and add 6 days to it and create a new item (in the same list.)

Flow overview - Scope A will update the current item - Scope B will create a new item.

The first thing that needs to be done is to combine the text time with the date value. This needs to stay in the local timezone. Put the dynamic content of the StartDate with a space followed by the StartTime. Power Automate will be able to read this datetime time format.

A1 - This is just to get the date and time into the ISO 8601 format so that it can be used in other steps. It is not converting the time zone, it is using the same timezone as the source and destination. (Be sure to use the [s] sortable format string. DO NOT USE [u] universal.)

A2 - This will convert the local time into the UTC timezone in order to fill the date and time column for the current item. This will use the outputs of A1 as the source time. It will convert it from Local to UTC. (Be sure to use the [u] universal format string. If a time ends with Z it is in UTC timezone.)

This scope will update the item with a date and timeA1 uses the Convert Time Zone to correctly format the date and time - do not change the timezoneA2 uses the output of local time from A1 to convert to UTC timezone and then updates the item.

The second scope of the flow is an example of creating a new item. This will use the outputs of the A1 local timezone to add days. Once the days are added, then it will be converted to UTC in order to populate the date and time column in the SharePoint list.

B1 - Add to Time - this will add days to the output of A1 Local Formatted Date Time and will remain in the local timezone

B2 - This will convert the new time of the event from Local to UTC.

The individual time text column will use the output of the Add to Time action with the formatDateTime() function which just shows the hours. The individual date column will use the output of the Add to Time action with the formatDateTime() function which just has the date.

Scope B - creates a new event with time added to the original local time of the eventZoom Scope B - actions B1 will add to the original local time and B2 will convert to UTCCreate new item uses the outputs from B1 and B2 to populate the individual fields.

If I run the flow using my original item from the SharePoint list, I get these results.

Results Scope AZoom Results Scope AResults Scope BZoom Results Scope BInitial Item Before FlowUpdated Item and Created Item

I hope these examples help you with getting your flow put together. I added a lot of photos so it might be easier to see what is happening and if you are getting errors double check the details.

Let me know if this works for you

Was this reply helpful? Yes No
NWEASD 174 on at

Like (0)

Report
Copy link

Link copied!

Thank you. I was hoping to not have to re do a lot but I ended up just replacing the variables I did with time conversions. That is not a fast process with the automate. (32 loops with 3 conversion in each). I believe it is working but will check when we get closer to spring. I do not have a concern that it will not work properly now.

Was this reply helpful? Yes No