Within the next 7 day issues

Ashalee

Good Evening all,

I am trying to do something simple. I have a "Due Date" column. Whenever that date is within the next 7 days I would like it to check a box.

However, the issues I am getting are - it is checking the boxes for due dates have no date populated as well as dates that are in the past??

Here is my current formula where I have finally gotten the blank dates to stop checking the boxes, but now I am stuck with a due date of 1/10/22 that is checking the box..

=IF(ISBLANK([Due Date]@row), "", IF([Due Date]@row <= TODAY() + 7, 1))

Someone please help me figure out where I went wrong?

Thanks!!

Genevieve P.

Hi @Ashalee

You did the right thing with your first IF statement! Blank date cells are seen as "in the past".

For your second statement, you just need to add in where the range should start from. For example, right now you're saying that the date just has to be less than or equal to 7 days from now, so that's the end date of the timeframe, but you don't say that the count should start from today. This means that any date in the past will check the box.

Try adding in a statement that says greater than or equal to Today, like so:

=IF(ISBLANK([Due Date]@row), "", IF(AND([Due Date]@row <= TODAY() + 7, [Due Date]@row >= TODAY()), 1))

Cheers!

Genevieve

Genevieve P.

Hi @Ashalee

You did the right thing with your first IF statement! Blank date cells are seen as "in the past".

For your second statement, you just need to add in where the range should start from. For example, right now you're saying that the date just has to be less than or equal to 7 days from now, so that's the end date of the timeframe, but you don't say that the count should start from today. This means that any date in the past will check the box.

Try adding in a statement that says greater than or equal to Today, like so:

=IF(ISBLANK([Due Date]@row), "", IF(AND([Due Date]@row <= TODAY() + 7, [Due Date]@row >= TODAY()), 1))

Cheers!

Genevieve

Heather Duff

Hi @Ashalee ,

We'll want to rule out dates that are in the past:

=IF(ISBLANK([Due Date]@row), "", IF(AND([Due Date]@row >=TODAY(),[Due Date]@row <= TODAY(7), 1)))

Also - I moved the 7 to inside the TODAY() parenthesis. You can use those parenthesis to calculate how many days in the future (positive number) or past (negative number) from today you want to count.

Hope this helps! Let me know if it works.

Best,

Heather

Ashalee

@Genevieve P.

You are amazing. Thank you so very much!

Genevieve P.

No problem! 🙂