Calculations in Survey123 connect form

RachelS

Hi,

I'm trying to create 2 calculations at the bottom of my form for totalhours and totalunsocialhours.

1. The first is totalhours, the formula I've tried only works if all the 6 fields are filled out which won't always be the case. How do I allow only if it's filled in?

${hoursworked1}+${hoursworked2}+${hoursworked3}+${hoursworked4}+${hoursworked5}+${hoursworked6}

 

2. The second is much more complicated. There are 3 time periods to choose from in a drop down list  12-8am, 8-6pm, 6-12pm.

If either 12-8am or 6-12pm I want to add the hours.

if(${timeperiod1} 12-8am or 6-12pm then add {hoursworked1}
if(${timeperiod2} 12-8am or 6-12pm then add {hoursworked2}
if(${timeperiod3} 12-8am or 6-12pm then add {hoursworked3}
if(${timeperiod4} 12-8am or 6-12pm then add {hoursworked4}
if(${timeperiod5} 12-8am or 6-12pm then add {hoursworked5}
if(${timeperiod6} 12-8am or 6-12pm then add {hoursworked6}

But again all 6 may not be filled out, there may be any between 1 up to 6.

I'd appreciate any help with this.

Rachel 

Staff Timesheets.xlsx

mohammedAlsaket_0-1727896384214.png

JA7

For your first question, the easiest way to handle this is to give those questions a default of 0. That way if your users don't answer the question, it has a numeric answer instead of a null and your summation calculation will still work.

For the second question, the general idea is the same, you want to 0's to populate your summation when the fields do not meet your specified parameters. You can do this with if statements:

if(${timeperiod1}='12-8am' or ${timperiod1}='6-12pm', ${hoursworked1}, '0')

 

What this statement is saying, is if timeperiod1 is 12-8am or 6-12pm, provide hoursworked1, otherwise provide 0. 

If you are still getting comfortable with Survey123, I recommend adding an intermediate question for each of your various if statements and then a final calculation field to sum them all together. Keep them visible in the survey while you do your testing, that way you can see if each one is firing how you expect it to and makes it easier to troubleshoot if it is not working. Once you are ready to publish you can hide those fields.