Calculate Buisness Days Open

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using the following code to calculate buisness days which is working
fine, but it requires input of both start and end dates.
I would like to modify the code to calculate with the input start date and
use the current date if the end date field is null until a date is entered to
the end date field.

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare
'Compliments of Dave Hargis

intTotalDays = DateDiff("d", dtmStart, dtmEnd) +1 'Start with total days
'Add one to include First Day

dtmToday = dtmStart 'Initiate compare date
Do Until dtmToday > dtmEnd
If Weekday(dtmToday, vbMonday) > 5 Then 'It is Saturday or Sunday

intTotalDays = intTotalDays - 1 'Take one day away for Weekend day

ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day away for the Holiday

End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next Compare

Loop 'Until dtmToday > dtmEnd All days have been compared

CalcWorkDays = intTotalDays 'Return the value
End Function

Can the code be modified that way? If not is there a different code I can use?


Thank You.
 
Change the data type of the second parameter to Variant (this will allow it
to be null). Then, check to see if it is NULL and if so, convert it to
todays date You could even make the second parameter optional so if you only
pass it one date, it will automatically use todays date as the dtmEnd.

HTH
Dale

Function CalcWorkDays(dtmStart As Date, _
Optional dtmEnd As Variant = NULL) As
Integer

Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare
'Compliments of Dave Hargis

'Added this line
if ISNULL(dtmEnd) then dtmEnd = Date()

intTotalDays = DateDiff("d", dtmStart, dtmEnd) +1 'Start with total days
'Add one to include First Day
dtmToday = dtmStart 'Initiate compare date
Do Until dtmToday > dtmEnd
If Weekday(dtmToday, vbMonday) > 5 Then 'It is Saturday or Sunday

intTotalDays = intTotalDays - 1 'Take one day away for Weekend day

ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day away for the Holiday

End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next Compare

Loop 'Until dtmToday > dtmEnd All days have been compared

CalcWorkDays = intTotalDays 'Return the value
End Function
Click to expand...

--
Email address is not valid.
Please reply to newsgroup only.


Pierre said:
I am using the following code to calculate buisness days which is working
fine, but it requires input of both start and end dates.
I would like to modify the code to calculate with the input start date and
use the current date if the end date field is null until a date is entered to
the end date field.

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare
'Compliments of Dave Hargis

intTotalDays = DateDiff("d", dtmStart, dtmEnd) +1 'Start with total days
'Add one to include First Day

dtmToday = dtmStart 'Initiate compare date
Do Until dtmToday > dtmEnd
If Weekday(dtmToday, vbMonday) > 5 Then 'It is Saturday or Sunday

intTotalDays = intTotalDays - 1 'Take one day away for Weekend day

ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day away for the Holiday

End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next Compare

Loop 'Until dtmToday > dtmEnd All days have been compared

CalcWorkDays = intTotalDays 'Return the value
End Function

Can the code be modified that way? If not is there a different code I can use?


Thank You.
Click to expand...
 
That modification is just what I needed, it is working well.

Thank you very much.

Dale Fye said:
Change the data type of the second parameter to Variant (this will allow it
to be null). Then, check to see if it is NULL and if so, convert it to
todays date You could even make the second parameter optional so if you only
pass it one date, it will automatically use todays date as the dtmEnd.

HTH
Dale

Function CalcWorkDays(dtmStart As Date, _
Optional dtmEnd As Variant = NULL) As
Integer

Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare
'Compliments of Dave Hargis

'Added this line
if ISNULL(dtmEnd) then dtmEnd = Date()

intTotalDays = DateDiff("d", dtmStart, dtmEnd) +1 'Start with total days
'Add one to include First Day
dtmToday = dtmStart 'Initiate compare date
Do Until dtmToday > dtmEnd
If Weekday(dtmToday, vbMonday) > 5 Then 'It is Saturday or Sunday

intTotalDays = intTotalDays - 1 'Take one day away for Weekend day

ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day away for the Holiday

End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next Compare

Loop 'Until dtmToday > dtmEnd All days have been compared

CalcWorkDays = intTotalDays 'Return the value
End Function
Click to expand...

--
Email address is not valid.
Please reply to newsgroup only.


Pierre said:
I am using the following code to calculate buisness days which is working
fine, but it requires input of both start and end dates.
I would like to modify the code to calculate with the input start date and
use the current date if the end date field is null until a date is entered to
the end date field.

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare
'Compliments of Dave Hargis

intTotalDays = DateDiff("d", dtmStart, dtmEnd) +1 'Start with total days
'Add one to include First Day

dtmToday = dtmStart 'Initiate compare date
Do Until dtmToday > dtmEnd
If Weekday(dtmToday, vbMonday) > 5 Then 'It is Saturday or Sunday

intTotalDays = intTotalDays - 1 'Take one day away for Weekend day

ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day away for the Holiday

End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next Compare

Loop 'Until dtmToday > dtmEnd All days have been compared

CalcWorkDays = intTotalDays 'Return the value
End Function

Can the code be modified that way? If not is there a different code I can use?


Thank You.
Click to expand...
Click to expand...
 
Back
Top