I am trying to find the right formula referencing dates and an identifier column

Meesh

I am working on a formula to check all boxes in the rows that match the identifier column if there is a projected date and it is greater than or equal to today. I collect the start and end date and if a class is delayed or ahead of schedule it collects the projected end date. What I want is if the class is still going on and the end date is in the past but the projected end date is equal to today or in the future, I want all rows tied to that class based on the identifier to have a checkmark next to it so all classes submitted daily show as active due to the projected end date. Any ideas as to how i can make this work?

Paul Newcome

You will need a COUNTIFS inside of an IF. Basically you want to count all rows that meet the criteria and have the matching identifier then if that count is one or greater, check the box.

=IF(COUNTIFS([End Date]:[End Date], @cell< TODAY(), [Projected End Date]:[Projected End Date], @cell>= TODAY(), Identifier:Identifier, @cell = Identifier@row)> 0, 1)

Zachary Hall

Entering the formula below in a checkbox column and making it a column formula should work.

• =IF(AND([End Date]@row<TODAY(),[Projected End Date]@row>TODAY(-1)),1,0)

Let me know if not!

Meesh

That would work to check the boxes for those that match the date criteria but I need to it check all boxes even if the dates don't have that criteria based on the identifier that matches all entries for that class. So if the newest submitted entry for that class is submitted, and it matches all of the other rows with the same identifier that states it is for the same class as prior submissions, I want it to check all of the boxes in all rows for previously submitted entries for that class based on the date criteria matching and if the identifier column matches those rows with the most recent submission for that class.

So if the most recent class does not have a projected end date then I don't want it to check any of the boxes for that class that matches the identifier column even if previous submissions had a projected end date. I only want it to show all days submitted for each class if the most recent submission for that class shows a projected end date meaning it's either delayed or ahead of schedule.

I hope I'm not making this more complicated or confusing the way I'm trying to explain it. I'm happy to clarify more if needed. Thank you for your help :)

Paul Newcome

You will need a COUNTIFS inside of an IF. Basically you want to count all rows that meet the criteria and have the matching identifier then if that count is one or greater, check the box.

=IF(COUNTIFS([End Date]:[End Date], @cell< TODAY(), [Projected End Date]:[Projected End Date], @cell>= TODAY(), Identifier:Identifier, @cell = Identifier@row)> 0, 1)

Meesh

@Paul Newcome Thank you so much!!! That worked!! I updated the formula to count those that don't match that criteria but if the end date is still ">=today" then it will still check the box as well. Thank you for your help!! This was my final formula:

=IF(COUNTIFS([End Date]:[End Date], @cell < TODAY(), [Projected End Date]:[Projected End Date], @cell >= TODAY(), [{Helper} Identifier]:[{Helper} Identifier], @cell = [{Helper} Identifier]@row) > 0, 1,IF([End Date]@row>=Today(),1,0))

Paul Newcome

Happy to help. 👍️