EXCEL - Need macro or formula to calculate Med Waste

OUTCOME: How many drugs are wasted each day.  This is based on a report that they receive from the Pharmacy.  They subtract the time whey were picked up (xfer type "I") from the time the drug was marked wasted ( W) by User.  This user is then instructed on proper administration of drugs to help prevent drug waste.

Here is what the user is currently doing manually:
1.      Highlight the patient's name, medication and users in the first report Medication Orders by Patient_Test.  
2.      Then look to see if there is a W noted for a medication for this patient by this same user.  This means the medication was pulled but not administered.
3.      I found that patient ESK had a drug picked up (I) by user SA at 01:23:42  on 06/15/15.  This drug was marked W at 01:33:10, so this drug was wasted.  The other listings for this patient were administered as there was no W noted.
4.      I rounded off the times and subtracted 01:23 from 1:33 and got 10 minutes of wasted time.  
5.    Note that user MM wasted 1 hr and 42 minutes or 112 minutes.  Wasted time is always listed in Minutes.

The final report should look like Medication Orders by Patient_Test2.

So I need a Formula that will do all of the above.  I am fairly new to formulas and don't know where to start.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kvrogersAuthor Commented:
I was just told there is another TYPE called PD.   PD means that the drug is a Partial dose that is wasted right away. Generally the "I" associated with the PD is only a few seconds apart. This means, the nurse wasted right away.  So these seconds would also be counted in the report.  So we have W and PD as wasted types.

Ejgil HedegaardCommented:
Check attached file.
Press the button in Q2 to run.

I have made the result in decimal minutes, since PD only gives seconds.
Or it could be rounded up to minutes, but then 1 second returns 1 minute.

I have shortened the file name, since EE truncates to 40 characters in file names.
kvrogersAuthor Commented:
This is a wonderful report.  Thank you.  

But when I take the first calculation which is 1:33:10 - 1:23:42  I get a difference of  9 minutes 38 seconds but you show 9 minutes and 47 seconds.  Am I doing something wrong.

I also get on the next one I get 23:08:35 - 21:26:47 is 1 hr and 42 minutes which calculated to 112 minutes.  and you show 101.80

Can you also show me know you created the button WASTE button.  I would like to create one for the other two macros so that the user can just click them 1,2,3.

Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Ejgil HedegaardCommented:
The results are correct, you are doing something wrong.

The difference is not 9 minutes 38 seconds, but 9 minutes 28 seconds (60 - 42 = 18 seconds + 10 seconds).
The values are not minutes and seconds, but decimal minutes, where the decimals are fractional minutes.
9.47 is 9 minutes and 0.47 * 60 seconds / minute = 28 seconds.
The real value is 9.46666666666667, but when formatted to 2 decimals show 9.47.
1 hour 42 minutes is 60 minutes + 42 minutes = 102 minutes, not 112 minutes.
When using decimal minutes, you can sum the values to give the total minutes.

If you want the seconds from Q3 use this formula: =MOD(Q3,1)*60

Time calculations are typically a bit tricky, because it always has to be corrected with 24 hours per day, 60 minutes per hour, and 60 seconds per minute.
A Date/Time value in Excel is a number, where dates are integer values starting 01/01/1900, and times are fractional day, so the value used for 06/15/2015 01:33:10 is 42170.0646990741, where 42170 is the number of days, and 0.0646990741 is the time, shown as  01:33:10 when formatted as time.

The calculation formula in the program is
(DateValue(wsResult.Range("F" & rw)) - DateValue(wsResult.Range("F" & (rw - 1))) + TimeValue(wsResult.Range("F" & rw)) - TimeValue(wsResult.Range("F" & (rw - 1)))) * 24 * 60

Open in new window

The first part with Datevalue is to correct for time before and after midnight, and add the number of days.
The factors at the end 24 * 60 converts the decimal day to minutes (with fractional seconds).
With the factors 24 * 60 * 60 the result will be seconds.

Use the Developer tab to create the button.
Use Insert and select the first, use Formular not ActiveX.
Then draw it on the sheet to the size you want, attach the appropriate macro when asked, and change the text (right click on the button, select Edit text).
If the Developer tab is not shown, go to Excel settings and set a mark to show the Developer tab.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kvrogersAuthor Commented:
I am learning so much from this website.  It is so nice that people are willing to help someone with as little experience as I have.  

This was a challenging project and you made it look so EASY.

Thanks again Ejgil.  I really appreciate it.  

PS:  I did not pawn this off as my script either,  I told them I had help from someone.  So you got credit for this here too.
kvrogersAuthor Commented:
Well, you knew they were going to come up with something else, right.  This is the new question:

With the times calculated with this report- is there then another report that can be run that can configure all the times from one nurse and average it for a given period of time?  Essentially – if Nurse Sarah wasted 10 drugs on this report – is there a way those ten values could be averaged?

Ejgil HedegaardCommented:
I added it to the same function.
kvrogersAuthor Commented:
This is great. She loves it.
Thanks again
kvrogersAuthor Commented:
Well had meeting yesterday with user and of course she wants more info.   I had her put it in writing in an email.

" I wish to have an average per individual and per unit.    "

Since she wants by UNIT too I have now included Column C which is the unit name.    She is now combining 8 excel spreadsheets to get total by month.  

I am attaching the report that I ran combining all 8 excel pages that she sent me for a test.  

I ran the first two macros but I didn't know how to tweak the Find Waste and Average MACRO because  we added a column (area or unit) to the report and I need by user and unit.

