Microsoft office form add Time duration field

[EdisonDevadoss]

In my form, I need a Time duration field. But Microsoft form provides only date format. 

How I can add Time duration field as google form provided?

I added the below Image of google form Time duration field. I want the same field in Microsoft form also.

[#2]

@EdisonDevadoss there's no way to do this yet but there are plenty of uservoice requests for it which you can vote for at https://microsoftforms.uservoice.com/forums/386451-welcome-to-microsoft-forms-suggestion-box?query=t...

Rob
Los Gallardos
Microsoft Power Automate Community Super User

[#3]

Hi@EdisonDevadoss 

Yep, just like @RobElliott said, there's no way to do that. Even with Regular Expressions, because Forms doesn't support them yet. RegEx are alowed in "Forms Pro" (now called Users Voice) but it requires a Dynamics 365 license. There you could format the expected answer to be like: [number][number]:[number][number] (just saying, this is not necesary correct), so users can only write answers looking like hours or time.

In my forms I just put a note asking users to format the answer, but I pledge everyday hoping they do not commit any mistake:

Hope this helps you.

Cheers and let's keep waiting Microsoft some day allows RegEx or time format questions. (We have being wating til 2016)

[#4]

@EdisonDevadoss I am also looking for a duration but on the Excel workbook after the FORMS are answered.

I've found that it faults in two primary ways. 

One by formula pattern change within the form column value area.

I use the FORMS input to the data in cells of the C column with a start time, and a manual text or drop down entry in another column that causes a time stamp. That formula is: =IF(P2<>"",IF(Q2<>"",Q2,NOW()),"")   Both C2 and Q2 have the same time and date formatting: m/d/y  hr/mm
To get the difference I use the next formula
=TEXT(Q1-B1,"d:h:mm") is in R column and it works out fine for the initial cell.  But upon using the FORM and submitting, the excel adds another line and changes the formula values to =Q1-B1  and the presented time difference is then off.  Without using FORMS the logic and pattern =TEXT(Q1-B1,"d:h:mm") holds and the display time Days-Hours-Minutes comes out correct. Then when using FORMS to fill out further queries it interjects a new Time formula: =Q1-B1 for all of the following rows
That new formula shows an incorrect format and the duration is then off for each row. Basically FORMS changes the forumula from =TEXT(Q1-B1,"d:h:mm") to =Q1-B1
It skews what we are after.

Next, I tried to work outside of the Form row and question set column input areas.  Off to the right and enter  =TEXT(Q1-B1,"d:h:mm") to the right of the last FORM input data column.  I even gave it 4 or 5 empty columns as a buffer.  It is apparent that excel uses a spacer row on the bottom of the last entered form record.  Any independent column of autofilled patterned formula will be broken and offset by that FORMS input row formatting.  That destroys the automation of the need to category times and durations.  

Is there any way around this or an outright fix for it?

[#5]

@MSCIDM it is not a good idea to try to manipulate the spreadsheet behind the form.  You can use a flow in Power Automate to get the start and completion times of your form responses, save them to a SharePoint list and then you can get the duration with a calculated column.

Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)

[#6]

@Rob_Elliott I understand your advice. Though there is a growing need to use these worksheets behind forms as active tools for SharePoint and even teams channel active status and record keeping. Adding formula and column categories is only a natural progression of the tool. Can you provide a power automate example that scrapes the original workbook to output to another workbook or channel?

[#7]

@MSCIDM an example that gets your forms responses and saves them to the SharePoint documents library as a new excel spreadsheet containing the start and completion time is shown below.

I've used a manual trigger followed by the Get my profile (v2) action.

Next, add a Send an HTTP request to SharePoint action with the following parameters. I've renamed it to FormInfo.

Site Address: https://forms.office.com/

Method: GET

Uri: formapi/api/3d234255-e20f-4205-88a5-9658a402999b/users/@{outputs('Get_my_profile_(V2)')?['body/id']}/light/forms('VUIjPQ_iBUKIpZZYpAKZm2tQtOivi1hPg51lh-vO1rRUQ1RUTzExRjNMTVYwVjNZUTEwSFRYUVhLNi4u')

So for the the first part in blue you need your Tenant ID.. For the Users/../ select ID from the dynamic content box, and for the last part in blue paste in the ID of your form inside single apostrophes as shown below:

Next, add another send an HTTP request to SharePoint action with the following parameters. I've renamed it to FormResponses.

Site Address: https://forms.office.com/

Method: GET

Uri: formapi/DownloadExcelFile.ashx?formid=VUIjPQ_iBUKIpZZYpAKZm2tQtOivi1hPg51lh-vO1rRUQ1RUTzExRjNMTVYwVjNZUTEwSFRYUVhLNi4u&minResponseId=1&maxResponseId=@{body('FormInfo')?['rowCount']}

For the part in blue paste in the form ID (this time without single apostrophes) and for the row count select it from the dynamic content box. In Headers enter Content-Type then application/json as shown below:

Finally, add a SharePoint create file action and select your site and library. We need the file name to be unique so in the File name field enter an expression of utcNow() followed by .xlsx

For the File Content field select Body from the FormResponses section of the dynamic content box.

Run the flow and the new Excel file will be created in the library:

You can now open the spreadsheet and do whatever calculations you want on it.

Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)