Need help with date/time conversion of Parsed timestamp outputs into SS and then into SP list

[EMY9317]

I've created a flow designed to circumvent the annoying fact that you can't create recurring events in the modern SP calendar list view.  The flow takes a form response that contains the name, start date/time and # of recurrences and updates an excel spreadsheet that resides on the site with this info into the first row of the spreadsheet table. Then the spreadsheet formulas populate the following table rows depending on the number of recurrences noted.  The flow then filters the table array to just the rows in that table that contains data and parses those items. Then it creates a new SP list item for each of those separate rows.  Finally, it wipes out the data in the first row of the spreadsheet (subsequently clearing out the rows following it), making the spreadsheet ready for the next recurring event to be created.

The problem I'm having is this: In order to make the calendar events show the actual start time and duration in the View Events pane of the calendar view, I had to use the date/time column type in the calendar list.  The form, however, only has a date field, not date/time.  So I had to break this out into 3 separate form questions: Start time hours, Start time minutes and Start time AM/PM.  Same thing for the End time.  My flow initializes variables to combine the date and time form answers into date/timestamps that can be added to the spreadsheet.  But when it takes the spreadsheet rows and creates SP list items, the time becomes all messed up.  I tried formatting the date/time columns in the spreadsheet to be something that can be transposed to SP correctly, but that didn't work. 

I hate date/time format stuff.  It confuses me and I end up going round and round until I hit something that works.  I'm hoping someone here can help me.

I've attached my flow here. One thing to point out, each team within the department has their own SP site, with their own SP calendar and their own recurring events flow spreadsheet.  So the flow also has a switch. Depending on the team the manager selects in the form, one of 6 cases, the flow will update that team's calendar and spreadsheet.  I didn't show the switch or other cases here.  They are all identical, so I showed only one.  I'm sure there is a function I can use that I'm not familiar with to be able to get the correct time to show in the SP list, but I don't know what it is.  I tried to use a Convert to UTC expression within the Create Item step but the dynamic content for the parsed date/time outputs don't show up when I tried to use an expression.  All that is available from the parse is the Body.  I do believe I am lost....

[wskinnermctc]

Welcome back @EMY9317 

In the List Rows step did you go to the Advanced Options and DateTime Format dropdown and select ISO 8601?

Also can you stick 2 compose statements into the flow before your create items step and put the start and end times into it and show the output in a screenshot. I'm trying to see what the value looks like that it is trying to put into SharePoint. 

[EMY9317]

Yes, the ISO 8601 is set in Advanced Options of the List rows function.

This is the output from the Parse JSON function.  Is that what you mean?

And this is the Create Item step.

And this is what it looks like in the SP list.  It creates the correct # of events and steps the date out correctly (the End dates are wrong but I'll fix them as soon as I can figure out how to fix the times).

[wskinnermctc]

Ok I see. Is it only the End DateTimes that are incorrect?

Or both the Start DateTime and End DateTime are incorrect in SharePoint?

[EMY9317]

It's both.  The time entered into the form for Start time is 6:30 AM (CST) and the End time is 3:30 PM (CST).  What shows up on the SP list is Start time 12:30am and 9:30am.  Both are 6 hours behind what it should be.  I thought it was just a simple matter of converting the time zone to UTC as I've done in my other flows.  But when I try to do that with the parsed outputs, they aren't there to select.  I can select them as dynamic content directly in the Create item function....but I can't select them in the Convert expression.  Hope that makes sense.

[wskinnermctc · Accepted solution]

I have made a flow that will take the MS Form responses and convert into times that will post correctly into a SharePoint list. I know your flow has Case steps, but you can just adjust mine to fit into your Case steps.

Edit Note: I forgot to say that the use of the Array and the Do Until loop within my example flow removes the use of an external Excel file. Basically all of the work you were doing with the Excel file is now completed within the Do Until loop and the Array is used as a source of formatted datetime list just like the Excel file was.

There are some key things to think about when getting this to format correctly.

• SharePoint Display DateTime
• Daylight Savings

Your SharePoint has a local display setting that can only be changed by an administrator. It will most likely have your datetime display set to your local timezone. When you put a date into a SharePoint list that is in UTC standard time, the display settings will show it as your local time. So if you put Wednesday 2/22/2023 1:00 AM UTC into a SharePoint list, it will show as Tuesday 2/21/2023 7:00 PM.

