Need help calculating time in VBA

[EAB1977]

I need some expert eyes here.

What I am trying to accomplish is converting time into minutes for a
report that I am working on. Am I anywhere close? SOme of the
calculatuions are not correct if I use the Minutes function. Please
help.

Function TestTime(StartDate As Date, StartTime As Date, Optional
CompletedDate As Date, _
Optional CompletedTime As Date)
Dim dbs As DAO.Database, rs As DAO.Recordset, CurDate As Date, HolTime
As Integer

'** Convert the time calculations to minutes

'These are known times
Const EndTime As Date = #5:00:00 PM#
Const MorningTime As Date = #8:00:00 AM#
Const LunchStart As Date = #12:00:00 PM#
Const LunchEnd As Date = #1:00:00 PM#

'** First, is there a CompletedDate?
If IsNull(CompletedDate) Then
TestTime = 0
Exit Function
End If

'** Next, check to see if the StartDate and the CompletedDate are the
same.
If StartDate = CompletedDate Then
If CompletedTime < #12:00:00 PM# Then
TestTime = CompletedTime - StartTime
Else
TestTime = (CompletedTime - StartTime) - LunchTime
End If
Exit Function
ElseIf CompletedDate - StartDate = 1 Then
MsgBox EndTime - StartTime
'TestTime = Minute(TestTime)
'TestTime = TestTime + (CompletedTime - MorningTime)
Exit Function
End If

'** Next, run the query for any holidays and calculate minutes
Set dbs = DAO.OpenDatabase("\\files-2K1\ENG\QA\Database\CQAAnalysis
\CQAAnalysis.mdb")
Set rs = dbs.OpenRecordset("SELECT * FROM tblHolidays WHERE
tblHolidays.HolidayDate" _
& " Between #" & StartDate & "# And #" & CompletedDate
& "#")
If rs.BOF = True And rs.EOF = True Then
HolTime = 0
Else
rs.MoveLast
HolTime = rs.RecordCount * (8 * 60) 'To get minutes from days
End If


'** Next, find the time for the time the shipment was started
TestTime = Minute(EndTime - StartTime + (EndTime < StartTime))
CurDate = StartDate + 1

'** Next
Do Until CurDate > CompletedDate
If CurDate < CompletedDate Then
If Weekday(CurDate) < 6 Then 'Saturday or Sunday
TestTime = TestTime * (8 * 60)
End If
ElseIf CurDate = CompletedDate Then
TestTime = TestTime + (Minute(CompletedTime - MorningTime))
End If
CurDate = CurDate + 1
Loop

End Function

 

[Fred Smith]

To convert a time to minutes, multiply by 1440.
The Minute function returns only the number of minutes in the hour. It
doesn't convert the time to minutes.

Regards,
Fred.