Need help calculating time in VBA
 

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

Need help calculating time in VBA
 

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.

"EAB1977" wrote in message
...
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