Using TODAY()

[Sharon_Dquest1]

In my lab we document the expiration date of items every day we use them in excel. I want it to highlight when an item is expired so that the item is no longer used. If today is 1/4/21 and an item expires today, we can still use it today, but I want it to highlight tomorrow if it's still being used. What I don't want to do it is open the sheet back up tomorrow and have the cells highlight for previous days entered. For example, I opened up the sheet today (1/4/21) and even though I'm using a new item (the item used the past few days expired 1/3/21), all of the previous days are highlighted even though it wasn't expired when it was used. I used the formula <TODAY() in each (ex=$K12:$K394<TODAY() ). I also can't just do Cell1<Cell2, since the day the result is entered if usually one of the last columns and I worry someone will miss it turn red since they're already towards the end of the row when it will turn red.  Any ideas?

[#2]

@Sharon_Dquest1 Can you provide a sample workbook with no sensitive data to review? My best suggestion is to create a new static column that tracks the "Date Last Used" so prevent the TODAY function from incorrectly flagging expired items

[#3]

@adversi 

Here is a sample sheet. Thanks!

[#4]

@Sharon_Dquest1 

Can you post a sample sheet that isn't totally blank? 

@adversi asked for "no sensitive data," but (taking the risk of reading minds here) I doubt it meant "no data at all."  Just omit proprietary data, but give us something to work with that represents the kind of data, some of which is equipment that has expired, some of which hasn't yet, etc..... that would enable us to test functions, conditional formats, and so forth. If you could include the formulas you have developed, even though you're not satisfied, it still helps us help you.

[#5]

@adversi 

You will find it attached. And if I had any common sense yesterday I wouldn't have removed everything. Thanks!

[#6]

@Sharon_Dquest1 

I may be missing it, but this new one still is basically blank, isn't it? Here's a screen grab... Data: Please show some representative data, as asked before, in rows 4-6 at least, something to work with that represents the kind of data, some of which is equipment that has expired, some of which hasn't yet, etc..... that would enable us to test functions, conditional formats, and so forth. If you could include the formulas you have developed, even though you're not satisfied, it still helps us help you.

I see the formula in Column I, but no other.... but to see how you store the dates, since that's what you've asked about...etc.