Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time Sheet - Calculating Time Differences for Totals | Excel Discussion (Misc queries) | |||
Calculating Regular time, overtime and double time | Excel Worksheet Functions | |||
Daylight Saving Time (DST) and calculating time difference. | Excel Programming | |||
Daylight Saving Time (DST) and calculating time difference. | Excel Programming | |||
calculating timesheet, time-in/time-out = total hours & minutes, . | Excel Worksheet Functions |