Date checkboxes in sheet generated from a range of dates in another sheet
Hi!
I have a grid that is filled by a Vacation Request form. Team members can request single dates only or a range of dates.
When the request is approved, I want to check each date in the request to individual checkboxes in another sheet like the "Beth" column below.
I would also like it to see single dates, like in the "Eric" row in the first picture. I would then use these checkboxes in an Index/Match formula in each person's individual schedule.
The formula that I currently have in the "Bryan" column pulls only the Start date.
=IFERROR(INDEX(COLLECT({Vacation Approved}, {Tech Name}, "Bryan", {Vacation Start}, Date@row), 1), 0)
Any help would be appreciated!
Best Answers
-
My first suggestion would be to have users enter the end date regardless of whether it is one day or ten days. Consistency in data collection always makes reporting much much easier.
From there, your formula to check the boxes (also assuming you only want to check the boxes if the dates are approved) in the second sheet would look something like this...
=IF(COUNTIFS({Source Sheet Start Date}, @cell <= Date@row, {Source Sheet End Date}, @cell >= Date@row, {Source Sheet Approved}, @cell = 1, {Source Sheet Name}, @cell = "Bryan") > 0, 1)
-
Happy to help. 👍️
Answers
-
My first suggestion would be to have users enter the end date regardless of whether it is one day or ten days. Consistency in data collection always makes reporting much much easier.
From there, your formula to check the boxes (also assuming you only want to check the boxes if the dates are approved) in the second sheet would look something like this...
=IF(COUNTIFS({Source Sheet Start Date}, @cell <= Date@row, {Source Sheet End Date}, @cell >= Date@row, {Source Sheet Approved}, @cell = 1, {Source Sheet Name}, @cell = "Bryan") > 0, 1)
-
Works great! Thank you!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!