Auto populate multiple checkboxes from 2 dates

Nicholas Raymond

Hello!

Smartsheet 2.PNG

I am trying to auto populate multiple checkboxes from the anticipated start date and end date based on the fiscal years. For example the 10/5/21 to 4/30/22 would have Q2, Q3, and Q4 check marked. Is there some formula that would allow me to be able to automatically checkmark every quarter that is between those 2 dates.

Thank you!

Paul Newcome

I don't know exactly what your dates are for the quarters, so I will use Jan 1 - Mar 30 as Q1 in my example. You will need to change the dates to match your quarters.

=IF(AND([Anticipated Start Date]@row <= DATE(2021, 03, 30), [Anticipated end Date]@row >= DATE(2021, 01, 01)), 1)

The idea is if the [Anticipated Start Date] is less than the end of the quarter and the [Anticipated End Date] is greater than the start of the quarter.

Austin Marsland

Use a workflow such as, when rows are added or changed, edit cell (with condition being that the date falls between that quarters dates). You have have to do 4 individual workflows for this since "has any of" and "has all of" operators might screw up which boxes get check

Nicholas Raymond

That was my other option was using a workflow but when I created each of the 4 workflows from: when rows are added or changed, edit cell with the conditions of Anticipated Start Date is between 4/1/22 and 6/30/22 or where Anticipated End Date is between 4/1/22 and 6/30/22 based on the quarter it only checkmarked it when it fell between those dates and not it it included it previously. As an example, if the start date was 7/8/21 and the end date was 4/6/22, it only checkmarked Q1 and Q4 and not Q1 through Q4 as I need. Do you have any suggestions or answers for a workflow for that?

Thank you!

Paul Newcome

I don't know exactly what your dates are for the quarters, so I will use Jan 1 - Mar 30 as Q1 in my example. You will need to change the dates to match your quarters.

=IF(AND([Anticipated Start Date]@row <= DATE(2021, 03, 30), [Anticipated end Date]@row >= DATE(2021, 01, 01)), 1)

The idea is if the [Anticipated Start Date] is less than the end of the quarter and the [Anticipated End Date] is greater than the start of the quarter.

Nicholas Raymond

That worked, Thank you!

Paul Newcome

Happy to help. 👍️