How to seperate Date,Hour,Minute and Second in XLS Form

Support
,
1

How to know how much time is spent to the filling of a Form

2

This seems to be an Excel question.

3

Yes
When you Download Submitted form you will face to this .

4

If you connect Excel directly to ODK Central, it will automatically convert the start/end columns into datetime columns so you don't have to take the manual steps below. See https://docs.getodk.org/central-submissions/#downloading-submissions-with-power-bi-or-excel for how.

If you want to do the datetime conversion manually, you have two options. You might need to format the resulting cells to display the datetime properly.

  1. You can use the TEXT function in Excel to replace the T with a space and removes the Z (which stands for Zulu time or UTC), then format it as a standard date and time.

    =TEXT(SUBSTITUTE(SUBSTITUTE(A1,"T"," "),"Z",""),"yyyy-mm-dd hh:mm:ss")

  2. You can also use DATE,TIME,MID` to separately extract the year, month, day, hour, minute, and second from the ISO 8601 timestamp and then combine them using DATE and TIME functions.

    =DATE(MID(A1,1,4), MID(A1,6,2), MID(A1,9,2)) + TIME(MID(A1,12,2), MID(A1,15,2), MID(A1,18,2))

2 Likes