Need to input a time from MS Forms to a SP List date and time column.

[EMY9317]

Hello there, I visit here for help a lot, but I've never posted a question before.  I am a beginner and not a developer.  I'm stronger in Power Automate than Power Apps.  I've not successfully created an app yet, but have several successful flows.  I am trying to keep my builds in Power Automate since I can build and support much better in this environment.

I have a flow that is triggered when a new time off request form response is received.  When the form is submitted, the flow will take the form info, start an approval and update a SP list (a modern SP calendar view).  The flow works fine, but I've been asked if I can fix the fact that all of the calendar items, when viewed in the events pane, show as "All Day" events even when they are not full day events.  They would like to see the From and To times here.  

I'm pretty sure this is because the Start Time and End Time columns in the SP list are single line text fields and not actual time fields as part of the date/time picker.  However, it appears that Forms does not have the same date/time picker fields as SP does.  Because these are time off requests for future days, I cannot use the created or modified dates and times.  It must be data that is entered by the user into the form.

Is there a way to take the information from the single line text field in the form and input it into the date/time picker in the SP list?  If so, what would that look like?

Happy to share additional info if needed.

[wskinnermctc · Accepted solution]

I don't think you're going to have a good method so long as the user can type in a time since you can't mandate how they write the text. Example they might put 3pm, 3:00pm, 3 PM, or Starts at 3:00PM. You can't accommodate all of those variations with a power automate expression.

You'll need to create some format of choices in the form like Select Start Time: 1:00 PM, 2:00 PM, or 3:00 PM and then use that result in power automate to formatDateTime(Date & StartTime, 'MM-dd-yyyy hh:mm:ss tt') and then put that in a DateTime column in SharePoint. 

It will be difficult to put the select times in the form because you will have to decide if you'll put all of the times, or maybe like 3 questions for start time like 1.StartHour (1,2,3,4,5 etc) 2. StartMinute (00, 15, 30, 45) 3. AM/PM.

Or you could try and make a starttime question and then a duration question like hours, minutes. Then with those responses you can format them in power automate to make a time that you can use.

The more standard you can make the time selection in the form, the easier the formatting of time in power automate and sharepoint will be. You could try and use text responses from a form and say in the question (Please format your time as 3:00 PM or 8:15 AM), but I can guarantee people will type in whatever they want and it will result in errors.

An alternative would be to make a powerapp from the SharePoint list and it can easily handle start and end time columns.

Also I think Forms Pro (additional paid license) can use time inputs.

[EMY9317]

I was afraid of that.  I don't think my company has a Forms Pro license.  I don't think I can build it in Power Apps.  I'm taking the App in a Day session in a few weeks to try to get more familiar with it but anything I've tried to build has not worked so clearly there is a disconnect with me and that environment.

I appreciate the response! 

[wskinnermctc]

I was thinking, there is a little bit of a round about way to get them to fill in the time on the sharepoint list by directly editing the list item after they submit the form. So they fill out the form that creates a new item in the list. Then power automate kicks off a flow. You will need some extra columns in the flow to help manage the steps. So you could have a column that is Approved (Y/N), and then a StartDateTime and EndDateTime. 

When they submit the form it creates the item, but the Approved, StartDateTime and EndDateTime are all blank since they aren't connected to the form. The flow kicks off and it checks if the Approved is  N, and if the StartDateTime and EndDateTime are filled. If they are not filled, then it sends an email with a link to the item saying to fill out the dropdown datetimes. After they complete that the flow will rerun since the item was modified, this time it sees that the datetimes columns are filled and it will continue along the manager approval branch. There might be some catches you will have to work out like double edits or corrections, but it could work.

Also you shouldn't think power apps is too difficult. You could make a sharepoint list with all of your columns, and then look above the list click integrate with power apps or create a form and it will start pulling in stuff from the list into an app. You could find videos on what to do next. Or to create your own canvas app, once you figure out how to create a dropdown box in the app, link the box to a sharepoint list, and patch to create a new item, you can make anything.

Obviously the MS Form is easier and keeps people from directly editing the list. But a power app isn't much more different than a MS form once you figure out how to make selection boxes and patch to a list with a new item.

[wskinnermctc]

This is not exactly a clean look for a MS Form; however, it would work in the meantime until you get something better up and running in a power app. Just have them select each time item in the form and then in PA combine the answers together to format a time that you can use.

MS Form Time Selection

[EMY9317]

I don't think I like the idea of them completing the form and then going back to update the list.  Especially since my flow is creating items on 2 separate lists; one is the team calendar only if the approval results in Y, and the other is a management list that has more info and populates regardless of the approval decision.  Users don't have access to and can't see the management list.

I'm trying out the drop down menu solution just to see what that looks like on the form and if I can get it to work.  I need to figure out the correct input format for the SP list date/time picker so I can get that field to populate.  Then I can show the solution to the managers and see if they prefer this way.

In the meantime, I'll keep working on a Power App and see if I can get it to work.  I'm self-teaching everything so I just need to get myself to a point where what I'm doing makes sense.  Power Automate makes sense to me.  Power Apps, not so much yet.  I'm working on it!

[EMY9317]

This is what I have in the test form.