Need help with a date formula

Jennifer Henry

I have a sheet with dates listed

I need to pull back the first "Flu Vaccine Required" status if any of the dates in this sheet are on or between the start date and end date in another sheet.

I tried using:

=INDEX(COLLECT({Date Range for Flu}, {Date Range for Flu}, >=[Start Date]@row, {Date Range for Flu}, <=[End Date]@row), 1)

But it's coming back invalid data

jmyzk_cloudsmart_jp

Hi @Jennifer Henry

A straightforward approach to solving this is to create sheet references, such as {Start Date} and {End Date}.

Then, as the image below shows, you can insert the reference in the sheet summary field, such as ={Start Date} or ={End Date}.

With this, the formula for Flu Vaccine Required becomes simple as;

=IF(AND([Date for Flu]@row >= [Start Date]#, [Date for Flu]@row <= [End Date]#), "No", "Yes")