Auto populate multiple checkboxes from 2 dates

Options

Hello!


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!

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    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.

Answers

  • Austin Marsland
    Options

    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
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    Options

    That worked, Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!