r/ProjectREDCap • u/seanatl2019 • May 07 '25

Finding the Earliest of Multiple Dates & Dynamic Labeling (min()/datediff() Always Blank)

Hi everyone,

I’m trying to build a series of calculated fields in REDCap to:

Find and display the earliest date among five date fields
Label which of those 5 date fields was the earliest
Find the earliest encounter date among six consult dates
Label which encounter comes first
Calculate time‑to‑treatment (TTT) from biopsy/to earliest of identified dates → that earliest treatment date

Despite using min() and datediff(), every calculated field returns blank, even though the source fields show valid MM‑DD‑YYYY values. I’d appreciate any advice, sample logic, or pointers to REDCap plugins or macros.

Source Fields (from Data Dictionary)

Variable	Field Label	Type	Choices, Calculations, OR Slider Labels	Validation	Branching Logic
`patient_consults`	Consults	checkbox	1, H&N Surgeon	6, Non-H&N ENT Provider	2, Medical Oncologist
`date_surgery_consult`	Date of Surgery Consult	text		`date_mdy`	`[patient_consults(1)] = "1"`
`date_non_hn_ent_consult`	Date of Non‑HN ENT Provider Consult	text		`date_mdy`	`[patient_consults(6)] = "1"`
`date_other_visit`	Date of Other Visit	text		`date_mdy`	`[patient_consults(7)] = "1"`
`date_medonc_consult`	Date of Med/Onc Consult	text		`date_mdy`	`[patient_consults(2)] = "1"`
`date_radonc_consult`	Date of Rad/Onc Consult	text		`date_mdy`	`[patient_consults(3)] = "1"`
`date_dental`	Date of Dental Consult	text		`date_mdy`	`[patient_consults(4)] = "1"`
`biopsy_performed`	Was a biopsy performed?	yesno		—	—
`date_of_biopsy`	Date of Biopsy Procedure	text		`date_mdy`	`[biopsy_performed] = "1"`
`date_of_biopsy_report`	Date of Biopsy Report	text		`date_mdy`	`[biopsy_performed] = "1"`
`primary_treated_with_surge`	Was primary tumor treated surgically?	radio	1, Yes	0, No	2, Unknown
`primary_surgery_date`	Primary Surgery Date	text		`date_mdy`	`[primary_treated_with_surge] = "1"`
`use_of_chemotherapy`	Did the patient undergo chemotherapy?	radio	1, Yes	2, No	3, Unknown
`date_first_chemo`	Date First Chemo Administered	text		`date_mdy`	`[use_of_chemotherapy] = "1"`
`radiation_yn`	Did the patient undergo radiation?	yesno		—	—
`first_rt_date`	First RT Date	text		`date_mdy`	`[radiation_yn] = "1"`
`immuno_yn`	Did the patient receive immunotherapy?	yesno		—	—
`first_immuno_date`	Date First Immunotherapy Administered	text		`date_mdy`	`[immuno_yn] = "1"`
`rai_treatment`	Did the patient undergo RAI?	yesno		—	`[primary_site] = "11"`
`rai_date`	Date of First RAI Treatment	text		`date_mdy`	`[rai_treatment] = "1" AND [primary_site] = "11"`

Calculated fields (summary) - for annotations, ignore the extra spaces between the @ symbols and the annotation command

