How can I collect the time (date, star and end time ) of each entry?

Question

How can I collect the time (date, star and end time ) of each entry?

Mina Wang 0

Hi,

We were using Forms to collect the visitors' feedback for the exhibition. Here is the questionnaire.

https://forms.cloud.microsoft/Pages/DesignPageV2.aspx?origin=NeoPortalPage&subpage=design&id=DQSIkWdsW0yxEjajBLZtrQAAAAAAAAAAAAN__kDyiHdUMFMzRUVKWEY1VVNHVFlUWk0xNUpIVFQ4Uy4u

As we have to analyze the date and time of each entry to understand which date most visitors came during the exhibition, could you help me with the time data about date, start and end time of each entry?

Thank you so much!

Mina

0 comments

2 answers

Your answer

Answer 1

Hi @Mina Wang

It looks like the link you shared is the form editing link (DesignPage), so I'm unable to access it directly. To help you more accurately, could you share a few screenshots (for example, your form's Responses tab or the data view you're currently seeing) so I can better picture your setup?

In the meantime, here is how you can retrieve the date, start time, and end time directly from to see if this gives you everything you need:

Microsoft Forms automatically records the exact date, start time, and completion time for every submission in the background, so you don't need to add manual date/time fields. You can export this data by following these steps:

Open your questionnaire in Microsoft Forms
Click on the Responses tab at the top.
Click Open in Excel (or select the dropdown next to it to download a .xlsx copy to your device).

Once you open the Excel file, you will see two default system columns at the beginning of the table:

-Start time: The exact date and timestamp when the visitor opened the form.

-Completion time: The exact date and timestamp when the visitor submitted the form.

Feel free to give those steps a try, or send over a few screenshots so I can walk you through the next steps. Thank you.

Answer 2

Export the form responses to Excel; each response row includes separate Start time and Completion time values that can be used to derive the response date, start time, end time, and completion duration.

Export the timestamps

Open the form in Microsoft Forms.
Select View responses (or the Responses tab).
Select Open results in Excel.
- Open results in Excel creates a workbook with a live connection to the form.
- Download a copy creates an offline snapshot that will not receive later responses.
Open the workbook and locate the Start time and Completion time columns. Microsoft documents these among the first five columns of the exported response data, with one response per row.^1^

The timestamps apply to responses already submitted, so no additional date/time questions need to be added to the questionnaire.

Prepare the data in Excel

Assuming the exported data has been converted to an Excel table and its timestamp columns are named Start time and Completion time, add these calculated columns:

Response date

=INT([@[Start time]])

Format the result as a date, such as dd/mm/yyyy.

Start time only

=MOD([@[Start time]],1)

Format it as hh:mm:ss.

End time only

=MOD([@[Completion time]],1)

Format it as hh:mm:ss.

Time taken to complete the form

=[@[Completion time]]-[@[Start time]]

Format the duration using the custom number format:

[h]:mm:ss

If the export is not an Excel table, use ordinary cell references instead. For example, if Start time is in B2 and Completion time is in C2:

=INT(B2)
=MOD(B2,1)
=MOD(C2,1)
=C2-B2

Count responses by exhibition date

Select any cell in the response data.
Choose Insert → PivotTable.
Add Response date to Rows.
Add Respondent ID to Values and set it to Count.
Sort the count from largest to smallest.

This shows which date received the most submissions. If finer analysis is needed, add an hour column:

=HOUR([@[Start time]])

Then use Response date and the hour in the PivotTable to identify the busiest response periods.

The Start and Completion times measure when the visitor began and submitted the questionnaire. They do not necessarily represent when that visitor entered and left the exhibition. If actual arrival and departure times are required, those must be collected separately in the form or through a check-in/check-out process.

References

Check and share your form results | Microsoft Support