If you want your SharePoint list to show the time of Wednesday 2/22/2023 1:00 AM Central Time then you need to insert into the list Wednesday 2/22/2023 7:00 AM UTC. The display settings will convert to show the correct time you want to see.

Daylight Savings

The use of daylight savings is very tricky when it comes to time conversion. Power Automate can accommodate daylight savings conversions, but you have to make sure to set it up properly so the conversion can happen.

I don’t have all of the secrets of daylight savings, but it usually involves using the local time converted into UTC that will give you the most accurate answer.

If you want to set up recurring schedules at 12:00 PM every Wednesday for Central Time zone you have to consider that this will look differently in UTC. So look at the chart below.

Wednesday 2023-02-22 12:00 PM CST = 2023-02-22 18:00:00Z UTC (6 hours difference)

Wednesday 2023-03-01 12:00 PM CST = 2023-03-01 18:00:00Z UTC (6 hours difference)

Wednesday 2023-03-08 12:00 PM CST = 2023-03-08 18:00:00Z UTC (6 hours difference)

Wednesday 2023-03-15 12:00 PM CST = 2023-03-15 17:00:00Z UTC (5 hours difference due to DST)

Wednesday 2023-03-22 12:00 PM CST = 2023-03-22 17:00:00Z UTC (5 hours difference due to DST)

Consider this when using your flow because you ultimately need the correct UTC time to be posted into your SharePoint list.

I’m saying all of this to understand the flow I made better and also highlight an error I made when first making this flow.

If you took your schedule time of 2/22/2023 12:00 PM CST and said you want to have it recurrence of 10 times. To convert to CST time to UTC and add 7 days for each week to reschedule it would make the time 18:00:00Z UTC for all 10 recurrences. This wouldn’t be correct when it comes to daylight savings time because the time showing in the SharePoint list would be 18:00:00Z UTC converted to CST/CDT which would be 1:00 PM in CDT.

So anyways here is the flow below.

The basic outline is below:

• Format the start datetime and end datetime that comes from the MS Form
• Have 4 separate Variables that can be set and reset to calculate for schedule recurrence.
  • Variable StartDateTime CST = value from Form
  • Variable EndDateTime CST = value from Form
  • Variable StartDateTime UTC = blank
  • Variable EndDateTime UTC = blank
• Have a variable that counts the number of Do Until loops for recurrence
  • Variable DoUntilLoopCounter = 0
• Have a variable that holds the number of schedule recurrence.
  • Variable Recurrence = value from Form
• Have a variable array that holds all of the formatted datetimes.
  • Variable RecurrenceListArray = [] blank array brackets
• Begin a Do Until loop for the amount of recurrence (example 4 recurrence)
• Do Until will continue until the variable DoUntilLoopCounter is equal to the count of Recurrence.

Within the Do Until loop

Loop 1:

• Put the converted Start/EndDateTime UTC into RecurrenceListArray (first item in array)
• Add 7 days to the Start/EndDateTime CST so now you have Start/EndDateTime CST+7days
• Convert the Start/EndDateTime CST+7days into UTC so now you have UTC+7days
• Add 1 to the DoUntilLoopCounter so it now equals 1

Loop 2:

• Put the converted UTC+7days into the RecurrenceListArray (second item in array aka 2nd recurrence)
• Add 7 days to the Start/EndDateTime CST+7days so now you have Start/EndDateTime CST+14days
• Convert the Start/EndDateTime CST+14days into UTC so you now have UTC+14days
• Add 1 to the DoUntilLoopCounter so it now equals 2

Loop 3:

• Put the converted UTC +14days into the RecurrenceListArray (third item in array aka 3rd recurrence)
• Add 7 days to the Start/EndDateTime CST+14days so now you have Start/EndDateTime CST+21days
• Convert the Start/EndDateTime CST+21days into UTC so you now have UTC+21days
• Add 1 to the DoUntilLoopCounter so it now equals 3

Loop 4:

• Put the converted UTC +21days into the RecurrenceListArray (fourth item in array aka 4th recurrence)
  • The loop will add 7 days to CST+21days but it doesn’t matter
  • The loop will convert the CST+28days to UTC but it doesn’t matter
• Add 1 to the DoUntilLoopCounter so now it equals 4

Loop 5:

• The Do Until loop will see that the variable DoUntilLoopCounter is 4 which is equal to the variable Recurrence so the loop will stop.