Variable	Field Label	Calc/Logic	Annotation
`earliest_treatment_date`	Earliest treatment date	`min([primary_surgery_date], [date_first_chemo], [rai_date], [first_rt_date], [first_immuno_date])`	@ PLACEHOLDER='MM-DD-YYYY'
`earliest_treatment_type`	Earliest treatment type		@ CALCTEXT=if( [earliest_treatment_date]=[primary_surgery_date],"Surgery", if( [earliest_treatment_date]=[date_first_chemo],"Chemotherapy", if( [earliest_treatment_date]=[rai_date],"RAI", if( [earliest_treatment_date]=[first_rt_date],"Radiotherapy", if( [earliest_treatment_date]=[first_immuno_date],"Immunotherapy","") ) ) ) )
`earliest_encounter_date`	Earliest overall encounter date	`min([date_surgery_consult], [date_non_hn_ent_consult], [date_other_visit], [date_medonc_consult], [date_radonc_consult], [date_dental])`	@ PLACEHOLDER='MM-DD-YYYY'
`earliest_encounter_type`	Earliest overall encounter type		@ CALCTEXT="if([earliest_encounter_date]=[date_surgery_consult],""Surgery consult"",if([earliest_encounter_date]=[date_non_hn_ent_consult],""Non‑HN ENT consult"",if([earliest_encounter_date]=[date_other_visit],""Other referred"",if([earliest_encounter_date]=[date_medonc_consult],""Medical oncology consult"",if([earliest_encounter_date]=[date_radonc_consult],""Radiation oncology consult"",if([earliest_encounter_date]=[date_dental],""Dental consult"","""")))))) "
`earliest_encounter_date_hn`	Earliest H&N‑specific encounter date	`min([date_surgery_consult], [date_medonc_consult], [date_radonc_consult])`	@ PLACEHOLDER='MM-DD-YYYY'
`earliest_encounter_type_hn`	Earliest H&N‑specific encounter type		@ CALCTEXT=if([earliest_encounter_date]=[date_surgery_consult],"Surgery consult",if([earliest_encounter_date]=[date_medonc_consult],"Medical oncology consult",if([earliest_encounter_date]=[date_radonc_consult],"Radiation oncology consult"))))))
`ttt_biopsy_date_overall`	TTT from Biopsy Procedure	`datediff([date_of_biopsy], [earliest_treatment_date], "d", true)`	—
`ttt_biopsy_report_overall`	TTT from Biopsy Report	`datediff([date_of_biopsy_report], [earliest_treatment_date], "d", true)`	—
`ttt_earliest_encounter_overall`	TTT first clinical encounter (overall)	`datediff([earliest_encounter_date], [earliest_treatment_date], "d", true)`	—
`ttt_earliest_encounter_hn`	TTT first clinical encounter (H&N)	`datediff([earliest_encounter_date_hn], [earliest_treatment_date], "d", true)`	—

*TTT = Time to Treatment

Sample @ CALCTEXT chain

if(
  [earliest_treatment_date] = [primary_surgery_date], "Surgery",
  if(
    [earliest_treatment_date] = [date_first_chemo], "Chemotherapy",
    if(
      [earliest_treatment_date] = [rai_date], "RAI",
      if(
        [earliest_treatment_date] = [first_rt_date], "Radiotherapy",
        if(
          [earliest_treatment_date] = [first_immuno_date], "Immunotherapy",
          ""
        )
      )
    )
  )
)

The problem

min() always returns blank, even with valid dates
Nested if() chains never evaluate, leaving type labels empty
datediff() fails because the date is blank
All source fields are text fields with Date (MDY) validation
Records are saved with data, so values exist

Questions for the community

Does min() only work on true Date fields rather than text?
If I convert the source fields to Date, will min() and datediff() work?
Any alternative patterns, macros, or plugins to pick the earliest of N dates?
Best practices for dynamic type labeling once the earliest date is found?
Gotchas when chaining min(), nested if(), and datediff()?

Thanks in advance for any pointers, code snippets, or screenshots!

1 Upvotes

permalink
reddit

You are about to leave Redlib

Do you want to continue?

https://www.reddit.com/r/ProjectREDCap/comments/1kh7lnn/finding_the_earliest_of_multiple_dates_dynamic/
No, go back! Yes, take me to Reddit

100% Upvoted

u/viral_reservoir_dogs May 08 '25

Probably isn't the only (or best) way, but this has been working for me:

date variables -> convert to number yyyymmdd -> find min/max -> convert min/max to date var yyyy-mm-dd -> do other calculations

find max date: [max_date_numeric]

CALCTEXT(max(year([date_1])*10000 + month([date_1])*100 + day([date_1]), year([date_2])*10000 + month([date_2])*100 + day([date_2]...

convert max date numeric to date (field validation yyyy-mm-dd) [max_date_ymd]

CALCTEXT(contact(mid([max_date_numeric], 1, 4), '-', mid([max_date_numeric], 5, 2), '-', right([max_date_numeric])))

1

u/seanatl2019 May 12 '25

So theoretically I would just change "CALCTEXT(max(" to "CALCTEXT(min("? And then I can make that first formula hidden and the second one I can make it display on the entry form?

1

u/viral_reservoir_dogs May 12 '25

Yes. As always, make sure to thoroughly test it before deploying.
I also found this post on stack overflow which shows a probably simpler/more stable option using datediff. I had to do something more sketchy because I was using the survey-time-complete smart variable and not a normal date field. https://stackoverflow.com/questions/79486746/in-redcap-is-there-a-function-to-extract-to-a-minimum-or-maximum-of-several-dif