Pharmacy formulas for tracking patients.

**jgross29** · 08-14-2012, 01:12 PM

i am trying to create a formula to track prescriptions at my pharmacy. For example i want to know if someone received 30qty in pills on "X" date that todays date is 27 days since the script has been filled only if the field called "refills" has a value >=1.

Another example would be the same thing but if the qty=15 i want to know that today is 13 days since the patient received his prescriptions only if the field called "refills" has a value >=1.

If anyone can help me out it would be greatly appreciated.

**NickyC** · 08-14-2012, 09:30 PM

Hi
if your date is in cell A2 and refill in B2, try
=IF(B2>0,TODAY()-A2,"")

if quantity is in cell C2 then try:
=IF(AND(B2>0,C2=15),TODAY()-A2,"")

**jgross29** · 08-15-2012, 12:01 PM

thank you very much. i tried what you said but cannot seem to get it to work. i am attaching the spreadsheet here so you can see. your formula was entered in column N. two rows filled in are both examples. qty 30 and qty 15. anything you can do to help would be greatly appreciated.

**NickyC** · 08-15-2012, 09:51 PM

Hi
Sorry, I'm not sure what you are trying to achieve.

you asked for:

"Another example would be the same thing but if the qty=15 i want to know that today is 13 days since the patient received his prescriptions only if the field called "refills" has a value >=1. "

The formula tells you that for the patient who took quantity 15 (cell I3) with 2 refils (cell J3) on date 14/8/12 (cell H3) it is two days since the prescription was filled.

What did you want the formula to return?

**eugbed** · 08-15-2012, 11:29 PM

Are you trying to flag when the script is almost ready for renewal? ie. a couple of days before they run out? (and only if they are supposed to have a renewal...that's why the refill value needs to be > 1)

**eugbed** · 08-15-2012, 11:49 PM

Are you trying to flag when the script is almost ready for renewal? ie. a couple of days before they run out? (and only if they are supposed to have a renewal...that's why the refill value needs to be > 1)

**eugbed** · 08-15-2012, 11:55 PM

OK - I am going to have a go at this (but I want you to know this is the first time I have tried to help someone on this forum, but I have received a lot of help from others!!). I have highlighted in yellow, the areas of your file that I worked on. If I am interpreting correctly, you want to know when a refill is due. So, I did two different formulas - in column "N" the formula returns the date that the refill is due in 3 days. In column "O", it will return "due" (or you could make this word anything you wanted) on the date that the refill is 3 days away from due. Is that helpful? I have changed your date in cell H2 so the "due" shows up.