# Last time a specific data appear

Copper Contributor

# Last time a specific data appear

Hello everyone

I have a table where I keep track of which supplement my birds have got. In column A is the date the bird received the supplement. Column B is the type of supplement it got. Column C tells how many days there need to be inbetween two uses of this type of supplement. Column D gives the first date this supplement can be used again.

This is an simplified version of my table:

Date of use Type of supplement Time inbetween uses (in days) Date next use

01/04/2024     Supplement A                          7                             08/04/2024

03/04/2024     Supplement B                          1                             04/04/2024

04/04/2024     Supplement C                         30                            04/05/2024

06/04/2024     Supplement A                          7                             13/04/2024

07/04/2024     Supplement B                          1                             08/04/2024

09/04/2024     Supplement D                         14                            23/04/2024

12/04/2024     Supplement C                         30                            12/05/2024

As you see I used supplement A on the first of April 2024 and I need to wait 7 days before I can give it again, so I can give it again on the Eight of April 2024. But as you see, I have made a mistake and I give supplement A again on the Sixth of April 2024, which is two days too early. Now I want that the cell "06/04/2024" turns red because I use the supplement too early again. So I want to use conditional formatting in this case. I want to write a formula that excel searches the previous use of the supplement used in this line and than takes the value on the intersection of this row and the column D 'Date next use' and compare this with the 'Date of use' of the current row.

Now the problem I am having is that I can't find out how to write the formula to find 'the last use of a supplement'. Can anyone help me out please?

A big thank you in advance

Benjamin Herremans

# Re: Last time a specific data appear

@benjaminherremans  you can use this in the conditional formatting custom rule:

=\$A1<MAX((\$A\$1:\$A1 + \$C\$1:\$C1)*(\$B\$1:\$B1 = \$B1)*(\$A\$1:\$A1<>\$A1))

assuming date of use is column A and the Applied to range starts in A1

alternatively if you have the conditional formatting Applied to range start in A2 then:

=\$A2<MAX((\$A\$1:\$A2 + \$C\$1:\$C2)*(\$B\$1:\$B2 = \$B2))