Forum Discussion

CristianBaez's avatar
CristianBaez
Copper Contributor
Feb 01, 2022

Looking for a formula to not update the cell after is past expiration date.

Hi! 

I'm creating a spreadsheet where I like to collect all the absences from an employee. Let me explain: For every absence, the employee earns two flags and each one of the flags expire 28 days after the date of the last one issued. If the employee gets another flag, the 28 days will reset for the other flags as well. I was able to create the formula for that but I want the flag dates to remain "as is" after they expired.

 


Those 2 flags above are expired, but if I add another one under it the expired ones get updated as well. Is there a way to add flags without the formula updating the ones that are expired?

=IF(OR(C8="",B8=""),"",IF(AND(E7="",C9=""),C8+28,IF(AND(C8<>"",C9<>"",B9<>"",E7=""),LOOKUP(2,1/(C8:C11<>""),C8:C11+28),E7+28)))


That is the formula I'm using to calculate the expiration date. I was thinking of adding a column named Expired? and if I manually selected yes to lock the cell from being updated but again, I don't know what formula to use. Any help or idea will be appreciated.

 

Thanks!

 

Formulas and Functions

1 Reply

  • mtarler's avatar
    mtarler
    Silver Contributor
    Feb 01, 2022
    I really tried to understand your text and formula but don't get it. What do you mean by 'getting reset' and why do they get 2 flags instead of just 1 w/ exp date of 56 days. And more off, I don't get the formula. The picture doesn't show rows or columns so I'm assuming the 2nd flag is row 8 and showing columns A:E. But then when, why, how would E7="". I must be missing something.