Modifying a Macro to Produce Reports

[PNeil]

Hello everyone,
I am a big fan of this forum and have used it to solve many problems by searching through here (I only recently registered). I recently used a Macro created by hiker95 several years back. I am using this macro to sign in/out patients at my facility instead of a paper sign in/out book. This program utilizes a USB Barcode Scanner for input data. I have around 100 patients who are coming and going many times during the day each. Below is a sample I am using to work out bugs.

Name		Time Out(1)	Time In(1)	Time Out(2)	Time In(2)	Time Out(3)	Time In(3)
Frank Sinatra		9/25/2019 12:38	9/25/2019 12:45
Josephina Johnson-Atkinson		9/25/2019 12:38	9/25/2019 12:45
Neil Paul		9/25/2019 12:38	9/25/2019 12:45
George Jetson		9/25/2019 12:38	9/25/2019 12:45

<colgroup><col width="228" style="width: 171pt; mso-width-source: userset; mso-width-alt: 8338;"> <col width="23" style="width: 17pt; mso-width-source: userset; mso-width-alt: 841;"> <col width="128" style="width: 96pt; mso-width-source: userset; mso-width-alt: 4681;"> <col width="128" style="width: 96pt; mso-width-source: userset; mso-width-alt: 4681;"> <col width="129" style="width: 97pt; mso-width-source: userset; mso-width-alt: 4717;"> <col width="130" style="width: 98pt; mso-width-source: userset; mso-width-alt: 4754;"> <col width="127" style="width: 95pt; mso-width-source: userset; mso-width-alt: 4644;"> <col width="130" style="width: 98pt; mso-width-source: userset; mso-width-alt: 4754;"> <tbody> </tbody>

What I am looking for here is to see if it is possible to create a report, possibly on another sheet or highlighting cell pairs, to display when each patient is out of the building longer than 4hrs [e.g., the difference between Time Out(1) and Time In(1)]. Also, an additional report with total cumulative time out and/or total # of times out of the building.

I manage a residential substance abuse program and we are aiming to increase our accounting of patients. These changes would be used to collect data to prevent/predict relapse and monitor adherence to the program.

I am not able to code in VBA and am thankful for the code written by hiker95. I was only curious about the potential to use this data to further our treatment goals.

link to past post where this program was found. https://www.mrexcel.com/forum/excel-questions/672492-scan-barcode-excel-date-time-stamp-out.html

Thank you

 

[Rijnsent]

Hi PNeil,
could you add some examples of what you are trying to achieve? The data in your mockup shows only people who left and came back 7 minutes later, right? Being out longer than 4 hours: that's basically B4: =IF(B4<NOW()-4/24,"OUT >4","OK")
You could e.g. use that in conditional formating, say for E2 the formula: =E2<NOW()-4/24
If you can provide a bit more data, someone could help you setting up some calculation sheet that you could then use to make your reports (yeah, also without macros )
Cheers,
Koen