Auto populate multiple checkboxes from 2 dates
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
-
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
-
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
-
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!
-
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.
-
That worked, Thank you!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!