Date checkboxes in sheet generated from a range of dates in another sheet

Beth C

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!

Paul Newcome

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)

Paul Newcome

Happy to help. 👍️

Paul Newcome

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)

Beth C

Works great! Thank you!

Paul Newcome

Happy to help. 👍️