# Thread: Auto calculate utilization rate

1. Hi,

I used ASP and wrote a login / logout program. This is to capture people who comes into my lab from 0800 to 1700.

The data collected are stored in a table

Userid Date_in Time_in Date_out Time_out
123 4/3/2005 0900 4/3/2005 1000
121 4/3/2005 0925 4/3/2005 1100
122 4/3/2005 1200 4/3/2005 1400
127 4/3/2005 1500 4/3/2005 1600
125 4/3/2005 1503 4/3/2005 1630
122 5/3/2005 0800 5/3/2005 0845

The manual way of calculating utilization on 4/3/2005 will be [(Time between 0900 to 1100) + (Time between 1200 to 1400) + (Time between 1500 to 1630)] / (Time between 0800 to 1700) X 100%

The problem is that depending on what time the next person arrives, there might be an overlap in time. For example, A comes at 9am and leaves at 11am. B comes at 9.30am and leaves at 12pm. The total duration will be from 9.00am to 12 pm that is 3 hours instead of (rs("time_out")-rs("time_in")) = (11-9) + (12-9.5) = 4.5 hours

Is there a formula or way to automate this process?

Any help will be appreciated!

Thanks

Mark

2.

3. Try something like this...
Code:
```'<air code />
'This code assumes that visitors will go in to lab and leave on the same day.

strSQL = "SELECT * FROM tblTable " & _
"WHERE Date_in='4/3/2005' " & _
"ORDER BY Date_in, Time_in, Date_out, Time_out"

objRS.Open strSQL, objConnection

Do While objRS.EOF = False
If Len(tmTime_in) = 0 Then
' First person in lab
tmTime_in = objRS("Time_in")
tmTime_out = objRS("Time_out")
ElseIf objRS("Time_out") <= tmTime_out Then
' Someone enters AND leaves the lab while occupied
'Do nothing
ElseIf objRS("Time_in") <= tmTime_out Then
' Someone enters the lab while occupied AND leaves after the current visitor
tmTime_out = objRS("Time_out")
ElseIf objRS("Time_in") > tmTime_out Then
' Someone enters the lab after previous visitor leaves
numTotalTimeInMinutes = numTotalTimeInMinutes + (tmTime_out - tmTime_in)

tmTime_in = objRS("Time_in")
tmTime_out = objRS("Time_out")
End If

objRS.MoveNext
Loop

numTotalTimeInMinutes = numTotalTimeInMinutes + (tmTime_out - tmTime_in)

Response.Write numTotalTimeInMinutes```

4. Dear ACW,

Thank u for your kind help. The codes works perfectly for calculating 1 day. I am suppose to print a report for the monthly utilization. I will try to use an increment counter to do a DateADD to the strSQL statement.

Regards,
Mark

5. Does each user login into their own account?

Regards, Drew
Captain Media

6. No. The student just key in their student id when they come in and key in their student id when they come out.

Regards,
Mark

Originally Posted by captainmedia
Does each user login into their own account?

Regards, Drew
Captain Media