Calculating time spent on multiple activities

Support
1

Hi,

I'm trying to create a time-sheet form for field workers to record their activities + clock their hours. I want it to calculate the time spent on activities and aggregate the total time at the end of the form.

I'm really struggling to work out how to calculate the time. When recording the number of activities, I've set it to skip activities if they haven't been completed. If all activities are completed, then the calculation works, however if one of the time questions is left blank, I get error messages.

I've attached the document here to show what I've tried. I also don't know the proper way of calculating hours spent and have made up some division to get there - but it's not quite perfect. If anyone has any suggestions I'd be so grateful!

Thanks, Gabrielle

MONTH. PF timesheet and expenses test 3.0.xlsx (38.2 KB)

2

Welcome to the 'Curse of NaN'! :slight_smile:

Problem is your blank questions aren't being treated as 0 (zero) but rather NaN (literally "not a number"), which throws your entire calculation off. Trick is to explicitly convert any blank responses to 0. Try this:

coalesce(${round_cpclinic1hrs},0) + coalesce(${round_cpclinic2hrs},0)+ coalesce(${round_cpclinic3hrs},0)

which will basically give you a 0 when your response is null. coalesce() is documented here

  • Gareth 'The NaN Master' Bestor
2 Likes
3

It worked! Thanks so much, I had tried coalesce before, but had put it in the other calculation groups and it hadn't worked.

You Bestor-ed the dread NaN!

2 Likes
4

I reckon Nafundi (aka @yanokwa and @LN) should pay me a $buck every time this comes up - I could have bought a house in Auckland by now! :wink:

5

@Xiphware, I don't think that's going to be sustainable :laughing:

There is a PR adding this to the docs at https://github.com/opendatakit/docs/pull/734/files

6

adding extra calculations and using an if() would work too as explained here;