Forum Discussion
Microsoft office form add Time duration field
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?
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)