Check a Box if Between Dates on Another Sheet

ElizabethOwen

Hey all,

I am trying to build a formula to check off a box on my sheet if at least one date is found between two dates on a separate sheet.
I've tried a million variations but here's what I have so far, thinking I was overcomplicating things

=COUNTIFS({Approve Date}, >=[Week Starts]@row, {Approve Date}, <=[Week Ends]@row, 1, 0)

So if the Approve Date on my other sheet is between the Week Starts and Week Ends dates on this sheet, I want it to check the box. The purpose is to act as a reconciliation grid so that we can confirm we didn't miss a week on our master sheet.

Thank you!

ChristianFinke

To check a box if at least one date from another sheet's Approve Date column falls between the Week Starts and Week Ends dates in your current sheet, use this formula:
=IF(COUNTIFS({Approve Date}, >=[Week Starts]@row, {Approve Date}, <=[Week Ends]@row) > 0, 1, 0)

This formula uses COUNTIFS to count dates that fall within the range defined by Week Starts and Week Ends for each row. If the count is greater than 0, the box is checked (1); otherwise, it remains unchecked (0). Replace {Approve Date} with the correct column reference from your other sheet.

ChristianFinke

To check a box if at least one date from another sheet's Approve Date column falls between the Week Starts and Week Ends dates in your current sheet, use this formula:
=IF(COUNTIFS({Approve Date}, >=[Week Starts]@row, {Approve Date}, <=[Week Ends]@row) > 0, 1, 0)

This formula uses COUNTIFS to count dates that fall within the range defined by Week Starts and Week Ends for each row. If the count is greater than 0, the box is checked (1); otherwise, it remains unchecked (0). Replace {Approve Date} with the correct column reference from your other sheet.

ElizabethOwen

Amazing! Thank you so much!