This page contains the following commonly used formulas: 

  • Formula to display Null values as 'Unanswered' or 'Unknown' 
  • Formula to display Age Ranges
  • Add days to a date object or variable
  • Getting a count of non-exclusive values
  • Creating a Running Count within a section that restarts in each section
  • Return only the date from time/date field
  • To capture a user prompt for the Active, Enrolled or Dismissed filters
  • Convert minutes to numbers
  • To set the Age of a participant to match the Ending Date range of the prompt
  • To identify an Age in months for children under one year of age
  • Returns a participant’s age based on today’s date
  • Return the date of participant’s 18th birthday  
  • Flag a participant who has more than one record of a tier 2 form

Formula to display Null values as 'Unanswered' or 'Unknown'

=If IsNull([Data Object]) Then "Unanswered" Else [Data Object]

Example to show a value of 'unanswered' for participants with an empty value for Race while showing the responses values for those who have data in this field the formula will read as follows: 

 =If IsNull([Race]) Then "Unanswered" Else [Race]

Formula to display Age Ranges

=If [Age] <10 Then "1-9" ElseIf [Age]<20 Then "10-19" ElseIf [Age]<30 Then "20-29" ElseIf [Age]<40 Then "30-39" ElseIf [Age]<50 Then "40-49" ElseIf [Age]<60 Then "50-59" ElseIf IsNull([Age]) Then "No Age Listed" Else "Over 60"

To change the age ranges, adjust the numbers next to the less than operators and within the quotes accordingly.

For example: To change the third age range in the above formula from a range of "20- 29" to a "20-25" age range; that section should read as follows: <26 Then "20-25" ElseIf [Age]<36 Then "26-35" etc.

Add days to a date object or variable:

=FormatDate(RelativeDate([Begin Date];7);"MM/dd/yyyy")

[Begin Date] can be any date object or variable. This adds 7 days to the [Begin Date].

Getting a count of values for multi-select fields:

=Sum(If(Match([Object Value]; "*Lunch*")) Then 1 Else 0)

Creating a Running Count within a section that Restarts in Each Section

=RunningCount([object to count];([object being sectioned]))

Return only the date from time/date field:

= ToDate(Left(UserResponse("Begin Date");Pos(UserResponse("Begin Date";(" "-1);"MM/dd/yyyy")

Convert minutes to numbers:

=FormatNumber(Floor([Number]/60);"00")+":"+FormatNumber(Mod([Number];60);"00")

[Number] is any number object that you set up either as a variable or from universe objects.

Common Age/Birthdate Formulas:

Basic Formula for Currect Age:

=DatesBetween([Date of Birth];CurrentDate();YearPeriod)

  • You'll do a DatesBetween calculation to find the time between your date element (in this case Date of Birth) and the Current Date.  The final piece (YearPeriod) signifies the data type you'd like.
  • For more on the DatesBetween formula, click here.

To set the Age of a participant to match the Ending Date range of the prompt:

=Floor(DaysBetween([DOB];[End Date])/365.25)

[End Date] can be exchanged with any date in your report.

To identify an Age in months for children under one year of age:

=FormatNumber(Sum(DaysBetween([DOB detail];[End Date])*12)/365.25;"#,##0.0")

Returns a participant’s age based on today’s date:

=Floor(DaysBetween([DOB];CurrentDate())/365.25)

"CurrentDate" function can be replaced with another date if you need to know a participant’s age as of a specific date.

Return the date of participant’s 18th birthday:

=MonthNumberOfYear([DOB]) + "/" + DayNumberOfMonth([DOB]) + "/" + FormatNumber(Year([DOB])+18;"00")

Flag a Participant who has more than one record of a tier 2 form:

=(If count([Record ID])>1 then 1)

where "xx" in [Record ID] is the unique ID of the record. This flag will return a "1" in a column if a participant has more than one record

Did this answer your question?