This is out of the Do Until loop

• A step Select is used to map the RecurrenceListArray which treats the array as a list of items similar to a Get Items
  • Map the columns to the values of the array
• Parse JSON of the Select
  • This will allow the use of dynamic values
• Apply to Each of the Parse JSON items
  • Create an Item in SharePoint for each of the recurrence

Below are Example photos of the flow:

All Flow Steps Outline

Below is an example MS Form that I made and filled out to use in this example. I have submitted a schedule that starts on Wed 2/22/2023 at 10:15 AM and ends at 3:45 PM and I want it to be scheduled for 5 times.

Example Form Response Submission

Below are the detail steps of the flow:

• Format the start datetime and end datetime that comes from the MS Form
• Have 4 separate Variables that can be set and reset to calculate for schedule recurrence.
  • Variable StartDateTime CST = value from Form
  • Variable EndDateTime CST = value from Form
  • Variable StartDateTime UTC = blank
  • Variable EndDateTime UTC = blank
• Have a variable that counts the number of Do Until loops for recurrence
  • Variable DoUntilLoopCounter = 0
• Have a variable that holds the number of schedule recurrence.
  • Variable Recurrence = value from Form
• Have a variable array that holds all of the formatted datetimes.
  • Variable RecurrenceListArray = [] blank array brackets

First Part of Flow to Initialize VariablesSecond Part of Flow to Convert StartDateTime CST to UTCThird Part of Flow to Convert EndDateTime CST To UTC

• Begin a Do Until loop for the amount of recurrence (example 4 recurrence)
• Do Until will continue until the variable DoUntilLoopCounter is equal to the count of Recurrence.

Within the Do Until loop

Loop 1:

• Put the converted Start/EndDateTime UTC into RecurrenceListArray (first item in array)
• Add 7 days to the Start/EndDateTime CST so now you have Start/EndDateTime CST+7days
• Convert the Start/EndDateTime CST+7days into UTC so now you have UTC+7days
• Add 1 to the DoUntilLoopCounter so it now equals 1

Loop 2:

• Put the converted UTC+7days into the RecurrenceListArray (second item in array aka 2nd recurrence)
• Add 7 days to the Start/EndDateTime CST+7days so now you have Start/EndDateTime CST+14days
• Convert the Start/EndDateTime CST+14days into UTC so you now have UTC+14days
• Add 1 to the DoUntilLoopCounter so it now equals 2

Loop 3:

• Put the converted UTC +14days into the RecurrenceListArray (third item in array aka 3rd recurrence)
• Add 7 days to the Start/EndDateTime CST+14days so now you have Start/EndDateTime CST+21days
• Convert the Start/EndDateTime CST+21days into UTC so you now have UTC+21days
• Add 1 to the DoUntilLoopCounter so it now equals 3

Loop 4:

• Put the converted UTC +21days into the RecurrenceListArray (fourth item in array aka 4th recurrence)
  • The loop will add 7 days to CST+21days but it doesn’t matter
  • The loop will convert the CST+28days to UTC but it doesn’t matter
• Add 1 to the DoUntilLoopCounter so now it equals 4

Loop 5:

• The Do Until loop will see that the variable DoUntilLoopCounter is 4 which is equal to the variable Recurrence so the loop will stop.

Do Until Pt1 Append to Array SectionDo Until Pt2 Convert StartDateTime CST +7Days

Do Until Pt3 Convert EndDateTime CST+7Days

This is out of the Do Until loop

• A step Select is used to map the RecurrenceListArray which treats the array as a list of items similar to a Get Items
  • Map the columns to the values of the array
• Parse JSON of the Select
  • This will allow the use of dynamic values
• Apply to Each of the Parse JSON items
  • Create an Item in SharePoint for each of the recurrence

Fourth Part of Flow Select values from Array

Fifth Part of Flow Apply to Each Create Items

Below is what the outputs look like in SharePoint.

SharePoint Results

I hope the steps outlined above help you make a flow that can post things correctly into a SharePoint list. If you make any changes always consider the differences that converting times to UTC and how it will be displayed in your SharePoint list based on local settings.

Additional Note About Flow:

The step Select that maps values requires that you manually type in the value you want to get out of the array like item()?['StartDateTime'] in the right side of the map columns. There is an issue after you save the flow that the display of the Select step will change and appear like the values are coming from the Form Response. This is just a display issue most likely caused because the column names in the array are the same to the display name of the form response values. This does not change that the values are actually being mapped from the RecurrenceListArray and not the Form Response.

