Forum Discussion
Microsoft office form add Time duration field
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.
6 Replies
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?
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)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?
HiEdisonDevadoss
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)
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=time
Rob
Los Gallardos
Microsoft Power Automate Community Super User
