I have a problem which may have a number of solutions. The solution I'm seeking after may not be possible, but another solution may present itself. If that's the case, then alternative solutions are certainly welcome.

The Problem
I work in a group home where we give medications on a daily basis. I'm responsible for making sure we don't run out of these medications. Every month I'm given an invoice to order more. However, not every medication fits into this cycle as neatly as it should, for whatever reason.

I also don't give medications on a daily basis, which means that I don't always get to see when they run low, and staff are notoriously bad at letting me know.

The solution, it would seem, would be to set myself an electronic reminder when a certain medication is running low. It wouldn't be terribly difficult to go through the meds as they are now and find out which ones are going to run out before the beginning of the next cycle.

The Solution
It comes in two parts. The first part is that when I do calculate the expected expiry dates, that I would have in place a notification, likely in Outlook, of when a medication is supposed to expire a week in advance of that expiration.

The second part includes Microsoft Excel. I have a LOT of reminders of things I need to do, and medications are important enough that I should have a redundant system in case I miss a reminder. As such, I'm trying to put together an excel file of the medications along with their expected expiration dates.

Doing it all manually would be easy. However, I'd love to make something that does a bit of the work for me. Specifically, once I put in the medication, how much of that medication is used on a daily basis, and how much is remaining, I'd like a formula that outputs the date I would need to order the medication. If that date comes before the next expected cycle, the medication would be highlighted and I would know to order it on the date given.

This may all sound relatively simple to someone who knows Excel well. However, Excel is a program I'm still figuring out.

How to use conditional cell highlighting is fairly simple to research through Google. However, when I did a search about countdowns, I got a lot of examples that use the Today() function. The problem with that function is that if I use it, then on any given day when I might look at the spreadsheet, the expiry date will be calculated given the medications as I saw them on a given day, but with the new date as the indicator. This will lead to a lot of problems if I'm relying on it.

The solution I would prefer is to have the "remaining" total of medication count down with each day in accordance with the value I provide in how much is used per day (which, as a side note, can be less than one. Some medications are given once a week, while other medications are given 3-4 times a week).

This is a bit more complicated and my basic Google searches have not dredged up how I might accomplish this.

Any and all help is appreciated, even on things which I can eventually find out with a bit of research (such as conditional highlighting) as I'm currently at work trying to do this order. However, as long as the main question is answered, I can find the other solutions myself.