Ejgil HedegaardCommented:
Unit (Area) included in average calculations.
I added sorting by Unit and User name, it looks better.
Screen updating is turned off during program run, so you don't get the screen flicker, and program run faster.

Column B is used to define which rows to use, so I added some random data from the previous file to column A and B.

The other 2 macros can be refined, see Module 2.
Only for information. The subs are not connected to the buttons.
Selection is not needed and it slows the execution.
The formula can be written to all cells in one statement.
kvrogersAuthor Commented:
This is great but I apologize that I did not explain this correctly.

They would like to know the waste per person for the month so:

Deborah Collins  i.e.    10.47                        1S    31.47.

They want to see how many meds the Unit wastes and also be able to consul the individual on their personal med waste.

Do I also replace FORMULA with FORMULA1 and StripData with StripData1 AND delete the original?


Ejgil HedegaardCommented:
it is not always easy to describe exactly without the context of what it is used for.
I read the specification as a combined table, but you mean 2 tables, one for each type.

Here it is.
Column A:B for user name, and column D:E for Unit.

Do I also replace FORMULA with FORMULA1 and StripData with StripData1 AND delete the original?
Or copy the content of the macros to the used ones.
kvrogersAuthor Commented:
Thank you, thank you, thank you.    I replaced the macro in the commands to StripDate1 and FORMULA1.  Worked PERFECT.

kvrogersAuthor Commented:
Can you take a look at the attached report (Pt name removed) and notice that on the Average Minutes by user for DORRIES,MOLLY is -216.68 and that cannot be correct.  

She is now running the report using a whole month's worth of data.  Could this be part of the problem?

Thanks for your help.

kvrogersAuthor Commented:
Can someone please take a look at this report and let me know what you think.   I think I've narrowed the problem down to line 174 .  

Can you take a look and see what is wrong with the data or the report because the result cannot be correct?

Ejgil HedegaardCommented:
The result in line 174 comes from line 289 in the input data.
Line 289 has a W in column I but since the user name in column Q in line 288 is not the same it is not transferred to the result sheet.
But the next line 290 also has a W in column I, and the same user name in column Q, so both line 289 and 290 is transferred.
When the waste is calculated the lines are used in pairs, and for every W the waste is calculated for that line using the previous line as the start point.
But the start point in line 174 is for Lewin, Tammy and not Dories, Molly, and several days later, hence the negative result.

I have changed the waste calculation to only calculate waste for odd lines on the result sheet, since the lines are transferred to the result sheet in pairs.

That solve part of the problem, but the real problem is the input data where several lines with W in column I also has a W in the previous line, so there is no start point, only a waste point.
See the marking on the input sheet (conditional formatting) where there are 2 W following each other.
I think it will produce incorrect results, so the input data need to be "shined up".

First try to make the staff make the input correct, but I think it will be hard work to do that.
You have a lot of people feeding the data, and they make errors, so think about how to handle the errors also.
kvrogersAuthor Commented:
I was looking over the RESULTS spreadsheet and I have highlighted one I have a question on.  I don't believe the time calculation is correct.

Can you give me more information on how the calculation formula is being used

Ejgil HedegaardCommented:
18:45:15 equals the time value 0.781423611108039
13:51:30 equals the time value 0.577430555553292
The number of minutes between the 2 times are
(0.781423611108039 - 0.577430555553292) * 24 * 60 = 293.75 minutes.

The time difference is 4 hours (240 minutes), 53 minutes and 45 seconds (0.75 minutes)
Total 240 + 53 + 0.75 = 293.75 minutes

What is the problem?
kvrogersAuthor Commented:
I am attaching another example from the original spreadsheet with no macros used.  Notice that this was one were the user Lewin obtained 4mg of Morphine and tried on 2 attempts to give the Morphine but did not and so both were wasted.  This was one were there was one "I" per user, per patient but 2 "W".s and I was confused as to what happened.

Did you account for this in the macro?

Ejgil HedegaardCommented:
The program looks for "W" (or "PD"), and if the previous line is for the same user and patient, both lines are transferred to result.
In the Lewin example 4 lines are transferred (2 pairs) and the time calculation done twice.
First for line 2 and 3, and then for line 3 and 4.
If the 2 "W" are 2 wastes, then average will be correct, but if it has to be considered as only one waste, the average will be wrong.

If the later is the case, the program must look for "I" followed by "W", and that is the start point.
End point will then be "W" not followed by "W", ignoring the line(s) between.

Please specify what is correct.

It is late now here, so I will close down and look at it tomorrow when I have your answer.
kvrogersAuthor Commented:
I have submitted your question to the user.  I think the calculation for  293.75 minutes above is correct.  

I have asked her to validate both.

Thanks for the help.
kvrogersAuthor Commented:
The person requesting the report has been home with a sick child and only got back with me today so I apologize for the delay.

Here is her response on Lewin:

"So, for Nurse Lewin- There were lines – two Wastes and One “I” or withdraw.

The nurse withdrew the medication on 7/11 at 8:02am.  
She wasted the entire vile at 8:07, but did it in 2 separate entries. This doesn’t happen very often which is why I am certain it is confusing.  I am not certain how excel is calculating this, but if two wastes occur for only one “I” it is okay if only one “W” is used. So, there would be a 5 min difference between these two (8:02 and 8:07), but that 5 min would only be counted once.  Does that make sense?"

Ejgil HedegaardCommented:
That means case 2, only the last "W" or "PD" to be used.
It does not matter for the time calculation, but is important for the averages.
Program changed according to that.
kvrogersAuthor Commented:
I have passed along the information to the user.  I hope this solves the issue.  Thanks for all your  help.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.