Calculate time duration from forms input

[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.

[Expiscornovus]

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) 

[HebbyHebz]

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.

[Expiscornovus]

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?

[HebbyHebz]

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 · Accepted solution]

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)

[HebbyHebz]

It works!🕺 🕺🕺
Thank you! @Expiscornovus