aşağıdaki kodu modüle kaydet
Function workDays(BegDate, EndDate)
Const SUNDAY = 1
Dim NumWeeks As Integer
If BegDate > EndDate Then
workDays = 0
Else
Select Case Weekday(BegDate)
Case SUNDAY: BegDate = BegDate + 1
End Select
Select Case Weekday(EndDate)
Case SUNDAY: EndDate = EndDate - 2
End Select
NumWeeks = DateDiff("ww", BegDate, EndDate)
workDays = NumWeeks * 6 + Weekday(EndDate) - Weekday(BegDate)
End If
End Function
sorguda
PazarHaric: workDays([İzin Başlama Günü],[Döneceği Gün])
şeklinde kullan
Saat için aynı modüle aşağıdaki kodu ekle,
Public Function Diff2Dates(Interval As String, Date1 As Date, Date2 As Date, _
Optional ShowZero As Boolean = False) As Variant
On Error GoTo Err_Diff2Dates
Dim booCalcYears As Boolean
Dim booCalcMonths As Boolean
Dim booCalcDays As Boolean
Dim booCalcHours As Boolean
Dim booCalcMinutes As Boolean
Dim booCalcSeconds As Boolean
Dim booSwapped As Boolean
Dim dtTemp As Date
Dim intCounter As Integer
Dim lngDiffHours As Long
Dim lngDiffMinutes As Long
Dim varTemp As Variant
Const INTERVALS As String = "hn"
'Check that Interval contains only valid characters
Interval = LCase$(Interval)
For intCounter = 1 To Len(Interval)
If InStr(1, INTERVALS, Mid$(Interval, intCounter, 1)) = 0 Then
Exit Function
End If
Next intCounter
'Check that valid dates have been entered
If Not (IsDate(Date1)) Then Exit Function
If Not (IsDate(Date2)) Then Exit Function
'If necessary, swap the dates, to ensure that
'Date1 is lower than Date2
If Date1 > Date2 Then
dtTemp = Date1
Date1 = Date2
Date2 = dtTemp
booSwapped = True
End If
Diff2Dates = Null
varTemp = Null
'What intervals are supplied
booCalcHours = (InStr(1, Interval, "h") > 0)
booCalcMinutes = (InStr(1, Interval, "n") > 0)
'Get the cumulative differences
If booCalcHours Then
lngDiffHours = Abs(DateDiff("h", Date1, Date2)) - _
IIf(Format$(Date1, "nnss") <= Format$(Date2, "nnss"), 0, 1)
Date1 = DateAdd("h", lngDiffHours, Date1)
End If
If booCalcMinutes Then
lngDiffMinutes = Abs(DateDiff("n", Date1, Date2)) - _
IIf(Format$(Date1, "ss") <= Format$(Date2, "ss"), 0, 1)
Date1 = DateAdd("n", lngDiffMinutes, Date1)
End If
If booCalcHours And (lngDiffHours > 0 Or ShowZero) Then
If booCalcHours Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffHours & IIf(lngDiffHours <> 1, " saat", " hour")
End If
End If
If booCalcMinutes And (lngDiffMinutes > 0 Or ShowZero) Then
If booCalcMinutes Then
varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
lngDiffMinutes & IIf(lngDiffMinutes <> 1, " dakika", " minute")
End If
End If
If booSwapped Then
varTemp = "-" & varTemp
End If
Diff2Dates = Trim$(varTemp)
End_Diff2Dates:
Exit Function
Err_Diff2Dates:
Resume End_Diff2Dates
End Function
sorguda da
SaatFarki: Diff2Dates("hn",[İzin Başlama Saati],[Döneceği Saat])
şeklinde kullan
Her iki kodda yabancı bir siteden alıntıdır.