WorkingDays Function (need hrs, not days)

R

RJF

Thanks to this forum, I have the Function below in my database that
calculates the difference between 2 dates.

It calculates how many days between the 2 dates (minus weekend and
holidays), but I need to calculate the Hours and Minutes (i.e. 4:15 is 4
hours and 15 minutes).

If BeginDate is 7/22/2008 8:17:00 AM and EndDate is 7/23/2008 8:17:00 AM,
I need it to show how many hours between
7/22/2008 8:17:00 AM to 7/22/2008 5:00:00 PM
then add those hours to the hours between
7/23/2008 8:00:00 AM and 7/23/2008 8:17:00 AM.
So the result would be 9:00.

If this is something that is very difficult to do, I will be putting it on
the back burner for a while. In other words, no rush but I would appreciate
any help.

I hope I've explained myself clearly enough.

Public Function WorkingDays2(BeginDate As Date, EndDate As Date) As Integer
'....................................................................
' Name: WorkingDays
' Inputs: BeginDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function has been modified to account for holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

BeginDate = BeginDate + 1
'To count BeginDate as the 1st day comment out the line above

intCount = 0

Do While BeginDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & BeginDate & "#"
If Weekday(BeginDate ) <> vbSunday And Weekday(BeginDate ) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If

BeginDate = BeginDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

Thanks so much.
 
G

Graham Mandeno

Hi RJF

I think the best approach is to perform the calculation in the smallest unit
you want to show (in your case, minutes) and then format the result.

I suggest you first modify your function to give you the number of *full*
work days, excluding the start and end dates. The following changes should
do the trick:

Public Function WorkingDays2(ByVal BeginDate As Date, _
ByVal EndDate As Date) As Integer
....
BeginDate = DateValue(BeginDate) + 1
EndDate = DateValue(EndDate)

intCount = 0

Do While BeginDate < EndDate
....

Note:
1. ByVal ensures that any changes made to the date variables in the
function don't affect the values that were passed.
2. DateValue() discards the time component of a date/time value
3. Changing <= to < excludes the last day

You can then calculate the number of minutes on the start day and on the end
day, and add the result of your function multiplied by the number of minutes
in a working day:

Public Function WorkingMinutes(StartTime as Date, EndTime as Date) as Long
Const StartOfDay = #08:00:00 AM#
Const EndOfDay = #05:00:00 PM#
WorkingMinutes = _
DateDiff("n", StartTime, DateValue(StartTime) + EndOfDay) _
+ DateDiff("n", DateValue(EndTime) + StartOfDay, EndTime) _
+ WorkingDays2(StartTime, EndTime) * DateDiff("n", StartOfDay, EndOfDay)
End Function


To format the minutes as hh:mm, use this function:

Public Function FormatHHMM(Minutes as Long) as String
FormatHHMM = Int(Minutes / 60) & ":" & (Minutes Mod 60)
End Function
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

RJF said:
Thanks to this forum, I have the Function below in my database that
calculates the difference between 2 dates.

It calculates how many days between the 2 dates (minus weekend and
holidays), but I need to calculate the Hours and Minutes (i.e. 4:15 is 4
hours and 15 minutes).

If BeginDate is 7/22/2008 8:17:00 AM and EndDate is 7/23/2008 8:17:00 AM,
I need it to show how many hours between
7/22/2008 8:17:00 AM to 7/22/2008 5:00:00 PM
then add those hours to the hours between
7/23/2008 8:00:00 AM and 7/23/2008 8:17:00 AM.
So the result would be 9:00.

If this is something that is very difficult to do, I will be putting it on
the back burner for a while. In other words, no rush but I would
appreciate
any help.

I hope I've explained myself clearly enough.

Public Function WorkingDays2(BeginDate As Date, EndDate As Date) As
Integer
'....................................................................
' Name: WorkingDays
' Inputs: BeginDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between
them
' Note that this function has been modified to account for holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

BeginDate = BeginDate + 1
'To count BeginDate as the 1st day comment out the line above

intCount = 0

Do While BeginDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & BeginDate & "#"
If Weekday(BeginDate ) <> vbSunday And Weekday(BeginDate ) <> vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If

BeginDate = BeginDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

Thanks so much.
Click to expand...
 
R

RJF

Hi Graham,

