how I calculate survey time using excel functions?

start

2020-07-29T18:45:16.560+03:00

end

2020-07-29T18:47:25.950+03:00

how I calculate survey time using excel functions?

start

2020-07-29T18:45:16.560+03:00

end

2020-07-29T18:47:25.950+03:00

Could you please have a look at the post discussed previously (it should solve your issue):

1 Like

thanks @Kal_Lam

but i need to calculate the survey duration after i download data

and use **excel functions** ( not kobo functions)

Hi @rahhal

Thank you for your query. I would like to start this with a disclaimer that this is an excel function more than it is a KoBoToolBox function. The best approach would be to always include this in a calculate question (which is hidden from the respondents) and you can then have it exported within your data.

However considering that most of our users may want to go the long route of calculating this in excel, I have prepared the following workaround in excel that uses a number of calculation and extraction functions below. Kindly note, I have omitted the logic behind the formula which does not form part of support

The formula you will need in excel is as below (REPLACE Cell B2 with the cell containing end time and A2 with the cell containing start time)

**=((DATEVALUE(LEFT(B2, SEARCH(“T”,B2)-1))+(TIMEVALUE(LEFT((RIGHT(B2,LEN(B2)-SEARCH(“T”,B2))), SEARCH(".",(RIGHT(B2,LEN(B2)-SEARCH(“T”,B2))))-1))))-(DATEVALUE(LEFT(A2, SEARCH(“T”,A2)-1))+(TIMEVALUE(LEFT((RIGHT(A2,LEN(A2)-SEARCH(“T”,A2))), SEARCH(".",(RIGHT(A2,LEN(A2)-SEARCH(“T”,A2))))-1))))) 24*

See the image below for an example

Kindly use the attached excel as a dummy to play around.

TimeDifference_BasedonKoBoExports.xlsx (8.9 KB)

NOTE: This cell assumes that you are on the same timezone for the two times i.e. Time 1 and Time 2. If you expect to have different timezones, then you need to tweak the logic I have prepared.

All the best.

Stephane

1 Like