Obviously there is a slight bug in his original code. Otherwise the code is excellent!!!

Explanation:
14:15 to 16:25

14:00 - 14:59, the original code returns 0.25 (for the 15 minutes) instead of the required 0.75 (for the 45 minutes) the patient is waiting in the waiting room.

So, the correction to the code is in this line:

*** dTimes(dInHour) = (60 - Minute(InTime)) / 60

I repeat the complete code for ease of reference to all others.

Sub Test()
Dim dtIn As Date
Dim dtOut As Date
Dim dTimes() As Double

dtIn = Range("A1").Value
dtOut = Range("B1").Value

dTimes = TimeArray(dtIn, dtOut)
Range("C1").Resize(1, 24).Value = dTimes
End Sub

Private Function TimeArray(ByVal InTime As Date, ByVal OutTime As Date) As Double()
Dim dInHour As Double
Dim dOutHour As Double
Dim dTimes(23) As Double
Dim lCtr As Long

dInHour = Hour(InTime)
dOutHour = Hour(OutTime)

dTimes(dInHour) = (60 - Minute(InTime)) / 60
For lCtr = dInHour + 1 To dOutHour - 1
dTimes(lCtr) = 1
Next
dTimes(dOutHour) = Minute(OutTime) / 60
TimeArray = dTimes
End Function






BillElerdin wrote:

Good eveing shg and dbKemp.
13-Nov-08

Good eveing shg and dbKemp. I copied your code to the VB code sheet, and
tried to run it. As you can tell, I'm lost in this arena. It didn't run,
and apparently the name 'TimeArray' got an 'ambiguous name detected' error..

This is what I had on the Excel workbook sheet (I did add the date since we
have patients that span over midnight):


Time in time out
11/10/08 6:15 11/10/08 18:30
11/11/08 13:54 11/11/08 15:15
11/11/08 22:30 11/12/08 2:45

Could you either help me see what I did wrong, or identify a resource I can
reference to study up on this (near term)? Thanks for your assistance. I
presume I'm missing something basic. -Bill

"shg" wrote:

Previous Posts In This Thread:

On Friday, November 07, 2008 2:26 AM
BillElerdin wrote:

Time Range Summary - parse data
Greatings from the SF Bay Area. I'm lost on a particular problem.

I want to summarize patient visits to our ED and Clinics. I'd like to take
an arrival-to-discharge time, and spread the
minutes/hours they were 'in-department' during the day. For example, Patient
'A' presents at 14:15 and is discharged at 16:25. I'd like to allocate his
time as .75 hours (or 45 monutes) between 14:00 and 14:59, 1 hour between
15:00 and 15:59, and .42 hours (25") between 16:00 and 16:59. I haven't a
clue how to efficiently do this. Thanks in advance for help on this...
hopefully it will shorten waits in the Emergency Room.

I envision a spreadsheet delineating Hour of Day on the top row, and
patients down column 'A'. Each patient stay would be noted as hours or
minutes in a row over the appropriate hour columns. -Bill

On Friday, November 07, 2008 9:33 PM
dbKemp wrote:

Time Range Summary - parse data
On Nov 7, 2:26 am, Bill Elerding
wrote:

This assumes In Time is in cell A1 and Out Time is in cell B1. The
results of the function are put into the 24 cells to the right of the
two input times.

Sub Test()
Dim dtIn As Date
Dim dtOut As Date
Dim dTimes() As Double

dtIn = Range("A1").Value
dtOut = Range("B1").Value

dTimes = TimeArray(dtIn, dtOut)
Range("C1").Resize(1, 24).Value = dTimes
End Sub

Private Function TimeArray(ByVal InTime As Date, ByVal OutTime As
Date) As Double()
Dim dInHour As Double
Dim dOutHour As Double
Dim dTimes(23) As Double
Dim lCtr As Long

dInHour = Hour(InTime)
dOutHour = Hour(OutTime)

dTimes(dInHour) = Minute(InTime) / 60
For lCtr = dInHour + 1 To dOutHour - 1
dTimes(lCtr) = 1
Next
dTimes(dOutHour) = Minute(OutTime) / 60
TimeArray = dTimes
End Function

On Saturday, November 08, 2008 1:46 PM
BillElerdin wrote:

Thanks, dbKemp. I copied your data to the code sheet, and tried to run it.
Thanks, dbKemp. I copied your data to the code sheet, and tried to run it.
When I did, I got the following error:

Compile error: Sub or Function not defined.

it was on line 7, "dTimes = TimeArray(dtIn, dtOut)"

Any thoughts on this. THANKS! -Bill


"dbKemp" wrote:

On Saturday, November 08, 2008 3:52 PM
shg wrote:

Time Range Summary - parse data
The line of code got wrapped:

Code:
--------------------
Private Function TimeArray(ByVal InTime As Date, ByVal OutTime As Date) As Double()
Dim dInHour As Double
Dim dOutHour As Double
Dim dTimes(23) As Double
Dim lCtr As Long

dInHour = Hour(InTime)
dOutHour = Hour(OutTime)

dTimes(dInHour) = Minute(InTime) / 60
For lCtr = dInHour + 1 To dOutHour - 1
dTimes(lCtr) = 1
Next
dTimes(dOutHour) = Minute(OutTime) / 60
TimeArray = dTimes
End Function

--------------------


--
shg
------------------------------------------------------------------------
shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=26431

On Thursday, November 13, 2008 11:01 PM
BillElerdin wrote:

Good eveing shg and dbKemp.
Good eveing shg and dbKemp. I copied your code to the VB code sheet, and
tried to run it. As you can tell, I'm lost in this arena. It didn't run,
and apparently the name 'TimeArray' got an 'ambiguous name detected' error..

This is what I had on the Excel workbook sheet (I did add the date since we
have patients that span over midnight):


Time in time out
11/10/08 6:15 11/10/08 18:30
11/11/08 13:54 11/11/08 15:15
11/11/08 22:30 11/12/08 2:45

Could you either help me see what I did wrong, or identify a resource I can
reference to study up on this (near term)? Thanks for your assistance. I
presume I'm missing something basic. -Bill

"shg" wrote:

EggHeadCafe - Software Developer Portal of Choice
ASP.NET Data Control Series - Part 2
http://www.eggheadcafe.com/tutorials...trol-seri.aspx