weekends and holidays!

G

Guest

I have attempted to use the second function here:

http://www.mvps.org/access/datetime/date0006.htm

________________________________________________________________

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer

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)

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

intCount = 0

Do While StartDate <= EndDate

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

StartDate = StartDate + 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

________________________________________________________________


I have created a table called "tblholidays" and entered two dates for two
public holidays in "holidaydate" being 06/04/2007 and 09/04/2007

In the query I have inserted the following:

DaysW1: WorkingDays2([SLA_Date1],[SLA_Date2])

For one row the two dates are 05/04/2007 and 10/04/2007

For this row I thought DaysW1 should equal 2 but it doesn't. It equals 4.
This suggests it is ignoring the weekend (7th and 8th) but not the public
holidays (6th and 9th)

A) Can anyone say why it is not ignoring the public holidays?
B) I don't want the total number of days. I want the difference so the
answer to the above should be 1.

cheers!
 
M

Michel Walsh

To me it returns 2, the fifth and the tenth are two days in the interval you
specified. As in "how many integers are with the limit of 1 and 3, the
answer is 3, (1, 2, 3) not 1. You can get up to exclusively the second date
supplied, by changing the <= to a strict < in the Do While line:

Do While StartDate < EndDate



I aslo had to change the line with the FindFirst, my setting was generating
an error, into:



rst.FindFirst "[HolidayDate] = #" & Format(StartDate, "mm-dd-yyyy") & "#"


Finally, if you still get 4 (or 3) as answer, may be you entered the
holidays as dd/mm/yyyy instead of mm/dd/yyyy, so, having no holiday in the
interval you supplied, the answer was technically correct, given the stored
data (rather than the intended data).



Hoping it may help,
Vanderghast, Access MVP
 
G

Guest

Its working fine since I changed the holiday date format to "mm/dd/yyyy".
How can I make the function work to cope with "dd/mm/yyyy" ??


Michel Walsh said:
To me it returns 2, the fifth and the tenth are two days in the interval you
specified. As in "how many integers are with the limit of 1 and 3, the
answer is 3, (1, 2, 3) not 1. You can get up to exclusively the second date
supplied, by changing the <= to a strict < in the Do While line:

Do While StartDate < EndDate



I aslo had to change the line with the FindFirst, my setting was generating
an error, into:



rst.FindFirst "[HolidayDate] = #" & Format(StartDate, "mm-dd-yyyy") & "#"


Finally, if you still get 4 (or 3) as answer, may be you entered the
holidays as dd/mm/yyyy instead of mm/dd/yyyy, so, having no holiday in the
interval you supplied, the answer was technically correct, given the stored
data (rather than the intended data).



Hoping it may help,
Vanderghast, Access MVP



scubadiver said:
I have attempted to use the second function here:

http://www.mvps.org/access/datetime/date0006.htm

________________________________________________________________

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As
Integer

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)

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

intCount = 0

Do While StartDate <= EndDate

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

StartDate = StartDate + 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

________________________________________________________________


I have created a table called "tblholidays" and entered two dates for two
public holidays in "holidaydate" being 06/04/2007 and 09/04/2007

In the query I have inserted the following:

DaysW1: WorkingDays2([SLA_Date1],[SLA_Date2])

For one row the two dates are 05/04/2007 and 10/04/2007

For this row I thought DaysW1 should equal 2 but it doesn't. It equals 4.
This suggests it is ignoring the weekend (7th and 8th) but not the public
holidays (6th and 9th)

A) Can anyone say why it is not ignoring the public holidays?
B) I don't want the total number of days. I want the difference so the
answer to the above should be 1.

cheers!
Click to expand...
Click to expand...
 
G

Guest

I didn't think "mm/dd/yyyy" would be a problem until I started entering
dates into a form. The format is automatically switched to "dd/mm/yyyy".

So either I change the function to suit "dd/mm/yyyy" or I somehow change the
form input to suit "mm/dd/yyyy".

Michel Walsh said:
To me it returns 2, the fifth and the tenth are two days in the interval you
specified. As in "how many integers are with the limit of 1 and 3, the
answer is 3, (1, 2, 3) not 1. You can get up to exclusively the second date
supplied, by changing the <= to a strict < in the Do While line:

Do While StartDate < EndDate



I aslo had to change the line with the FindFirst, my setting was generating
an error, into:



rst.FindFirst "[HolidayDate] = #" & Format(StartDate, "mm-dd-yyyy") & "#"


Finally, if you still get 4 (or 3) as answer, may be you entered the
holidays as dd/mm/yyyy instead of mm/dd/yyyy, so, having no holiday in the
interval you supplied, the answer was technically correct, given the stored
data (rather than the intended data).



Hoping it may help,
Vanderghast, Access MVP



scubadiver said:
I have attempted to use the second function here:

http://www.mvps.org/access/datetime/date0006.htm

________________________________________________________________

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As
Integer

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)

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

intCount = 0

Do While StartDate <= EndDate

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

StartDate = StartDate + 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

________________________________________________________________


I have created a table called "tblholidays" and entered two dates for two
public holidays in "holidaydate" being 06/04/2007 and 09/04/2007

In the query I have inserted the following:

DaysW1: WorkingDays2([SLA_Date1],[SLA_Date2])

For one row the two dates are 05/04/2007 and 10/04/2007

For this row I thought DaysW1 should equal 2 but it doesn't. It equals 4.
This suggests it is ignoring the weekend (7th and 8th) but not the public
holidays (6th and 9th)

A) Can anyone say why it is not ignoring the public holidays?
B) I don't want the total number of days. I want the difference so the
answer to the above should be 1.

cheers!
Click to expand...
Click to expand...
 
M

Michel Walsh

