Rolling 90 Day Formula

Matthew_Lanterman

Hello,

I need to write a formula that will check a box if a date is in the next 90 days, and uncheck if the date has passed.

What I have so far is:

=IF(Start@row <= TODAY(90), 1,0)

So, if the Start date is less than or equal to 'Today' or the next 90 days, then check the box. If the Start date is not in the next 90, then leave the box unchecked.

My formula is not un-checking the box for dates in the past (past 'Today'), and is checking the box when the date is blank, which is not a desired outcome.

I tried a nested if statement to uncheck the box if the date is in the past, and blanks, but have not had success yet.

Paul H

Try

=IF(ISDATE(Start@row), IF(Start@row <= TODAY(90), IF(Start@row < TODAY(), 0, 1), 0))

Paul H

Try

=IF(ISDATE(Start@row), IF(Start@row <= TODAY(90), IF(Start@row < TODAY(), 0, 1), 0))

Matthew_Lanterman

That works! Thank you @Paul H

For my own understanding:

First IF(ISDate is checking to see if the Start@Row is a date, if yes, evaluate second statement, If not, leave box unchecked

the second statement is 'within 90 days of today' IF(Start@row <= TODAY(90),

The third IF is 'saying' - If the start date is less than today, uncheck the box, if not then check the box.

Paul H

@Matthew_Lanterman Your breakdown is correct.

Check its a date, check its within 90, check its not in the past