How to exclude specific date ranges when calculating a date field in Survey123 connect? (without JavaScript)

[JuanTomásMartinez17]

Hi everyone,
I'm working on a Survey123 Connect form where i have two date feilds. "Field B" should be calculated as "Field A" + 120 days. However, the calculate must exclude certain periods of the year (e.g from May 1st to August 31st) depending on the selecter work period in another question.

I know it's possible to do this with JavaScript to skip specific dates and continue the count after those periods. But JS is not an option for this particular project.

Importante note: I don't need to add extra days to compensate (120 days + amount of days the period to exclude has). I need the days between a period to be ignored in the 120 days count. They shouldn't be considered at all, almost like they don't exist in the timeline.

Is there any way to achieve this using XLSForm expressions or other built-in tools within Survey123 Connect?

Thanks in advance.

[Neal_t_k]

without a little more context it will be hard to be specific, but an if statement should work for this.  Construct a nested if statement if there are multiple conditions to exclude.

If (${fieldA}>date1 and ${fieldA}<date2, condition_1, ${fieldA} + 120)

date1 = May1

date2 = August1

if those dates are static you can use some date formatting to construct a concat calculation to account for changing years.

${year} = format-date(now(), '%Y')

concat("5/1/",${year})

not sure what condition_1 is from your description, if it is just fieldA or null or some other calculation.

Your date formatting is going to be important, you may need a helper fields to store fieldA as a number or to convert your constructed dates to a date.  The following should help you with date formatting and calculations with dates.

https://community.esri.com/t5/arcgis-survey123-blog/dates-and-time-in-survey123/ba-p/895528

[Neal_t_k]

This appears to work.  Something with the date and the concat function is formatting to the day before, not sure why that is, but that is why I have 5/2 and 8/2 instead of the first.

[JuanTomásMartinez17]

Let me provide more details:
There is a select_one question called work_period, with two options: peak_period and low_period.If peak_period is selected, workers are not allowed to work between May 1st and September 30th.

• If low_period is selected, they cannot work during June and July.

Therefore, when calculating the due date for Field B (which is computed as Field A + 120 days), the non-working periods must be taken into account based on the selected option. The calculation should skip any days that fall within the restricted timeframes.

In JavaScript (I’ve done something similar before), the logic would look something like this — perhaps this helps clarify what I mean:

function calculateExpireDate(startDate, daysToAdd, period) {
    let date = new Date(startDate);
    let daysCounted= 0;

    while (daysCounted < daysToAdd) {
        let month = date .getMonth();
        let valid = true;

        if (period === 'full_season' && month >= 5 && month <= 9) {
            valid = false;
        } else if (period === 'low_period' && (month === 6 || month === 7)) {
            valid = false;
        }

        if (valid ) {
            daysCounted++;
        }

        if (daysCounted < daysToAdd) {
            date.setDate(date.getDate() + 1);
        }
    }

    return date.toISOString().split("T")[0];

}


/** CONFIGURACION:
 * calculation: pulldata("@javascript", "calcularFechaVenc.js", "calcularFechaVenc", ${fecha_not_inscripcion}, 60, ${periodo_de_trabajo})
 * relevant: selected(${periodo_de_trabajo}, 'plena_temp') or selected(${periodo_de_trabajo}, 'semi_temp') or selected(${periodo_de_trabajo}, 'todo_el_anio')
 * **/

[Neal_t_k]

So to clarify if  "low period" is selected and FieldA = 5/31/2025, your calculation would be 5/31/2025 + 181 days, (Skipping 30days for June and 31 for July, then adding the 120 days)? if that is the case then the  calculations above should work if you modified the Date1 and Date2 to be Jan 31 to May 31st.  That is the period where if FieldA falls in if 120 days are added FieldB would fall into June and July, so if you add 181 days to those dates it should put you out side your June/July window. The other complicated part is if FieldA falls in the low period, but that is just the number of day left in the period + 120.  So you end up with a lot of helper fields and a complicated if statement.

You can try this out, you may have to fiddle with the dates, and modify for your other period, but I think it accomplishes what you want.   But that said, @ChristopherCounsell's solution may be easier.

[ChristopherCounsell · Accepted solution]

You could also precalculate the dates.

Create a CSV to use with pulldata. Have input date in one column plus the calculated date in column b.

This lets you use whatever logic you need to generate the table for the next few years, and then survey123 simply needs to pull the b value. No JavaScript or complex statements within the survey form. 

If you have a selector that changes the calculated date, you can add extra columns to the pulldata table and update the expression to get the column based on input.

[JuanTomásMartinez17]

I think i understand the approach, and it would be easy for me to generate a CSV with all possible dates in the format:
start_date, skipping_period_1, skipping_period_2
01/01/2025, 01/06/2025, 15/05/2025
02/01/2025, 02/06/2025, 16/05/2025

...and so on, using Python.

The issue I see is that I could generate a certain number of rows that would cover, for example, the next 5 years, but eventually I’d need to regenerate the file. That’s a concern because I don’t know who will be in charge of the project in the future or if there will be availability to update it.

Ideally, I’d prefer a more long-lasting solution. Still, if no other option is available, I think this is a solid workaround.

[ChristopherCounsell]

Just do 20 years, CSV will be fine with that many rows.

It's a brute force method but it'll save you headaches with nested if statements, and you can validate results for problematic dates like end of month or leap years ahead of time.