Calculate Buisness Days Open

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.
 
G

Guest

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...
 
G

Guest

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calcworkdays Revision? 2
Counting Workdays Function 1
Need some help updating text box after query in form. 2
Help with my function vba code 1
CalcWorkDays error 1
Can't query DateDiff 3
Infamous Business Day Count 7
Number of days 9

Top