Formula to check box if Date is 7 days ago

Andrea Westrich

Hello all,

I have an idea to help me initiate an update request to add a new row. I'm thinking about adding a helper column with a checkbox and a column formula to check the box if the Date in the previous row is 7 days earlier than today.

Do you think that will work and if so, can you help me with the formula? Mine is not working.

I've attached a screenshot of columns and my formula which would be placed in row 2 to look at the date in row 1: =If([Date]1, TODAY(-7), 1, 0) This formula does not work... Invalid Data Type.

What is the proper formula and IMPORTANTLY, will it be possible to make it a column formula where each row will look at the date in the previous row?

Thanks

Screenshot 2021-06-25 054718.jpg

Paul Newcome

Once you save the sheet, the auto-number column should update. It doesn't really matter what number it starts at as long as there is something in every row. It won't replicate data within the column, so it provides a unique data point on every row which we use the MATCH function to "search for" and output the row number.

That second formula (the IFERROR) gets nested into the IF formula. It doesn't go in its own column. I got a little ahead of myself and clicked on the post button too early before I was done typing out my solution. The IF formula goes into the checkbox column.

Paul Newcome

It looks like it is because those cells are blank. A blank is considered "less than" a non-blank. Even though the overall formula is outputting a check (or not) based on the criteria, the condition within the formula is registering a date for comparison. That means a blank is definitely going to be less than TODAY(-7).

To leave the checkbox blank until a date is entered, we can modify the existing formula like so:

=IF(IFERROR(INDEX(Date:Date, Row@row - 1), TODAY()) <> "", IF(IFERROR(INDEX(Date:Date, Row@row - 1), TODAY()) < TODAY(-7), 1))

Basically we are adding an IF statement to the very beginning that says if the result of the INDEX function is not blank, then run the comparison. By default this means that if the result of the INDEX function is blank, then it will not run the comparison and will not check the box.

Andrea Westrich

@Paul Newcome, brilliant!! Works like a charm!

Thank you so much for your help!!

Paul Newcome

Grr... Ok. What about this one?

=IFERROR(IF(INDEX(Date:Date, Row@row - 1) < TODAY(-7), 1), "")

Paul Newcome

To be able to make it a column formula, we have to be able to use @row references instead of specific row references. To do this we need to add 2 columns.

We need a system generated auto-number column with no special formatting that we will call "Auto" and then a text/number column called "Row" with this column formula:

=MATCH(Auto@row, Auto:Auto, 0)

Now that we have the row numbers established, we can use an INDEX function to pull data from the date column and use "Row@row - 1" to determine which row number to index from.

The only catch is... What do you want to do about Row 1? Since there is no previous row it will throw an error. The formula below will leave it blank instead.

=IFERROR(INDEX(Date:Date, Row@row - 1), "")

Paul Newcome

Oops. Sorry. Hit the post button early. We actually need to adjust the output of the IFERROR, so as to not check the box on row 1 by outputting today's date.

=IFERROR(INDEX(Date:Date, Row@row - 1), TODAY())

Now we can drop this into an IF statement to say that if it is less than TODAY(-7), check the box.

=IF(IFERROR(INDEX(Date:Date, Row@row - 1), TODAY()) < TODAY(-7), 1)

Andrea Westrich

Thank you @Paul Newcome. I set up the Auto column and tried to set it as auto numbering starting with 1, but nothing appears.

The formula for the Row column, =MATCH(Auto@row, Auto:Auto, 0), seems to work for the first row without the auto column.

What column should this formula be in? =IFERROR(INDEX(Date:Date, Row@row - 1), TODAY())

Paul Newcome

Once you save the sheet, the auto-number column should update. It doesn't really matter what number it starts at as long as there is something in every row. It won't replicate data within the column, so it provides a unique data point on every row which we use the MATCH function to "search for" and output the row number.

That second formula (the IFERROR) gets nested into the IF formula. It doesn't go in its own column. I got a little ahead of myself and clicked on the post button too early before I was done typing out my solution. The IF formula goes into the checkbox column.

Andrea Westrich

@Paul Newcome, many thanks. I had to change the ) < TODAY(-7) to = TODAY(-7), otherwise every checkbox checked. Could that mean the @row -1 was not working correctly?

I see what you were doing with the < and that would be best in case the entries are not done on the correct day, but if I make the formula with < Today(-7), every checkbox checks. Or am I doing something wrong? See screenshot.

Paul Newcome

