Calculating if a certain time falls within a range in Survey123

11-17-2021 12:26 PM
Esri Contributor
0 0 455

For some organizations, there is a need to have certain data submitted during a certain window of time. For example, let's look at a workflow for shelters serving those experiencing homelessness.

Throughout the evening, these shelters are taking a census of the number of beds occupied. This informs decisions for placing individuals throughout the evening. Only one of these censuses (taken at ~3:00AM) is counted as the "Official" census of the evening.


How can we use Survey123 to denote that a survey collected, say, between 2:45 AM and 3:15 AM is the official survey?

Let's start in Survey123 Connect. First we'll add in a question that captures the time of the census. This can be completed by the user, or auto-calculated using the now() function.



Next, we will pass this dateTime object as a variable into a calculation that determine if the hour and minute fall between 2:45 AM and 3:15 AM. If it does, I want to submit the value "Yes."

Let's look at the whole expression, then break it down piece by piece:




if((((int(format-date(${CensusDateTime}, '%H'))) + ((int(format-date(${CensusDateTime}, '%M'))) div 60)) > 2.5) and (((int(format-date(${CensusDateTime}, '%H'))) + ((int(format-date(${CensusDateTime}, '%M'))) div 60)) < 3.5),"Yes","No")





The first thing we'll look at is format-date(${CensusDateTime}, '%H'). Here, we're looking at the response for the CensusDateTime question, and simply extracting out the value of the Hour. If the time were 1:00 AM, we would see a "1," if it were 1:00 PM, we would see a "13." Then we convert this value to an integer using the int() function. 

For the minute of the submission, we do something similar, getting the value with format-date(${CensusDateTime}, '%M') and converting it to an integer with int(). However, this value we divide by 60, using div, to get the minute as a decimal.

When these values are added together, "2:30 AM" becomes a number that can be evaluated 2.5. 

Now we simply need an if() statement. If we only want to count the census as "Official" if collected between 2:30 AM and 3:30 AM, we want our calculated value to be between 2.5 and 3.5. So we use the and operator in the expression. If both conditions are true (the value is greater than 2.5 and less than 3.5, the expression returns "Yes," otherwise it returns "No." 

This expression is then placed in the calculation field of the XLSX form:



A similar variation can be placed in the relevant field to perhaps pass a note that the data being added is going to be marked as official:




(((int(format-date(${CensusDateTime}, '%H'))) + ((int(format-date(${CensusDateTime}, '%M'))) div 60)) > 2.5) and (((int(format-date(${CensusDateTime}, '%H'))) + ((int(format-date(${CensusDateTime}, '%M'))) div 60)) < 3.5)





noteOfficialCensusNoteThis record is official(((int(format-dat....


About the Author
Senior Solution Engineer at Esri | Health & Human Services