Last time a specific data appear

Question

Hello everyone&nbsp;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&nbsp;simplified version of my table:Date of use Type of supplement Time inbetween uses (in days) Date next use01/04/2024&nbsp; &nbsp; &nbsp;Supplement A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;08/04/202403/04/2024&nbsp; &nbsp; &nbsp;Supplement B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;04/04/202404/04/2024&nbsp; &nbsp; &nbsp;Supplement C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;30&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 04/05/202406/04/2024&nbsp; &nbsp; &nbsp;Supplement A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;13/04/202407/04/2024&nbsp; &nbsp; &nbsp;Supplement B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;08/04/202409/04/2024&nbsp; &nbsp; &nbsp;Supplement D&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;14&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 23/04/202412/04/2024&nbsp; &nbsp; &nbsp;Supplement C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;30&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12/05/2024As 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?&nbsp;A big thank you in advanceBenjamin Herremans

m_tarler · Answer

benjaminherremans&nbsp; you can use this in the conditional formatting custom rule:=$A1&lt;MAX(($A$1:$A1 + $C$1:$C1)*($B$1:$B1 = $B1)*($A$1:$A1&lt;&gt;$A1))assuming date of use is column A and the Applied to range starts in A1alternatively if you have the conditional formatting Applied to range start in A2 then:=$A2&lt;MAX(($A$1:$A2 + $C$1:$C2)*($B$1:$B2 = $B2))&nbsp;

Forum Discussion

Last time a specific data appear

1 Reply

Resources