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.

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.

KR

H--Medication-Order-by-Patient-Report-De

H--Medication-Order-by-Patient-Report-De

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.

KR

H--Medication-Order-by-Patient-Report-De

H--Medication-Order-by-Patient-Report-De

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

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.

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.

H-Medication-Order-by-Patient-Test.xlsm

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.

KR

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
```

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 trialThis was a challenging project and you made it look so EASY.

Thanks again Ejgil. I really appreciate it.

KR

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.

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?

KR

I added it to the same function.

H-Medication-Order-by-Patient-Test.xlsm

H-Medication-Order-by-Patient-Test.xlsm

" 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.

KR

H--H-Medication-Order-by-Patient-Combine

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.

H--H-Medication-Order-Combine.xlsm

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?

KR

Sorry.

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.

H--H-Medication-Order-Combine.xlsm

KR

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.

KR

H--Med-Waste-Workbook--TEST.xlsm

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

KR

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.

H--Med-Waste-Workbook--TEST1.xlsm

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

KR

H--Calculation-Quesion-083115.xlsm

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?

Did you account for this in the macro?

KR

H--example-of-waste-for-Lewin.xlsx

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.

I have asked her to validate both.

KR

Thanks for the help.

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?"

KR

It does not matter for the time calculation, but is important for the averages.

Program changed according to that.

H--Calculation-Quesion-083115-1.xlsm

KR

Microsoft Excel

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

Thanks,

Kate