I’m trying to calculate the number of hours between 2 date/times.
kobo temp.xlsx (17.9 KB) The calculation attached is part of the form. The rest of the form all works perfectly but this part does not. What am I doing wrong?
Hi @katie_inker,
When you want to convert this into time difference there are a couple of things you need t o consider:
- The date differences provides days and you have to multiply by the hours in a day (you got this assumption right.
- You can add the calculation on to the integer directly as shown here.
The result presents a perfect calculation as seen below.
You can find this XLS form here Hours Calculated Between Dates.xlsx (18.1 KB)
Stephane
1 Like
Hi Stephanie, Thanks very much for this. I see that I do not need the calculation to be in a separate line which is great. However I’ve uploaded your corrected excel and it does not do what I want. So if my departure time is 11am today and I return at 4 pm today the calculation returns 0 instead of 5 hours. If I change my return day to the next day it then calculates as 24. Is there a way to give me what I want?
Hi,
I think you have a problem with the time input and not calculation. Could you please do the following:
- Send screenshots of every time capture.
- Look at the back end and see what is the long date format of the time captured.
- Kindly send a screenshot of your formula.
Stephane
Hi again Stephanie,
That makes perfect sense but I’m not specifying a date format so I’m not clear how this goes wrong.
Attached is the excel form I’m using, date captures and output.
Does this explain the issue?
Once again thanks so much for your help.
Kind regards,
katie
Logbook Ethiopiai v8.xlsx (20.1 KB)
Logbook Test Version 1 - all versions - labels - 2020-02-03-16-47-58 (1).xlsx (15.5 KB)
Hi again Stephane. Have you by any chance been able to look at this issue?
I have finally managed to make this work using decimal-date-time. Posted in case anyone else has this issue!
1 Like
Hi @katie_inker,
Would you be kind enough to share your solution with the community so that the community could use your solution when needed.
Have a great day!
I’m sure it is possible to simplify this so the calculation is just one line - please let me know if you have this solution!
1 Like
Hi @katie_inker
Your solution can be done in one liner
The XLS form is attached here Hours Calculated Between Dates v2.xlsx (18.7 KB)
Notice I used an integer question with calculate which allows me to see the time difference.
Please note that you must add a statement of global time difference if the departure is located at a different place from the return geographically. This will add computation difference of time.
Stephane
1 Like
Thanks Stephane
2 Likes
Hi again Stephane. I have now tried this solution. When I run it in preview it works fine but when I deploy it to my phone no calculation is shown. Any ideas?
1 Like
Hi @katie_inker
I just noticed that this seems like a bug issue where decimal-date-time does not work on KoBoCollect & ODKCollect the same way as Enketo. I have however worked this out differently to ensure that it works on both by using a combination of date differences and decimal-date differences as shown below.
You can find the XLS here Hours Calculated Between Dates v3.xlsx (19.9 KB)
This seems to have worked out the issues.
Regards,
Stephane
1 Like
Thank you Stephane
1 Like
