IF formula to identify tasks for this week - missing tomorrow

Nicole C

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)

Darren Mullen

@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.

https://help.smartsheet.com/function/weeknumber

Nicole C

@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.

Darren Mullen

@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.

Nicole C

@Darren Mullen unfortunately, that did not work. That formula caused it only to check the box for today's date.

Darren Mullen

@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?

Nicole C

@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.

Darren Mullen

@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.

https://help.smartsheet.com/function/weeknumber

Nicole C

@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.

Darren Mullen

@Nicole C Great! Glad you were able to get it solved, thanks for the follow up.