Find changes in worksheet caused by formulas based on date values

[SpencerRichman]

I have a worksheet with a column called "Hire Date".
I have another column called "90-Day Pay Raise", which has a formula that simply adds 90 to the Hire Date, and is blank if the current date is less than 90 days and displays "Pay Raise Due" if => 90 days since the Hire date.
However, the worksheet has so many rows (employees) that it's not easily apparent which rows that show "Pay Raise Due" are NEW since the last time I opened the Excel workbook.
I have added a field at the top - showing "date this workbook was last opened".
I usually visit this workbook once or twice a week.
But I can't figure out how to have Excel tell me automatically which employee rows have passed that 90-day mark since the last time I opened the workbook.
Any suggestions would be appreciated!

 

[Skybluekid]

Two things,
Firstly, what version of Excel are you using, and could you update your account to show this, so we know
Secondly, are you able to post some data?

 

[SpencerRichman]

Two things,
Firstly, what version of Excel are you using, and could you update your account to show this, so we know
Secondly, are you able to post some data?

Yes, thanks. I updated my profile. I am using Office 2021 Windows desktop version. Here is a snapshot of the worksheet:

I have this formula in column F which makes it display "Pay Raise" once they are a full 3 months past their hire date: =IF((D3+60)<TODAY(),IF(C3="Floor Staff","Pay Raise",""),"")

But my problem is that when I open the worksheet, I have no way to tell which cells in column F are NEW since the last time I opened the workbook.
As you can see in Row 1 I added the Date this Worksheet was Last Opened and Today's Date, but I don't know if or how I can use that information to have Excel tell me which cells in column F are new Pay Raise notices.
Thanks.

 

[Skybluekid]

I assume that you marki it in some way to say the "Pay Rise" has been dealt with? Looking at this and to keep it as simple as possible, I would make use of Column G as a marker column.

So the fourmula could be

=IF(AND(D3+60<TODAY(),C3="Floor Staff",G3=""),"Pay Raise","")

I have put the AND to save the need for a nested IF.

 

[SpencerRichman]

I assume that you marki it in some way to say the "Pay Rise" has been dealt with? Looking at this and to keep it as simple as possible, I would make use of Column G as a marker column.

So the fourmula could be

=IF(AND(D3+60<TODAY(),C3="Floor Staff",G3=""),"Pay Raise","")

I have put the AND to save the need for a nested IF.

Thank you Skybluekid, but I already have a similar formula that works to fill in column F with "Pay Raise". But when I open the worksheet there are already a whole bunch of "Pay Raises" filled in on column F. What I need is some way for Excel to tell me if there are any NEW "Pay Raises" filled in on column F since the last time I opened Excel so that I'll know if I have to change anyone's pay rate who I haven't already changed before.

 

[Skybluekid]

I understand what you are saying. When a Pay Raise flag appears, which is triggered by the date and whether it is floor staff, do you mark that row to say that the Pay Raise has been completed? As by looking at the picture, there is an 'x' in column G.
So with that in mind, the formula that I have proposed uses that x in Column G to turn off the Pay Raise in F. So, when you open the work book, the cell value will be blank as the last condiotion will be false, ie G3 will see an 'x', and only new vaule will so Pay Rise.

 

[SpencerRichman]

Ah, now I get it. Thank you!!