cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HebbyHebz
Frequent Visitor

Calculate time duration from forms input

Hello,

I'm trying to create an Overtime Form, but since MS Forms has decided that time doesn't exist, I have to manually add time intervals of 30 minutes from 5 PM. How do I create a flow/expression that calculates duration in hours i.e (End Time - Start Time) from what the user selects in the form? 

 

Many thanks.

HebbyHebz_0-1626963871280.png

HebbyHebz_1-1626963886489.png

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Expiscornovus
Super User II
Super User II

Hi @HebbyHebz,

 

In that case you need a bit of more logic. Because a choice option will output as a string.

 

We will need a formatdatetime function as well. I adjusted my earlier expression to reflect the usage of a choice option question.

 

Below is a new example of the expression which subtracts the start time from the end time and shows the duration in hours. Also added a float function since I am expecting you also want to show 0.5 hours as a result?

 

 

div(float(sub(ticks(formatdatetime(outputs('Get_response_details')?['body/r5c2ccaa4342a46f29c4326adc2c53829'], 'yyyy-MM-ddTHH:mm')), ticks(formatdatetime(outputs('Get_response_details')?['body/r1c8d7e9e8f6b4bc688c984895ff6f93a'], 'yyyy-MM-ddTHH:mm')))), 36000000000)

 

 

 

 

View solution in original post

6 REPLIES 6
Expiscornovus
Super User II
Super User II

Hi @HebbyHebz,


Just to double check. Is it only a time field or a date/time field?

 

In case of the latter you could use a ticks function for both fields and subtract the start date/time from the end date/time. When you divide this number by 36000000000 you will convert it back to hours.

 

Below is an example of that approach.

In my example body/r6bedb046699349bbb4a4681074f901f2 is end date/time and body/r411b8d53aaa5425f9185781ac48a2d06 is start date/time.

 

 

div(sub(ticks(outputs('Get_response_details')?['body/r6bedb046699349bbb4a4681074f901f2']), ticks(outputs('Get_response_details')?['body/r411b8d53aaa5425f9185781ac48a2d06'])), 36000000000) 

 

 

endtimeminusstarttime.png

Hello @Expiscornovus,

 

Thanks for replying. It really is just a time field. Although I'm curious as to how you got one field to take in both date & time?

Could you please explain how to get a single field on my form to take in both date and time - the closest I've seen on my standard MS forms is the date input, I think?


And also, how would the flow work with just time as in the screenshots? Any expression/function for this?


Many thanks.

HebbyHebz_0-1627033836746.png

 

Expiscornovus
Super User II
Super User II

Hi @HebbyHebz,

 

I think I got confused, my bad. I thought it was possible on a Date type question to enable time in the settings of the question. But this doesn't appear to be possible.

 

How did you create that start and end time questions. I guess just with a Choice question and added those PM times manually, correct?

Yes, that's exactly what I did; I manually added those PM times to a choice question.

How would I get the duration, i.e End Time - Start Time?

Also, do the times I add to the choice question have to be in a particular format?

 

Many thanks again @Expiscornovus 

Expiscornovus
Super User II
Super User II

Hi @HebbyHebz,

 

In that case you need a bit of more logic. Because a choice option will output as a string.

 

We will need a formatdatetime function as well. I adjusted my earlier expression to reflect the usage of a choice option question.

 

Below is a new example of the expression which subtracts the start time from the end time and shows the duration in hours. Also added a float function since I am expecting you also want to show 0.5 hours as a result?

 

 

div(float(sub(ticks(formatdatetime(outputs('Get_response_details')?['body/r5c2ccaa4342a46f29c4326adc2c53829'], 'yyyy-MM-ddTHH:mm')), ticks(formatdatetime(outputs('Get_response_details')?['body/r1c8d7e9e8f6b4bc688c984895ff6f93a'], 'yyyy-MM-ddTHH:mm')))), 36000000000)

 

 

 

 

View solution in original post

HebbyHebz
Frequent Visitor

It works!🕺 🕺🕺
Thank you! @Expiscornovus 

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,333)