Answered

Calculate time duration from forms input

(0) Share

Report

Posted on by HebbyHebz

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.

Categories:

Building flows

I have the same question (0)

All responses (6)

Answers (1)

Expiscornovus 33,195 Most Valuable Professional on at

Like (0)

Report
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)

Was this reply helpful? Yes No
HebbyHebz 44 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Expiscornovus 33,195 Most Valuable Professional on at

Like (0)

Report

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?

Was this reply helpful? Yes No
HebbyHebz 44 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Verified answer

Expiscornovus 33,195 Most Valuable Professional on at

Like (0)

Report
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)

Was this reply helpful? Yes No
HebbyHebz 44 on at

Like (0)

Report

It works!🕺 🕺🕺
Thank you! @Expiscornovus

Was this reply helpful? Yes No