Select step Display Change Notice

Let me know if this works for you or if you have any questions.

[EMY9317]

This is great.  It works!  I understand what's happening...just still a little fuzzy on how it's doing it.  I just need to study it a bit more so I can truly understand the logic behind it.  I've never used Scope or Do Until functions before.  Hopefully this opens up some new doors for me.  I'm so appreciative of the time you took to help me with this!

[wskinnermctc]

Scope is just a container to hold steps as a way to organize them; it doesn't do anything. You can put Scopes inside Scopes inside Scopes. The only thing you can't put inside of a Scope is "Initialize Variables" for some reason, so that's why they are all out own their own at the beginning.

You don't have to use scopes, I use them for display purposes. But it is a way to add a container and put a note to say "Everything in this Scope container is for the purpose of whatever."

Do Until is usually called a loop since it will keep looping through the steps from top to bottom within that Do Until. It always runs the first loop even if the condition is met. It doesn't run the last loop once the condition is met.

A loop Do Until has a condition at the top of the Do Until. You are saying, "I want everything in this Do Until container to happen over and over until the condition at the top is met." If you put the condition at the top of the Do Until as 7 is equal to 9, then the loop would just continue running forever until the flow timed out. That is why you need variables or checks within the condition of the Do Until that change each time the Do Until loops over. 

If you put the condition at the top of the Do Until as 1 is equal to 1, then the steps within the Do Until would run, and then next loop around it would see the condition is met, and stop the loop. 

The steps in the Do Until always run at least once. Once the condition is met. The Do Until stops and it goes to the next flow steps after the Do Until.

That is why I started the variable DoUntilLoopCounter at 0. Each time the Do Until loops over, it adds 1 to the variable DoUntilLoopCounter. It checks to see if it matches the Recurrence count and continues going up by 1 until it equals. If you initialized the variable DoUntilLoopCounter at 1, it wouldn't get the last recurrence.

Sorry if the Scopes made it cluttered, I wanted it to be easier to read steps that are working together.

[wskinnermctc]

Another thing that keeps happening within the Do Until is that I'm setting the variables. Basically overwriting the variables each loop.

So I forgot to clean that part up.

For example, the StartDateTimeCST variable is initialized and has the datetime from the form. So the variable is initialized to equal 02/22/2023 10:15 AM. When it goes into the loop after the compose step it is changed to be 03/01/2023 10:15 AM. The variable keeps getting overwritten each loop.

The reason that is important is because after the Do Until loop in other following steps that variable now holds the last time it was set in the Do Until. So if you wanted to put StartDateTimeCST in something else, it will say 03/22/2023 10:15 AM.

If you want to 'reset' the variable, you would need to put a Set Variable step After to Do Until. (So outside the Do Until like before the Select or Apply to Each.) In this Set Variable you would basically use the same formula when the variable was initialized at the very beginning, so now it will hold the Form Response as expected.

[EMY9317]

Thank you for the explanation - that does help a lot.  I actually liked the scopes.  It made it easier to follow and I could see in each one that I was manipulating the Start date in one, the End date in another, etc.

So the Array variable and the Select and Parse functions are just taking each event item and defining it as a separate occurrence for however many recurrences are set, correct? 

[wskinnermctc]

The Do Until loads all of the different recurrences into the Array Variable. All of the recurrence are in the Array variable when the Do Until completes.

So the Array variable is holding a row for each occurence. It is like the excel sheet.

The Select is used to Map the array (You could maybe even skip this step but I do it to clearly see where things are laid out). The Select is saying, "Go into this array and map the values from the Array to the columns I titled on the left with the values on the right." So you could title the columns on the left side of the Select whatever you want. It will hold the values from the array.

You could probably Parse JSON the the array directly without the Select step, but it doesn't make a difference. I like using Select since it is easy to see.

The Parse JSON is reading the Select layout so that you can use the Parse JSON as dynamic content.

Do Until is basically putting all of the recurrence in the Array bucket.

After the Do Until the Array is poured into the Select bucket with mapped column names.

Then the Select is poured into the Parse JSON bucket so that you can use dynamic content.

[EMY9317]

I am bookmarking this page so I can refer back to these explanations!  I find so much stuff that says "do this" and I do it and it may work, but I have no idea what I did or why I did it. LOL