It looks like it is because those cells are blank. A blank is considered "less than" a non-blank. Even though the overall formula is outputting a check (or not) based on the criteria, the condition within the formula is registering a date for comparison. That means a blank is definitely going to be less than TODAY(-7).

To leave the checkbox blank until a date is entered, we can modify the existing formula like so:

=IF(IFERROR(INDEX(Date:Date, Row@row - 1), TODAY()) <> "", IF(IFERROR(INDEX(Date:Date, Row@row - 1), TODAY()) < TODAY(-7), 1))

Basically we are adding an IF statement to the very beginning that says if the result of the INDEX function is not blank, then run the comparison. By default this means that if the result of the INDEX function is blank, then it will not run the comparison and will not check the box.

Andrea Westrich

@Paul Newcome, brilliant!! Works like a charm!

Thank you so much for your help!!

Paul Newcome

Happy to help. 👍️

Andrea Westrich

@Paul Newcome, I want to fully understand the solution you so graciously provided and I have a couple of questions.

Why do I need the Row column, could I not run the index off the Auto column?

Now to fully understand the formula: =IF(IFERROR(INDEX(Date:Date, Row@row - 1), TODAY()) <> "", 

If I understand correctly, this part is saying, if the date in the Date column one row up is blank, do nothing, correct?

Can you explain what this portion is saying? Why do we need an IFerror and what is it doing? What is the purpose of 2 Today functions? IF(IFERROR(INDEX(Date:Date, Row@row - 1), TODAY()) < TODAY(-7), 1))

I appreciate your help to learn and fully understand.

Paul Newcome

You COULD run the index off of the auto column, but that will break if any rows are removed or added to the middle of the sheet or if any sorting is done.

Using the Row column, row 1 will always have the value of 1, row 2 will always have the value of 2, so on and so forth.

Now to break down the formula... The first bit would best be answered as both yes and no. Technically it is saying that if the date in the row above is not blank, then run the calculation. So by default if it is blank then it won't run.

The second portion we can break down into two parts. Bold and not bold.

IF(IFERROR(INDEX(Date:Date, Row@row - 1), TODAY()) < TODAY(-7), 1))

The bold portion can also be broken down into two more parts.

IFERROR(INDEX(Date:Date, Row@row - 1), TODAY())

If we leave the INDEX function on it's own, then when it is in row 1 the "Row@row - 1" will output a zero. Since there is no zero it will throw an error.

That's where the IFERROR function comes in. Basically, if there is an error with the INDEX portion, then it will output today's date.

The reason I chose today's date is because the non-bold portion of the IF statement is saying that if the output is less than today - 7. Today's date is greater than today - 7, so by outputting today's date for the formula on row 1 when we get an error on the INDEX function, it won't check the box.

So basically the IFERROR(........................, TODAY()) portion is simply to make it so that it can be applied as a column formula without throwing an error or checking the box on row 1.

Does all of that make sense? I feel like I kind of rambled a little bit.

Andrea Westrich

@Paul Newcome that is all very helpful and you did not ramble. However, I do still get an error on row 1 of #Invalid Operation. It's not super important to me, but if that is the reason behind the IFERROR, there is something that needs to be tweeked. Any ideas?

Paul Newcome

Oh shoot. I'm sorry. I didn't think it all the way through. I'm sorry. The reason for that is because of the first bit that says if the INDEX function is not blank then run the formula. That first INDEX function will never return a blank because the IFERROR is outputting today's date. Let's change the output for the first IFERROR to blank (bold).

=IF(IFERROR(INDEX(Date:Date, Row@row - 1), "") <> "", IF(IFERROR(INDEX(Date:Date, Row@row - 1), TODAY()) < TODAY(-7), 1))

Did that work for you?

Andrea Westrich

@Paul Newcome unfortuantely, I'm still getting invalidoperation on row 1. See attached.

I'm using this formulat, which works on subsequent rows.

Screenshot 2021-06-29 092436.jpg

=IF(IFERROR(INDEX(Date:Date, Row@row - 1), TODAY()) <> "", IF(IFERROR(INDEX(Date:Date, Row@row - 1), TODAY()) < TODAY(-6), 1))

Paul Newcome

Did you try that last one I posted where we replaced the first TODAY() reference with a blank?

=IF(IFERROR(INDEX(Date:Date, Row@row - 1), "") <> "", IF(IFERROR(INDEX(Date:Date, Row@row - 1), TODAY()) < TODAY(-7), 1))

Andrea Westrich

I did. It works for all other rows, but row one still has #invalid operation.