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
