cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rob_CTL
Helper V
Helper V

Working with a time field that is text (Forms -> SharePoint)

Hi,


Bit of a challenge today, I am trying to find a way to deal with a limitation of Microsoft Forms in that it doesn't have a "Time" control.  At the moment I have two text controls on the form "Start Time" and "End Time", here users input the time they started/finished a job in the format 11:00 (they are using 24H clock so I don't have to worry about AM/PM).  The start/end times are always within the same day so no issues with trying to combine two days.

 

pa-form1.png

 

What I am trying to achieve is to calculate the amount of time between the two time fields using Flow then push this result into a Number column within SharePoint.

 

pa-form2.png

 

Any thoughts gratefully received.

 

Cheers

Rob

1 ACCEPTED SOLUTION

Accepted Solutions
Mira_Ghaly
Dual Super User II
Dual Super User II

@Rob_CTL 

1. you need to convert the start and end times to an Integer using a an expression details as below

 

 

I initialized 2 variables you can replace with your variables from the form

Mira_Ghaly_0-1597748864767.png

New Int Variable as below :int(split(variables('Start'),':')[0]), repeat the same for End

Mira_Ghaly_1-1597749086632.png

 

 

Mira_Ghaly_0-1597749213262.png

 

 

Then use the the subtract expression as below to get the difference as below:

sub(variables('EndInt'),variables('StartInt'))

Mira_Ghaly_1-1597749300871.png

If this post helps you with your problem, please mark your as Accepted solution.

If you like my response, please give it a Thumbs Up.

MG (Naturally Curious)

 

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here

View solution in original post

2 REPLIES 2
Mira_Ghaly
Dual Super User II
Dual Super User II

@Rob_CTL 

1. you need to convert the start and end times to an Integer using a an expression details as below

 

 

I initialized 2 variables you can replace with your variables from the form

Mira_Ghaly_0-1597748864767.png

New Int Variable as below :int(split(variables('Start'),':')[0]), repeat the same for End

Mira_Ghaly_1-1597749086632.png

 

 

Mira_Ghaly_0-1597749213262.png

 

 

Then use the the subtract expression as below to get the difference as below:

sub(variables('EndInt'),variables('StartInt'))

Mira_Ghaly_1-1597749300871.png

If this post helps you with your problem, please mark your as Accepted solution.

If you like my response, please give it a Thumbs Up.

MG (Naturally Curious)

 

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here

View solution in original post

@Mira_Ghaly thanks for the reply, I managed to use SharePoint calculated columns to do the transformation but I like your solution so I'll keep that in my tool box for next time.

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Users online (1,688)