Calculating the next upcoming date in planned events

edited 02/08/22 in Smartsheet Basics

Hi, I'm trying to calculate the next upcoming date that is after Today. My current equation is:

=IF([On/Off Treatment]@row = "Off Treatment", "", MAX(COLLECT([CxD1 Dosing dates (Planned)]23:[CxD1 Dosing dates (Planned)]31, [CxD1 Dosing dates (Planned)]23:[CxD1 Dosing dates (Planned)]31, @cell <= TODAY())))

The date in RED is the output of this formula. It is obviously pulling the wrong date, the max date before today, when what I really want is the next date after today (02/17/22). Any suggestions?

Thanks in advance!


Best Answer