Auto Check Box if Date Falls in Specific Range?

britthduncan

Hello All,

I'm trying to figure out a formula to automatically check a box if a date falls between a specific range. For example, I want to check a box for Q1 if a project start date or end date falls between 1/1/2023 and 3/31/2023.

Does anyone know of a way to accomplish this?

britthduncan

For clarification, if either the Estimated Ideation / Planning Phase Start Date or Estimated Ideation / Planning Phase End Date is within the range 1/1/23 to 3/31/23, I would like the Ideation / Planning 2023 Q1 box to be automatically checked.

Any ideas?

Kelly Moore

Hey @britthduncan

Does this work for you

=IF(OR(AND([Estimated Ideation / Planning Phase Start Date]@row >= DATE(2023, 1, 1), [Estimated Ideation / Planning Phase Start Date]@row <= DATE(2023, 3, 31)), AND([Estimated Ideation / Planning Phase End Date]@row >= DATE(2023, 1, 1), [Estimated Ideation / Planning Phase End Date]@row <= DATE(2023, 3, 31))), 1)

Kelly

britthduncan

Hi Kelly,

Yes, that worked! Thank you! I did notice upon further testing 1 issue which you could maybe help me with? Let's say the start date for a phase is 5/1/23 and the end date is 12/29/23. Using this formula (with dates adjusted for each quarter), the boxes for Q2 and Q4 will be checked, but not Q3, which this phase also falls under. Do you know how I could account for this?

Thanks for your help!

Brittany

Kelly Moore

So if the Start date is in a certain quarter or the end date is within that quarter, you would like the checkbox checked. Are you always looking one year in advance? I assumed yes so I made the formula dynamic so you wouldn't have to adjust each year. I also used the MONTH function which eliminates hard coding in dates.

See if this works for you

This is for Q1

=IF(OR(AND(MONTH([Estimated Ideation / Planning Phase Start Date]@row) <= 3, YEAR([Estimated Ideation / Planning Phase Start Date]@row) = YEAR(TODAY()) + 1), AND(MONTH([Estimated Ideation / Planning Phase End Date]@row) <= 3, YEAR([Estimated Ideation / Planning Phase End Date]@row) = YEAR(TODAY()) + 1)), 1)

Q2

=IF(OR(AND(MONTH([Estimated Ideation / Planning Phase Start Date]@row) > 3, MONTH([Estimated Ideation / Planning Phase Start Date]@row) <= 6, YEAR([Estimated Ideation / Planning Phase Start Date]@row) = YEAR(TODAY()) + 1), AND(MONTH([Estimated Ideation / Planning Phase End Date]@row) > 3, MONTH([Estimated Ideation / Planning Phase Start Date]@row) <= 6, YEAR([Estimated Ideation / Planning Phase End Date]@row) = YEAR(TODAY()) + 1)), 1)

Q3

=IF(OR(AND(MONTH([Estimated Ideation / Planning Phase Start Date]@row) > 6, MONTH([Estimated Ideation / Planning Phase Start Date]@row) <= 9, YEAR([Estimated Ideation / Planning Phase Start Date]@row) = YEAR(TODAY()) + 1), AND(MONTH([Estimated Ideation / Planning Phase End Date]@row) > 6, MONTH([Estimated Ideation / Planning Phase Start Date]@row) <= 9, YEAR([Estimated Ideation / Planning Phase End Date]@row) = YEAR(TODAY()) + 1)), 1)

Q4

=IF(OR(AND(MONTH([Estimated Ideation / Planning Phase Start Date]@row) >9, YEAR([Estimated Ideation / Planning Phase Start Date]@row) = YEAR(TODAY()) + 1), AND(MONTH([Estimated Ideation / Planning Phase End Date]@row) >9, YEAR([Estimated Ideation / Planning Phase End Date]@row) = YEAR(TODAY()) + 1)), 1)

Does this work for you?

Kelly

britthduncan

Hi Kelly,

Thanks so much for this! I'm currently working on a sheet for our next budget cycle, which inludes 2023, 2024, and 2025, so I'd like to be able to automatically check boxes when a project phase falls in each quarter of each of those years. I hope that makes sense.

Thank You!

Kelly Moore

Hey

You have a couple of different approaches to make the different years easier to keep evergreen in the future.

-You can just replace the YEAR(TODAY())+1 and add in your exact year (don't put them in quotes since it is a number).

-If you have three sets of columns (Q12023,Q12024 etc), you can add the different years as a field in the summary sheet fields, then reference the appropriate field in the formula. Next year you won't have to update the code in formulas, you just go in and update the summary sheet field. I do this often if I have constants to use and/or update. A reference for a summary sheet field is FieldName# Whether to use square brackets or not follows normal column rules.

-build the different years as Year(TODAY())+1, YEAR(TODAY())+2, etc. This is the easiest way to keep it evergreen

Let me know if you need any help more executing any of these in your sheet

Kelly