IF formula to identify tasks for this week - missing tomorrow
Hi,
I am using the formula below as a column formula under "This Week" to check the box when the start or finish date falls within this week so I can see which tasks I need to be working on week by week. It is capturing it correctly except when I have a task that starts and ends tomorrow (one day duration). How can adjust it to capture the missing day (see line 10)?
=IF(AND(TODAY()>= Start@row, TODAY()<= Finish@row), 1)
Best Answers
-
@Nicole C Ahh ok. I see, in that case, you can use the WEEKNUMBER() function to check if the date(s) are in the same week as TODAY(). That should be a lot easier.
Darren Mullen, smartsheetguru.com
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
-
@Darren Mullen Weeknumber did help me solve it. But I had to get the formula to look at a range of weeknumbers compared to today's weeknumber. Here's what I ended up with that worked:
=IF(AND(WEEKNUMBER(TODAY()) >= [Week # Start]@row, (WEEKNUMBER(TODAY()) <= [Week # Finish]@row)), "true", "false")
I added a column for week # start and week # finish based on the task start and finish dates. Then I had the formula calculate today's week # to check it against the range. Now anytime I look at the sheet, it will tell me if a task falls within the date or date range for this week.
Answers
-
@Nicole C The issue is the Today is 9/19 which is NOT Greater than or equal to 9/20. It is more straight forward to write the formula as:
=IF(AND(Start@row >= Today(), Finish@row <= Today()), 1)
This should work.
Darren Mullen, smartsheetguru.com
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
-
@Darren Mullen unfortunately, that did not work. That formula caused it only to check the box for today's date.
-
@Nicole C I re-read your post, and you are checking the following "...to check the box when the start or finish date falls within this week..." .
So your formula requires BOTH conditions to be true, but you only care about one of the conditions, so you should then use "OR()" instead of "AND()" .
Am I understanding your requirement correctly now?
Darren Mullen, smartsheetguru.com
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
-
@Darren Mullen I guess I'm wanting the box to be checked when any of the dates within a range, or a single date, falls within the week that I am looking at my report. The 'OR' captures everything. I think I may need to change my parameters because I want to be able to look at the report any day of the week but keep the 'this week' range within the work week.
For example, I'm wanting to capture items that have either a start date, an end date or a date within the start/end range that fall within this work week. When I opened the report today (Tuesday 9/20) with my original formula, it is not capturing items that were due on Monday 9/19 or items starting tomorrow 9/21. I want anything that falls within 9/19-9/23. The tricky part, is next week, I'll want anything that falls within 9/26-9/30. So it needs to change week by week without manually having to re-enter the date range.
Hopefully that makes sense! I'm just not sure if it is possible, though.
-
@Nicole C Ahh ok. I see, in that case, you can use the WEEKNUMBER() function to check if the date(s) are in the same week as TODAY(). That should be a lot easier.
Darren Mullen, smartsheetguru.com
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
-
@Darren Mullen Weeknumber did help me solve it. But I had to get the formula to look at a range of weeknumbers compared to today's weeknumber. Here's what I ended up with that worked:
=IF(AND(WEEKNUMBER(TODAY()) >= [Week # Start]@row, (WEEKNUMBER(TODAY()) <= [Week # Finish]@row)), "true", "false")
I added a column for week # start and week # finish based on the task start and finish dates. Then I had the formula calculate today's week # to check it against the range. Now anytime I look at the sheet, it will tell me if a task falls within the date or date range for this week.
-
@Nicole C Great! Glad you were able to get it solved, thanks for the follow up.
Darren Mullen, smartsheetguru.com
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!