Hello, I need help with two things.
1) A custom function that returns the number of calendar days between two dates/times
2) Help with a custom function that I already have (it is below). This returns the number of business days. So, in a query if I put :Minutes: BusinessMin([LOGGED_DT],[R
Date]), it will return the amount of minutes that have passed. However, if the minutes have then turned into an hour, day, etc... how do I get that to roll up and display? Do I need to specify that?
Function BusinessMin(StartDate As Date, EndDate As Date) As Long
Dim WrkDays As Long
Dim FirstDayMin As Long
Dim LastDayMin As Long
Dim InterDayMin As Long
Dim DaySpan As Long, WeekSpan As Long
Dim totaldays As Long, Totalhours As Long, totalminutes As Long
Dim days As Long, hours As Long, Minutes As Long
Const InTime As Date = #8:30:00 AM#
Const OutTime As Date = #5:00:00 PM#
Const WorkMin As Integer = 510
DaySpan = DateDiff("d", StartDate, EndDate)
If DaySpan >= 7 Then
WeekSpan = Int(CSng(DaySpan / 7))
DaySpan = DaySpan Mod 7
Do While WeekSpan > 0
WeekSpan = WeekSpan - 1
DaySpan = DaySpan + 5
Loop
Else
If DaySpan >= 5 Then
DaySpan = DaySpan - 2
Else
If DaySpan >= 3 Then
If DatePart("w", StartDate, 2) >= 3 Then
DaySpan = DaySpan - 2
End If
End If
End If
End If
If DaySpan > 0 Then
DaySpan = DaySpan - 1
FirstDayMin = DateDiff("n", TimeValue(StartDate), OutTime)
If FirstDayMin < 0 Then
FirstDayMin = 0
End If
LastDayMin = DateDiff("n", InTime, TimeValue(EndDate))
If LastDayMin < 0 Then
LastDayMin = 0
End If
InterDayMin = DaySpan * WorkMin
BusinessMin = FirstDayMin + InterDayMin + LastDayMin
Else
BusinessMin = DateDiff("n", StartDate, EndDate)
End If
End Function
Start Free Trial