Thank you so much for the help and the quick response. I'm not very
experienced with this so forgive me for not understanding it all.

I created modules with the Functions that you gave me.

Then I called the last 2 in my query (even though I only need the last one).
Here's my query:

SELECT Mid([ops_name],7,99) AS Name, ops_vendors.name AS Vendor,
ops_equipment.description AS Machine, UCase([problem]) AS Problem1,
ops_equipment.serial_no AS [Serial No], ops_equipment.service_id AS [Service
ID], ops_service_log.BeginDate AS [Open Date], ops_service_log.EndDate,
WorkingMinutes([BeginDate],[EndDate]) AS Minutes, FormatHHMM([Minutes]) AS
[Hours Open]
FROM (vw_vhb_combine_accounts_distinct_DB INNER JOIN ((ops_service_log INNER
JOIN ops_equipment ON ops_service_log.equip_id = ops_equipment.equip_id)
INNER JOIN ops_vendors ON ops_equipment.vendor_id = ops_vendors.vendor_id) ON
vw_vhb_combine_accounts_distinct_DB.sub_acctno = ops_equipment.acctno) INNER
JOIN ops_param ON ops_equipment.acctno = ops_param.acctno
WHERE (((ops_service_log.BeginDate) Between #1/1/2008# And #8/29/2008#) AND
((vw_vhb_combine_accounts_distinct_DB.main_acctno)="191322"))
ORDER BY Mid([ops_name],7,99), ops_vendors.name, ops_equipment.description,
ops_equipment.serial_no, ops_equipment.service_id, ops_service_log.BeginDate;

The result is not giving the correct hours and minutes.

For example:

1/7/2008 11:03:32 AM to 1/7/2008 3:03:00 PM
is giving me 13:0

Can you tell what I am doing wrong?

Again, thank you so much.

-Rachel

--
RJF


Graham Mandeno said:
Hi RJF

I think the best approach is to perform the calculation in the smallest unit
you want to show (in your case, minutes) and then format the result.

I suggest you first modify your function to give you the number of *full*
work days, excluding the start and end dates. The following changes should
do the trick:

Public Function WorkingDays2(ByVal BeginDate As Date, _
ByVal EndDate As Date) As Integer
...
BeginDate = DateValue(BeginDate) + 1
EndDate = DateValue(EndDate)

intCount = 0

Do While BeginDate < EndDate
...

Note:
1. ByVal ensures that any changes made to the date variables in the
function don't affect the values that were passed.
2. DateValue() discards the time component of a date/time value
3. Changing <= to < excludes the last day

You can then calculate the number of minutes on the start day and on the end
day, and add the result of your function multiplied by the number of minutes
in a working day:

Public Function WorkingMinutes(StartTime as Date, EndTime as Date) as Long
Const StartOfDay = #08:00:00 AM#
Const EndOfDay = #05:00:00 PM#
WorkingMinutes = _
DateDiff("n", StartTime, DateValue(StartTime) + EndOfDay) _
+ DateDiff("n", DateValue(EndTime) + StartOfDay, EndTime) _
+ WorkingDays2(StartTime, EndTime) * DateDiff("n", StartOfDay, EndOfDay)
End Function


To format the minutes as hh:mm, use this function:

Public Function FormatHHMM(Minutes as Long) as String
FormatHHMM = Int(Minutes / 60) & ":" & (Minutes Mod 60)
End Function
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

RJF said:
Thanks to this forum, I have the Function below in my database that
calculates the difference between 2 dates.

It calculates how many days between the 2 dates (minus weekend and
holidays), but I need to calculate the Hours and Minutes (i.e. 4:15 is 4
hours and 15 minutes).

If BeginDate is 7/22/2008 8:17:00 AM and EndDate is 7/23/2008 8:17:00 AM,
I need it to show how many hours between
7/22/2008 8:17:00 AM to 7/22/2008 5:00:00 PM
then add those hours to the hours between
7/23/2008 8:00:00 AM and 7/23/2008 8:17:00 AM.
So the result would be 9:00.

If this is something that is very difficult to do, I will be putting it on
the back burner for a while. In other words, no rush but I would
appreciate
any help.

I hope I've explained myself clearly enough.

Public Function WorkingDays2(BeginDate As Date, EndDate As Date) As
Integer
'....................................................................
' Name: WorkingDays
' Inputs: BeginDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between
them
' Note that this function has been modified to account for holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

BeginDate = BeginDate + 1
'To count BeginDate as the 1st day comment out the line above

intCount = 0

Do While BeginDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & BeginDate & "#"
If Weekday(BeginDate ) <> vbSunday And Weekday(BeginDate ) <> vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If

BeginDate = BeginDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

Thanks so much.
Click to expand...
Click to expand...
 
G

Graham Mandeno

Hi Rachel

I'm sorry about that - I didn't allow for the case of the start and end
times being on the same day. DUH!

Public Function WorkingMinutes(StartTime As Date, EndTime As Date) As Long
Const StartOfDay = #8:00:00 AM#
Const EndOfDay = #5:00:00 PM#
If DateValue(StartTime) = DateValue(EndTime) Then
WorkingMinutes = DateDiff("n", StartTime, EndTime)
Else
WorkingMinutes = _
DateDiff("n", StartTime, DateValue(StartTime) + EndOfDay) _
+ DateDiff("n", DateValue(EndTime) + StartOfDay, EndTime) _
+ WorkingDays2(StartTime, EndTime) * DateDiff("n", StartOfDay, EndOfDay)
End If
End Function

Also, the following modified format function will ensure you always get two
digits for the minutes:

Public Function FormatHHMM(Minutes As Long) As String
FormatHHMM = Int(Minutes / 60) & ":" & Format(Minutes Mod 60, "00")
End Function

I note in your example you have a value for the seconds (1/7/2008 11:03:32
AM). Do you want to record times to the nearest second? If so then a minor
modification will be required.
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand


RJF said:
Hi Graham,

Thank you so much for the help and the quick response. I'm not very
experienced with this so forgive me for not understanding it all.

I created modules with the Functions that you gave me.

Then I called the last 2 in my query (even though I only need the last
one).
Here's my query:

SELECT Mid([ops_name],7,99) AS Name, ops_vendors.name AS Vendor,
ops_equipment.description AS Machine, UCase([problem]) AS Problem1,
ops_equipment.serial_no AS [Serial No], ops_equipment.service_id AS
[Service
ID], ops_service_log.BeginDate AS [Open Date], ops_service_log.EndDate,
WorkingMinutes([BeginDate],[EndDate]) AS Minutes, FormatHHMM([Minutes]) AS
[Hours Open]
FROM (vw_vhb_combine_accounts_distinct_DB INNER JOIN ((ops_service_log
INNER
JOIN ops_equipment ON ops_service_log.equip_id = ops_equipment.equip_id)
INNER JOIN ops_vendors ON ops_equipment.vendor_id = ops_vendors.vendor_id)
ON
vw_vhb_combine_accounts_distinct_DB.sub_acctno = ops_equipment.acctno)
INNER
JOIN ops_param ON ops_equipment.acctno = ops_param.acctno
WHERE (((ops_service_log.BeginDate) Between #1/1/2008# And #8/29/2008#)
AND
((vw_vhb_combine_accounts_distinct_DB.main_acctno)="191322"))
ORDER BY Mid([ops_name],7,99), ops_vendors.name,
ops_equipment.description,
ops_equipment.serial_no, ops_equipment.service_id,
ops_service_log.BeginDate;

The result is not giving the correct hours and minutes.

For example:

1/7/2008 11:03:32 AM to 1/7/2008 3:03:00 PM
is giving me 13:0

Can you tell what I am doing wrong?

Again, thank you so much.

-Rachel

--
RJF


Graham Mandeno said:
Hi RJF

I think the best approach is to perform the calculation in the smallest
unit
you want to show (in your case, minutes) and then format the result.

I suggest you first modify your function to give you the number of *full*
work days, excluding the start and end dates. The following changes
should
do the trick:

Public Function WorkingDays2(ByVal BeginDate As Date, _
ByVal EndDate As Date) As Integer
...
BeginDate = DateValue(BeginDate) + 1
EndDate = DateValue(EndDate)

intCount = 0

Do While BeginDate < EndDate
...

Note:
1. ByVal ensures that any changes made to the date variables in the
function don't affect the values that were passed.
2. DateValue() discards the time component of a date/time value
3. Changing <= to < excludes the last day

You can then calculate the number of minutes on the start day and on the
end
day, and add the result of your function multiplied by the number of
minutes
in a working day:

Public Function WorkingMinutes(StartTime as Date, EndTime as Date) as
Long
Const StartOfDay = #08:00:00 AM#
Const EndOfDay = #05:00:00 PM#
WorkingMinutes = _
DateDiff("n", StartTime, DateValue(StartTime) + EndOfDay) _
+ DateDiff("n", DateValue(EndTime) + StartOfDay, EndTime) _
+ WorkingDays2(StartTime, EndTime) * DateDiff("n", StartOfDay,
EndOfDay)
End Function


To format the minutes as hh:mm, use this function:

Public Function FormatHHMM(Minutes as Long) as String
FormatHHMM = Int(Minutes / 60) & ":" & (Minutes Mod 60)
End Function
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

RJF said:
Thanks to this forum, I have the Function below in my database that
calculates the difference between 2 dates.

It calculates how many days between the 2 dates (minus weekend and
holidays), but I need to calculate the Hours and Minutes (i.e. 4:15 is
4
hours and 15 minutes).

If BeginDate is 7/22/2008 8:17:00 AM and EndDate is 7/23/2008 8:17:00
AM,
I need it to show how many hours between
7/22/2008 8:17:00 AM to 7/22/2008 5:00:00 PM
then add those hours to the hours between
7/23/2008 8:00:00 AM and 7/23/2008 8:17:00 AM.
So the result would be 9:00.

If this is something that is very difficult to do, I will be putting it
on
the back burner for a while. In other words, no rush but I would
appreciate
any help.

I hope I've explained myself clearly enough.

Public Function WorkingDays2(BeginDate As Date, EndDate As Date) As
Integer
'....................................................................
' Name: WorkingDays
' Inputs: BeginDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between
them
' Note that this function has been modified to account for holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

BeginDate = BeginDate + 1
'To count BeginDate as the 1st day comment out the line above

intCount = 0

Do While BeginDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & BeginDate & "#"
If Weekday(BeginDate ) <> vbSunday And Weekday(BeginDate ) <>
vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If

BeginDate = BeginDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

Thanks so much.
Click to expand...
Click to expand...
Click to expand...
 
R

RJF

Hi Graham,

It's working perfectly. You are the best!

I can't thank you enough.

It's not necessary to calculate it to the nearest second, but thank you
anyway.

Thanks,

-Rachel


--
RJF


Graham Mandeno said:
Hi Rachel

I'm sorry about that - I didn't allow for the case of the start and end
times being on the same day. DUH!

Public Function WorkingMinutes(StartTime As Date, EndTime As Date) As Long
Const StartOfDay = #8:00:00 AM#
Const EndOfDay = #5:00:00 PM#
If DateValue(StartTime) = DateValue(EndTime) Then
WorkingMinutes = DateDiff("n", StartTime, EndTime)
Else
WorkingMinutes = _
DateDiff("n", StartTime, DateValue(StartTime) + EndOfDay) _
+ DateDiff("n", DateValue(EndTime) + StartOfDay, EndTime) _
+ WorkingDays2(StartTime, EndTime) * DateDiff("n", StartOfDay, EndOfDay)
End If
End Function

Also, the following modified format function will ensure you always get two
digits for the minutes:

Public Function FormatHHMM(Minutes As Long) As String
FormatHHMM = Int(Minutes / 60) & ":" & Format(Minutes Mod 60, "00")
End Function

I note in your example you have a value for the seconds (1/7/2008 11:03:32
AM). Do you want to record times to the nearest second? If so then a minor
modification will be required.
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand


RJF said:
Hi Graham,

Thank you so much for the help and the quick response. I'm not very
experienced with this so forgive me for not understanding it all.

I created modules with the Functions that you gave me.

Then I called the last 2 in my query (even though I only need the last
one).
Here's my query:

SELECT Mid([ops_name],7,99) AS Name, ops_vendors.name AS Vendor,
ops_equipment.description AS Machine, UCase([problem]) AS Problem1,
ops_equipment.serial_no AS [Serial No], ops_equipment.service_id AS
[Service
ID], ops_service_log.BeginDate AS [Open Date], ops_service_log.EndDate,
WorkingMinutes([BeginDate],[EndDate]) AS Minutes, FormatHHMM([Minutes]) AS
[Hours Open]
FROM (vw_vhb_combine_accounts_distinct_DB INNER JOIN ((ops_service_log
INNER
JOIN ops_equipment ON ops_service_log.equip_id = ops_equipment.equip_id)
INNER JOIN ops_vendors ON ops_equipment.vendor_id = ops_vendors.vendor_id)
ON
vw_vhb_combine_accounts_distinct_DB.sub_acctno = ops_equipment.acctno)
INNER
JOIN ops_param ON ops_equipment.acctno = ops_param.acctno
WHERE (((ops_service_log.BeginDate) Between #1/1/2008# And #8/29/2008#)
AND
((vw_vhb_combine_accounts_distinct_DB.main_acctno)="191322"))
ORDER BY Mid([ops_name],7,99), ops_vendors.name,
ops_equipment.description,
ops_equipment.serial_no, ops_equipment.service_id,
ops_service_log.BeginDate;

The result is not giving the correct hours and minutes.

For example:

1/7/2008 11:03:32 AM to 1/7/2008 3:03:00 PM
is giving me 13:0

Can you tell what I am doing wrong?

Again, thank you so much.

-Rachel

--
RJF


Graham Mandeno said:
Hi RJF

I think the best approach is to perform the calculation in the smallest
unit
you want to show (in your case, minutes) and then format the result.

I suggest you first modify your function to give you the number of *full*
work days, excluding the start and end dates. The following changes
should
do the trick:

Public Function WorkingDays2(ByVal BeginDate As Date, _
ByVal EndDate As Date) As Integer
...
BeginDate = DateValue(BeginDate) + 1
EndDate = DateValue(EndDate)

intCount = 0

Do While BeginDate < EndDate
...

Note:
1. ByVal ensures that any changes made to the date variables in the
function don't affect the values that were passed.
2. DateValue() discards the time component of a date/time value
3. Changing <= to < excludes the last day

You can then calculate the number of minutes on the start day and on the
end
day, and add the result of your function multiplied by the number of
minutes
in a working day:

Public Function WorkingMinutes(StartTime as Date, EndTime as Date) as
Long
Const StartOfDay = #08:00:00 AM#
Const EndOfDay = #05:00:00 PM#
WorkingMinutes = _
DateDiff("n", StartTime, DateValue(StartTime) + EndOfDay) _
+ DateDiff("n", DateValue(EndTime) + StartOfDay, EndTime) _
+ WorkingDays2(StartTime, EndTime) * DateDiff("n", StartOfDay,
EndOfDay)
End Function


To format the minutes as hh:mm, use this function:

Public Function FormatHHMM(Minutes as Long) as String
FormatHHMM = Int(Minutes / 60) & ":" & (Minutes Mod 60)
End Function
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks to this forum, I have the Function below in my database that
calculates the difference between 2 dates.

It calculates how many days between the 2 dates (minus weekend and
holidays), but I need to calculate the Hours and Minutes (i.e. 4:15 is
4
hours and 15 minutes).

If BeginDate is 7/22/2008 8:17:00 AM and EndDate is 7/23/2008 8:17:00
AM,
I need it to show how many hours between
7/22/2008 8:17:00 AM to 7/22/2008 5:00:00 PM
then add those hours to the hours between
7/23/2008 8:00:00 AM and 7/23/2008 8:17:00 AM.
So the result would be 9:00.

If this is something that is very difficult to do, I will be putting it
on
the back burner for a while. In other words, no rush but I would
appreciate
any help.

I hope I've explained myself clearly enough.

Public Function WorkingDays2(BeginDate As Date, EndDate As Date) As
Integer
'....................................................................
' Name: WorkingDays
' Inputs: BeginDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between
them
' Note that this function has been modified to account for holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

BeginDate = BeginDate + 1
'To count BeginDate as the 1st day comment out the line above

intCount = 0

Do While BeginDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & BeginDate & "#"
If Weekday(BeginDate ) <> vbSunday And Weekday(BeginDate ) <>
vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If

BeginDate = BeginDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

Thanks so much.
Click to expand...
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

Holiday day tracking 2
Holiday - Error 4
#Error in Results 5
Working Day calculations 1
Calculating Dates 3
What Code and where do I put it to Nest Loop 1
"working days" function (part days) 2
Calculate businness Days 1

Top