Enter the date in the format you mentioned as your preferred date format (in
the regional settings).

Modify the function to FORCE the AMERICAN format (ie, mm-dd-yyyy). In the
function, FindFirst method, at that point, assumes it is US format, while
when you enter data, Access assumes, by default, you do it into your
preferred data format.

Personally, my preferred date format is yyyy.mm.dd (the two dots format,
which is also referred as the international format), so I had to change the
function to work properly which runs perfectly, without modification, if
your preferred format is US, but not necessary as well otherwise (Ken, the
author of the function, probably tested his function only using US regional
setting). So, again, enter the date in YOUR preferred format, and FORCE the
function to use the US format.



Vanderghast, Access MVP

scubadiver said:
I didn't think "mm/dd/yyyy" would be a problem until I started entering
dates into a form. The format is automatically switched to "dd/mm/yyyy".

So either I change the function to suit "dd/mm/yyyy" or I somehow change
the
form input to suit "mm/dd/yyyy".

Michel Walsh said:
To me it returns 2, the fifth and the tenth are two days in the interval
you
specified. As in "how many integers are with the limit of 1 and 3, the
answer is 3, (1, 2, 3) not 1. You can get up to exclusively the second
date
supplied, by changing the <= to a strict < in the Do While line:

Do While StartDate < EndDate



I aslo had to change the line with the FindFirst, my setting was
generating
an error, into:



rst.FindFirst "[HolidayDate] = #" & Format(StartDate, "mm-dd-yyyy") & "#"


Finally, if you still get 4 (or 3) as answer, may be you entered the
holidays as dd/mm/yyyy instead of mm/dd/yyyy, so, having no holiday in
the
interval you supplied, the answer was technically correct, given the
stored
data (rather than the intended data).



Hoping it may help,
Vanderghast, Access MVP



scubadiver said:
I have attempted to use the second function here:

http://www.mvps.org/access/datetime/date0006.htm

________________________________________________________________

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As
Integer

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)

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

intCount = 0

Do While StartDate <= EndDate

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

StartDate = StartDate + 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

________________________________________________________________


I have created a table called "tblholidays" and entered two dates for
two
public holidays in "holidaydate" being 06/04/2007 and 09/04/2007

In the query I have inserted the following:

DaysW1: WorkingDays2([SLA_Date1],[SLA_Date2])

For one row the two dates are 05/04/2007 and 10/04/2007

For this row I thought DaysW1 should equal 2 but it doesn't. It equals
4.
This suggests it is ignoring the weekend (7th and 8th) but not the
public
holidays (6th and 9th)

A) Can anyone say why it is not ignoring the public holidays?
B) I don't want the total number of days. I want the difference so the
answer to the above should be 1.

cheers!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I've sorted it out.

thanks


Michel Walsh said:
Enter the date in the format you mentioned as your preferred date format (in
the regional settings).

Modify the function to FORCE the AMERICAN format (ie, mm-dd-yyyy). In the
function, FindFirst method, at that point, assumes it is US format, while
when you enter data, Access assumes, by default, you do it into your
preferred data format.

Personally, my preferred date format is yyyy.mm.dd (the two dots format,
which is also referred as the international format), so I had to change the
function to work properly which runs perfectly, without modification, if
your preferred format is US, but not necessary as well otherwise (Ken, the
author of the function, probably tested his function only using US regional
setting). So, again, enter the date in YOUR preferred format, and FORCE the
function to use the US format.



Vanderghast, Access MVP

scubadiver said:
I didn't think "mm/dd/yyyy" would be a problem until I started entering
dates into a form. The format is automatically switched to "dd/mm/yyyy".

So either I change the function to suit "dd/mm/yyyy" or I somehow change
the
form input to suit "mm/dd/yyyy".

Michel Walsh said:
To me it returns 2, the fifth and the tenth are two days in the interval
you
specified. As in "how many integers are with the limit of 1 and 3, the
answer is 3, (1, 2, 3) not 1. You can get up to exclusively the second
date
supplied, by changing the <= to a strict < in the Do While line:

Do While StartDate < EndDate



I aslo had to change the line with the FindFirst, my setting was
generating
an error, into:



rst.FindFirst "[HolidayDate] = #" & Format(StartDate, "mm-dd-yyyy") & "#"


Finally, if you still get 4 (or 3) as answer, may be you entered the
holidays as dd/mm/yyyy instead of mm/dd/yyyy, so, having no holiday in
the
interval you supplied, the answer was technically correct, given the
stored
data (rather than the intended data).



Hoping it may help,
Vanderghast, Access MVP



I have attempted to use the second function here:

http://www.mvps.org/access/datetime/date0006.htm

________________________________________________________________

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As
Integer

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)

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

intCount = 0

Do While StartDate <= EndDate

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

StartDate = StartDate + 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

________________________________________________________________


I have created a table called "tblholidays" and entered two dates for
two
public holidays in "holidaydate" being 06/04/2007 and 09/04/2007

In the query I have inserted the following:

DaysW1: WorkingDays2([SLA_Date1],[SLA_Date2])

For one row the two dates are 05/04/2007 and 10/04/2007

For this row I thought DaysW1 should equal 2 but it doesn't. It equals
4.
This suggests it is ignoring the weekend (7th and 8th) but not the
public
holidays (6th and 9th)

A) Can anyone say why it is not ignoring the public holidays?
B) I don't want the total number of days. I want the difference so the
answer to the above should be 1.

cheers!
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

data type mismatch error 3464 1
Viewing Totals/GroupBy creates Data Mismatch 3
Count days with integer 10
Calculating Net Working Days in a VB module 4
Default value of 5 WorkingDays after other control value 5
date compare results not working 7
Before, current, and ending units based on dates 2
adding work days to a begin date to derive the end date 1

Top