Check a Box if Between Dates on Another Sheet
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!
Best Answer
-
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.
Answers
-
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. -
Amazing! Thank you so much!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 463 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!