English

How To: Exclude dates from a date question in Survey123 Connect for ArcGIS

Summary

When creating a survey with a date question in Survey123 Connect for ArcGIS, dates such as public holidays and weekends can be excluded as these dates may be unrelated to the survey. Users may want to include only work days in the survey.

Procedure

To exclude public holidays or weekends from a date question in Survey123 Connect for ArcGIS, apply a constraint. However, before applying the constraint, the dates must be converted to a readable format using the format-date() function in decimal time. This is because Survey123 for ArcGIS processes date and time values as Epoch time (also known as Unix time) by default. Epoch time stores time as the amount of milliseconds elapsed since January 1, 1970, which is an approximate value. This time is also constantly changing every millisecond, rounding off to the nearest value.


To change the Epoch time of the date question to a decimal time, create a secondary question that references the date question. Use the format-date(${date},'%m/%d/%Y') expression to convert the date to a date format. Apply the constraint to the secondary question using the format-date() function in the constraint column.


Follow the instructions described below to achieve the desired result:

  1. Sign in to Survey123 Connect for ArcGIS and create a new survey, or open an existing .xls survey form with the Dates & Time template.
  2. In the survey form, create a date question in the name column. In this example, the date question is named date.

    Survey form with name and type columns
     
  3. Create a secondary question for the public holiday and weekend in the name column. In this example, the public holiday is named holiday_constraint and the weekend is named wkend_constraint.

    Survey form with constraints added
     
  4. For the secondary questions, in the calculation column, type in the calculation expression for the public holiday and weekend.
    In this example, the format-date(${date},'%m/%d/%Y') expression is used for the public holiday and the format-date(${date},'%a') expression is used for the weekend.
    Note:
    There are various format-date expressions that can be used for date questions. For example:
    
    format-date(${date_question},'%m/%d/%Y') = 25/12/2019
    format-date(${date_question},'%m') = 12
    format-date(${date_question},'%b %d, %Y') = Dec 25, 2019
    
    Select the desired expression.

    Format-date expression added in calculation column
     
  5. For the constraints, use the format-date() function for the public holiday and weekend in the constraint column. In this example, Christmas Day is selected as the public holiday. The format-date() function used for the public holiday is .!='12/25/2019' and for the weekend is ;.!='Sat' and .!Sun'.

    Format-date function added for public holiday and weekend in constraint column
     
The images below show the complete survey table in the .xls worksheet for the calculation column (top image) and constraint column (bottom image).

Survey table with completed calculation column

Survey table with completed constraint column
 

Related Information