Automated Symbol based on two separate date fields

Jerry Alexander

Right now I have the Priority column populating by a formula in the "Scheduled Start Date" field using this formula. =IF(ISBLANK([Scheduled Start Date]@row), 0, IFERROR(WORKDAY([Scheduled Start Date]@row - 1, [Scheduled Duration]@row), ""))

I've made a report that groups the tasks by the priority column. I also have a "Follow up" column. some tasks will just have a start date, others will need a start date and a follow up date. Is there a way to have the priority symbol change based on which ever of the two (Scheduled Start Date or Follow up) is earlier? keeping in mind the follow up date may be blank.

Screenshot 122822.PNG

Jeff Reisman

@Jerry Alexander

I'm trying to make sense of this. You are populating the RYG symbol column with this formula?

=IF(ISBLANK([Scheduled Start Date]@row), 0, IFERROR(WORKDAY([Scheduled Start Date]@row - 1, [Scheduled Duration]@row), ""))

I don't see how this could populate a RYG symbol column, or a checkbox, or a flag column. For RYG, you need to the formula result to be blank, "Red", "Yellow", or "Green". For checkbox or flag, the result needs to be blank, 1, or 0. The only thing this formula could return is a blank, a zero, or a date value. In a RYG symbol column with Scheduled Start Date and Scheduled Duration populated, this will return an #INVALID COLUMN VALUE error, while on blank rows it just returns 0. In a Date column with Scheduled Start Date and Scheduled Duration populated, this will return a date value, and will remain blank on blank rows. In a checkbox/flag column this will return a #BOOLEAN EXPECTED error on populated rows, and unchecked/unflagged on blank rows.

Getting back to the question at hand - what are your criteria for Red/Yellow/Green on the Priority?

Jerry Alexander

The criteria is if date is today or in the past, Red. If date is tomorrow, Yellow. If date is past tomorrow, Green. If date is blank, leave blank.

Right now, the formula I have works. What I'm wanting to add to this, is an additional date column to be included in the criteria.

Same formula idea, but rather just looking at [Scheduled Start Date], also look at [Follow up] with the same criteria and result.

=IF(ISBLANK([Scheduled Start Date]@row), 0, IFERROR(WORKDAY([Scheduled Start Date]@row - 1, [Scheduled Duration]@row), ""))