Explore More at wakehealth.edu

REDCap Tip of the Month: Calculated Fields - Using the DATEDIFF Formula

REDCap calculated fields allow for real-time calculations on data entry forms and surveys. REDCap supports the following mathematical operations within a calculated field:

  • Addition
  • Subtraction
  • Multiplication
  • Division

The DATEDIFF formula, which you may recognize from Excel, calculates the difference between two dates or datetimes. This can be useful for projects wanting to calculate the length of time between a diagnosis date and a surgery date or projects wanting to calculate a participant’s age at a follow-up visit, for example.

How do I format the equation?

To add a calculated field, select the “Calculated Field” field type from within the Online Designer. In the “Calculation Equation” textbox, enter the equation for your calculated field. Complete the field label and variable name fields as you normally would.

The equation for a DATEDIFF calculated field looks like this: datediff ([date1], [date2], "units", "dateformat", returnSignedValue), where “units” can be defined as years (y), months (M), days (d), hours (h), minutes (m), or seconds (s), “dateformat” can be defined as year-month-day (ymd), month-day-year (mdy), or day-month-year (dmy), and “returnSignedValue” can be defined as signed (true) or absolute (false). The “returnSignedValue” parameter is optional and will default to “false” if not specified.

In the example below, we are calculating the participant’s age at a follow-up visit, so our formula looks like this:

When completing the data entry form, the “Age at Visit” field will automatically calculate as soon as a date of birth and visit date are entered.

You can also add a ROUND formula to your DATEDIFF equation to round the result up or down to the nearest whole number, as shown below.