Expression with business days?

[Guest]

Hi,

I was struggling to create an expression that would show the last working
day of the next month. Or do any date calculation using only working days. I
don't now if it exist and would appreciate if someone could help me.

 

[Guest]

To find the last day of a month, add one to the month and set the day to 1.
This gives you the first day of the next month. Then subtract one from the
day. You must use 'dateadd' function to do all adds/subtracts on dates.
Then you will need a loop to loop backwards looking for a non-weekend day
(use 'datepart' function with 'w' parameter). Someone may have a better way.
If by 'working days' you have to exclude holidays, your problem is much more
difficult.
Look up 'date functions' in Access HELP.

-Dorian

 

[Guest]

I was able to get the last date but 'a loop to loop backwards' is not clear.
if you could give a bit more details on that. Thank you.

 

[Guest]

First, here is a function that counts the number of working days between two
dates. You will want to have a Holidays table to enter non working weekdays
in. For this function, it is called tblHolidays. It has two fields HolDate
(The Date of the Holiday) and HolDescription (Plain Text to show what holiday
it is.) The description isn't used in this function.

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "holidays", "[holdate] between
#" _
& dtmStart & "# And #" & dtmEnd & "#")

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function

Now, here is a function using the same table that determines if any given
date is a work day:

Public Function IsWorkDay(dtmSomeDay As Date) As Boolean
Dim blnWorkingDay
blnWorkingDay = Weekday(dtmSomeDay, vbMonday) < 6
If blnWorkingDay Then
blnWorkingDay = IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmSomeDay & "#"))
End If
IsWorkDay = blnWorkingDay
End Function

So, using a variation of mscertified's suggestion on finding the first day
of the next month:

dtmFirstOfMonth = DateSerial(Year(Date),Month(Date)+1,1)

And the other functions above, you should be able to get what you want. Now
if you need to know the first working day of the next month, this function
will do it:

Public Function NextWorkDay(dtmSomeDay As Date)
Dim dtmDayOff As Date
dtmSomeDay = DateAdd("d", 1, dtmSomeDay)
Do Until IsWorkDay(dtmSomeDay)
dtmSomeDay = DateAdd("d", 1, dtmSomeDay)
Loop
NextWorkDay = dtmSomeDay
End Function

 

[Guest]

That’s awesome, thank you a lot. If I want to execute those functions on the
form or report can I do in the Control Source of the control or I need to do
it in the MBV view and if it’s that how I do it?
You gave me function for the first working day of next month, but I need the
last working day of the month.
Thank you

First, here is a function that counts the number of working days between two
dates. You will want to have a Holidays table to enter non working weekdays
in. For this function, it is called tblHolidays. It has two fields HolDate
(The Date of the Holiday) and HolDescription (Plain Text to show what holiday
it is.) The description isn't used in this function.

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "holidays", "[holdate] between
#" _
& dtmStart & "# And #" & dtmEnd & "#")

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function

Now, here is a function using the same table that determines if any given
date is a work day:

Public Function IsWorkDay(dtmSomeDay As Date) As Boolean
Dim blnWorkingDay
blnWorkingDay = Weekday(dtmSomeDay, vbMonday) < 6
If blnWorkingDay Then
blnWorkingDay = IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmSomeDay & "#"))
End If
IsWorkDay = blnWorkingDay
End Function

So, using a variation of mscertified's suggestion on finding the first day
of the next month:

dtmFirstOfMonth = DateSerial(Year(Date),Month(Date)+1,1)

And the other functions above, you should be able to get what you want. Now
if you need to know the first working day of the next month, this function
will do it:

Public Function NextWorkDay(dtmSomeDay As Date)
Dim dtmDayOff As Date
dtmSomeDay = DateAdd("d", 1, dtmSomeDay)
Do Until IsWorkDay(dtmSomeDay)
dtmSomeDay = DateAdd("d", 1, dtmSomeDay)
Loop
NextWorkDay = dtmSomeDay
End Function
--
Dave Hargis, Microsoft Access MVP

Hi,

I was struggling to create an expression that would show the last working
day of the next month. Or do any date calculation using only working days. I
don't now if it exist and would appreciate if someone could help me.

 

[Guest]

Oh, I thought it was first day.

I would put all those functions in a standard module. I have those and
others in a module named modDateFunctions.

You could use the control source of a control, but it would depend on how
you need it to work.

Here is a modified function that counts backward looking for a work day:

Public Function LastWorkDay(dtmSomeDay As Date)

'Uncomment the line below if you don't want to consider the date received
' dtmSomeDay = DateAdd("d", -1, dtmSomeDay)
Do Until IsWorkDay(dtmSomeDay)
dtmSomeDay = DateAdd("d", -1, dtmSomeDay)
Loop
LastWorkDay = dtmSomeDay
End Function

And since you want the Last Work Day, you can find the last day of the month
of the next month like this:

dtmLastWorkDay = DateSerial(Year(Date), Month(Date) + 2, 0)

The 0 causes it to return 6/30/2007 (based on today's date)

Then calling the function:

=LastWorkDay(dtmLastWorkDay)

will return 6/29/2007 which is a Friday.

It may work like this if you want to use it in your control source, but I
haven't tested it:
=LastWorkDay(DateSerial(Year(Date), Month(Date) + 2, 0))

--
Dave Hargis, Microsoft Access MVP

That’s awesome, thank you a lot. If I want to execute those functions on the
form or report can I do in the Control Source of the control or I need to do
it in the MBV view and if it’s that how I do it?
You gave me function for the first working day of next month, but I need the
last working day of the month.
Thank you

First, here is a function that counts the number of working days between two
dates. You will want to have a Holidays table to enter non working weekdays
in. For this function, it is called tblHolidays. It has two fields HolDate
(The Date of the Holiday) and HolDescription (Plain Text to show what holiday
it is.) The description isn't used in this function.

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "holidays", "[holdate] between
#" _
& dtmStart & "# And #" & dtmEnd & "#")

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function

Now, here is a function using the same table that determines if any given
date is a work day:

Public Function IsWorkDay(dtmSomeDay As Date) As Boolean
Dim blnWorkingDay
blnWorkingDay = Weekday(dtmSomeDay, vbMonday) < 6
If blnWorkingDay Then
blnWorkingDay = IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmSomeDay & "#"))
End If
IsWorkDay = blnWorkingDay
End Function

So, using a variation of mscertified's suggestion on finding the first day
of the next month:

dtmFirstOfMonth = DateSerial(Year(Date),Month(Date)+1,1)

And the other functions above, you should be able to get what you want. Now
if you need to know the first working day of the next month, this function
will do it:

Public Function NextWorkDay(dtmSomeDay As Date)
Dim dtmDayOff As Date
dtmSomeDay = DateAdd("d", 1, dtmSomeDay)
Do Until IsWorkDay(dtmSomeDay)
dtmSomeDay = DateAdd("d", 1, dtmSomeDay)
Loop
NextWorkDay = dtmSomeDay
End Function
--
Dave Hargis, Microsoft Access MVP

Hi,

I was struggling to create an expression that would show the last working
day of the next month. Or do any date calculation using only working days. I
don't now if it exist and would appreciate if someone could help me.

 

[Guest]

What about that tblHolidays.. How should i populate it? For all historical
holidays and all future once too? or there is a way to have it repeated every
year automatically?
I appreciate your help a lot.

Oh, I thought it was first day.

I would put all those functions in a standard module. I have those and
others in a module named modDateFunctions.

You could use the control source of a control, but it would depend on how
you need it to work.

Here is a modified function that counts backward looking for a work day:

Public Function LastWorkDay(dtmSomeDay As Date)

'Uncomment the line below if you don't want to consider the date received
' dtmSomeDay = DateAdd("d", -1, dtmSomeDay)
Do Until IsWorkDay(dtmSomeDay)
dtmSomeDay = DateAdd("d", -1, dtmSomeDay)
Loop
LastWorkDay = dtmSomeDay
End Function

And since you want the Last Work Day, you can find the last day of the month
of the next month like this:

dtmLastWorkDay = DateSerial(Year(Date), Month(Date) + 2, 0)

The 0 causes it to return 6/30/2007 (based on today's date)

Then calling the function:

=LastWorkDay(dtmLastWorkDay)

will return 6/29/2007 which is a Friday.

It may work like this if you want to use it in your control source, but I
haven't tested it:
=LastWorkDay(DateSerial(Year(Date), Month(Date) + 2, 0))

--
Dave Hargis, Microsoft Access MVP

That’s awesome, thank you a lot. If I want to execute those functions on the
form or report can I do in the Control Source of the control or I need to do
it in the MBV view and if it’s that how I do it?
You gave me function for the first working day of next month, but I need the
last working day of the month.
Thank you

First, here is a function that counts the number of working days between two
dates. You will want to have a Holidays table to enter non working weekdays
in. For this function, it is called tblHolidays. It has two fields HolDate
(The Date of the Holiday) and HolDescription (Plain Text to show what holiday
it is.) The description isn't used in this function.

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "holidays", "[holdate] between
#" _
& dtmStart & "# And #" & dtmEnd & "#")

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function

Now, here is a function using the same table that determines if any given
date is a work day:

Public Function IsWorkDay(dtmSomeDay As Date) As Boolean
Dim blnWorkingDay
blnWorkingDay = Weekday(dtmSomeDay, vbMonday) < 6
If blnWorkingDay Then
blnWorkingDay = IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmSomeDay & "#"))
End If
IsWorkDay = blnWorkingDay
End Function

So, using a variation of mscertified's suggestion on finding the first day
of the next month:

dtmFirstOfMonth = DateSerial(Year(Date),Month(Date)+1,1)

And the other functions above, you should be able to get what you want. Now
if you need to know the first working day of the next month, this function
will do it:

Public Function NextWorkDay(dtmSomeDay As Date)
Dim dtmDayOff As Date
dtmSomeDay = DateAdd("d", 1, dtmSomeDay)
Do Until IsWorkDay(dtmSomeDay)
dtmSomeDay = DateAdd("d", 1, dtmSomeDay)
Loop
NextWorkDay = dtmSomeDay
End Function
--
Dave Hargis, Microsoft Access MVP


:

Hi,

I was struggling to create an expression that would show the last working
day of the next month. Or do any date calculation using only working days. I
don't now if it exist and would appreciate if someone could help me.

 

[Guest]

I don't know of anyway other than to key in the holidays by hand. Even if
someone had a universal holidays table will every conceivable holiday, it
would still take some adjusting because not everyone has the same holidays.

--
Dave Hargis, Microsoft Access MVP

What about that tblHolidays.. How should i populate it? For all historical
holidays and all future once too? or there is a way to have it repeated every
year automatically?
I appreciate your help a lot.

Oh, I thought it was first day.

I would put all those functions in a standard module. I have those and
others in a module named modDateFunctions.

You could use the control source of a control, but it would depend on how
you need it to work.

Here is a modified function that counts backward looking for a work day:

Public Function LastWorkDay(dtmSomeDay As Date)

'Uncomment the line below if you don't want to consider the date received
' dtmSomeDay = DateAdd("d", -1, dtmSomeDay)
Do Until IsWorkDay(dtmSomeDay)
dtmSomeDay = DateAdd("d", -1, dtmSomeDay)
Loop
LastWorkDay = dtmSomeDay
End Function

And since you want the Last Work Day, you can find the last day of the month
of the next month like this:

dtmLastWorkDay = DateSerial(Year(Date), Month(Date) + 2, 0)

The 0 causes it to return 6/30/2007 (based on today's date)

Then calling the function:

=LastWorkDay(dtmLastWorkDay)

will return 6/29/2007 which is a Friday.

It may work like this if you want to use it in your control source, but I
haven't tested it:
=LastWorkDay(DateSerial(Year(Date), Month(Date) + 2, 0))

--
Dave Hargis, Microsoft Access MVP

That’s awesome, thank you a lot. If I want to execute those functions on the
form or report can I do in the Control Source of the control or I need to do
it in the MBV view and if it’s that how I do it?
You gave me function for the first working day of next month, but I need the
last working day of the month.
Thank you


:

First, here is a function that counts the number of working days between two
dates. You will want to have a Holidays table to enter non working weekdays
in. For this function, it is called tblHolidays. It has two fields HolDate
(The Date of the Holiday) and HolDescription (Plain Text to show what holiday
it is.) The description isn't used in this function.

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "holidays", "[holdate] between
#" _
& dtmStart & "# And #" & dtmEnd & "#")

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function

Now, here is a function using the same table that determines if any given
date is a work day:

Public Function IsWorkDay(dtmSomeDay As Date) As Boolean
Dim blnWorkingDay
blnWorkingDay = Weekday(dtmSomeDay, vbMonday) < 6
If blnWorkingDay Then
blnWorkingDay = IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmSomeDay & "#"))
End If
IsWorkDay = blnWorkingDay
End Function

So, using a variation of mscertified's suggestion on finding the first day
of the next month:

dtmFirstOfMonth = DateSerial(Year(Date),Month(Date)+1,1)

And the other functions above, you should be able to get what you want. Now
if you need to know the first working day of the next month, this function
will do it:

Public Function NextWorkDay(dtmSomeDay As Date)
Dim dtmDayOff As Date
dtmSomeDay = DateAdd("d", 1, dtmSomeDay)
Do Until IsWorkDay(dtmSomeDay)
dtmSomeDay = DateAdd("d", 1, dtmSomeDay)
Loop
NextWorkDay = dtmSomeDay
End Function
--
Dave Hargis, Microsoft Access MVP


:

Hi,

I was struggling to create an expression that would show the last working
day of the next month. Or do any date calculation using only working days. I
don't now if it exist and would appreciate if someone could help me.

 

[Guest]

Thank you, was just wondering

I don't know of anyway other than to key in the holidays by hand. Even if
someone had a universal holidays table will every conceivable holiday, it
would still take some adjusting because not everyone has the same holidays.

--
Dave Hargis, Microsoft Access MVP

What about that tblHolidays.. How should i populate it? For all historical
holidays and all future once too? or there is a way to have it repeated every
year automatically?
I appreciate your help a lot.

Oh, I thought it was first day.

I would put all those functions in a standard module. I have those and
others in a module named modDateFunctions.

You could use the control source of a control, but it would depend on how
you need it to work.

Here is a modified function that counts backward looking for a work day:

Public Function LastWorkDay(dtmSomeDay As Date)

'Uncomment the line below if you don't want to consider the date received
' dtmSomeDay = DateAdd("d", -1, dtmSomeDay)
Do Until IsWorkDay(dtmSomeDay)
dtmSomeDay = DateAdd("d", -1, dtmSomeDay)
Loop
LastWorkDay = dtmSomeDay
End Function

And since you want the Last Work Day, you can find the last day of the month
of the next month like this:

dtmLastWorkDay = DateSerial(Year(Date), Month(Date) + 2, 0)

The 0 causes it to return 6/30/2007 (based on today's date)

Then calling the function:

=LastWorkDay(dtmLastWorkDay)

will return 6/29/2007 which is a Friday.

It may work like this if you want to use it in your control source, but I
haven't tested it:
=LastWorkDay(DateSerial(Year(Date), Month(Date) + 2, 0))

--
Dave Hargis, Microsoft Access MVP


:

That’s awesome, thank you a lot. If I want to execute those functions on the
form or report can I do in the Control Source of the control or I need to do
it in the MBV view and if it’s that how I do it?
You gave me function for the first working day of next month, but I need the
last working day of the month.
Thank you


:

First, here is a function that counts the number of working days between two
dates. You will want to have a Holidays table to enter non working weekdays
in. For this function, it is called tblHolidays. It has two fields HolDate
(The Date of the Holiday) and HolDescription (Plain Text to show what holiday
it is.) The description isn't used in this function.

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "holidays", "[holdate] between
#" _
& dtmStart & "# And #" & dtmEnd & "#")

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function

Now, here is a function using the same table that determines if any given
date is a work day:

Public Function IsWorkDay(dtmSomeDay As Date) As Boolean
Dim blnWorkingDay
blnWorkingDay = Weekday(dtmSomeDay, vbMonday) < 6
If blnWorkingDay Then
blnWorkingDay = IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmSomeDay & "#"))
End If
IsWorkDay = blnWorkingDay
End Function

So, using a variation of mscertified's suggestion on finding the first day
of the next month:

dtmFirstOfMonth = DateSerial(Year(Date),Month(Date)+1,1)

And the other functions above, you should be able to get what you want. Now
if you need to know the first working day of the next month, this function
will do it:

Public Function NextWorkDay(dtmSomeDay As Date)
Dim dtmDayOff As Date
dtmSomeDay = DateAdd("d", 1, dtmSomeDay)
Do Until IsWorkDay(dtmSomeDay)
dtmSomeDay = DateAdd("d", 1, dtmSomeDay)
Loop
NextWorkDay = dtmSomeDay
End Function
--
Dave Hargis, Microsoft Access MVP


:

Hi,

I was struggling to create an expression that would show the last working
day of the next month. Or do any date calculation using only working days. I
don't now if it exist and would appreciate if someone could help me.

 

[Douglas J. Steele]

Check my June, 2005 "Access Answers" column in Pinnacle Publication's "Smart
Access" (it's a followup to my September, 2004 column).

You can download both columns (and their associated sample databases) for
free at http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I don't know of anyway other than to key in the holidays by hand. Even if
someone had a universal holidays table will every conceivable holiday, it
would still take some adjusting because not everyone has the same
holidays.

--
Dave Hargis, Microsoft Access MVP

What about that tblHolidays.. How should i populate it? For all
historical
holidays and all future once too? or there is a way to have it repeated
every
year automatically?
I appreciate your help a lot.

Oh, I thought it was first day.

I would put all those functions in a standard module. I have those and
others in a module named modDateFunctions.

You could use the control source of a control, but it would depend on
how
you need it to work.

Here is a modified function that counts backward looking for a work
day:

Public Function LastWorkDay(dtmSomeDay As Date)

'Uncomment the line below if you don't want to consider the date
received
' dtmSomeDay = DateAdd("d", -1, dtmSomeDay)
Do Until IsWorkDay(dtmSomeDay)
dtmSomeDay = DateAdd("d", -1, dtmSomeDay)
Loop
LastWorkDay = dtmSomeDay
End Function

And since you want the Last Work Day, you can find the last day of the
month
of the next month like this:

dtmLastWorkDay = DateSerial(Year(Date), Month(Date) + 2, 0)

The 0 causes it to return 6/30/2007 (based on today's date)

Then calling the function:

=LastWorkDay(dtmLastWorkDay)

will return 6/29/2007 which is a Friday.

It may work like this if you want to use it in your control source, but
I
haven't tested it:
=LastWorkDay(DateSerial(Year(Date), Month(Date) + 2, 0))

--
Dave Hargis, Microsoft Access MVP


:

That's awesome, thank you a lot. If I want to execute those functions
on the
form or report can I do in the Control Source of the control or I
need to do
it in the MBV view and if it's that how I do it?
You gave me function for the first working day of next month, but I
need the
last working day of the month.
Thank you


:

First, here is a function that counts the number of working days
between two
dates. You will want to have a Holidays table to enter non working
weekdays
in. For this function, it is called tblHolidays. It has two
fields HolDate
(The Date of the Holiday) and HolDescription (Plain Text to show
what holiday
it is.) The description isn't used in this function.

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "holidays",
"[holdate] between
#" _
& dtmStart & "# And #" & dtmEnd & "#")

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function

Now, here is a function using the same table that determines if any
given
date is a work day:

Public Function IsWorkDay(dtmSomeDay As Date) As Boolean
Dim blnWorkingDay
blnWorkingDay = Weekday(dtmSomeDay, vbMonday) < 6
If blnWorkingDay Then
blnWorkingDay = IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmSomeDay & "#"))
End If
IsWorkDay = blnWorkingDay
End Function

So, using a variation of mscertified's suggestion on finding the
first day
of the next month:

dtmFirstOfMonth = DateSerial(Year(Date),Month(Date)+1,1)

And the other functions above, you should be able to get what you
want. Now
if you need to know the first working day of the next month, this
function
will do it:

Public Function NextWorkDay(dtmSomeDay As Date)
Dim dtmDayOff As Date
dtmSomeDay = DateAdd("d", 1, dtmSomeDay)
Do Until IsWorkDay(dtmSomeDay)
dtmSomeDay = DateAdd("d", 1, dtmSomeDay)
Loop
NextWorkDay = dtmSomeDay
End Function
--
Dave Hargis, Microsoft Access MVP


:

Hi,

I was struggling to create an expression that would show the last
working
day of the next month. Or do any date calculation using only
working days. I
don't now if it exist and would appreciate if someone could help
me.

 

[Guest]

I'm using those functions you gave me very extensively, thank you a lot.
Now I have another problem.

I am building form that displays data to compare two years. Some of the
controls are calculated some - are using DLookUp.
One of the controls has to be a cumulative percentage for the year. So I'll
have to refer to the data that is not on the form. I think it's better to do
this calculation in the VB screen but have very limited knowledge on how to
use it.

So, if it were the number for just January 2006 month, the formula in the
DateSource would look like that:
=(DLookUp("[MaxOfSumOfaccount_0_99]","[Query1]","[Date]=[StartDate]"))/(DLookUp("[Stmts_mailed]","[Stmts_mailed]","[Date]=[StartDate]"))
It's actually working and brings me the right value.

To find this value for the month of February I would use

=(DLookUp("[MaxOfSumOfaccount_0_99]","[Query1]","[Date]=LastWorkDay(DateSerial(Year([StartDate]),Month([StartDate])-1,0))"))/(DLookUp("[Stmts_mailed]","[Stmts_mailed]","[Date]=LastWorkDay(DateSerial(Year([StartDate]),Month([StartDate])-1,0))"))

But for some reason it's not working and don’t bring me any results not even
‘error’
And eventually, I don't need to see those numbers for each of the month I
just need to see the summary of those numbers for 12 previous months (going
backwards from [StartDate] which can be chosen through Calendar Active
control).

If some one could help me in this mess I would be happy.
Thanks

I don't know of anyway other than to key in the holidays by hand. Even if
someone had a universal holidays table will every conceivable holiday, it
would still take some adjusting because not everyone has the same holidays.

--
Dave Hargis, Microsoft Access MVP

What about that tblHolidays.. How should i populate it? For all historical
holidays and all future once too? or there is a way to have it repeated every
year automatically?
I appreciate your help a lot.

Oh, I thought it was first day.

I would put all those functions in a standard module. I have those and
others in a module named modDateFunctions.

You could use the control source of a control, but it would depend on how
you need it to work.

Here is a modified function that counts backward looking for a work day:

Public Function LastWorkDay(dtmSomeDay As Date)

'Uncomment the line below if you don't want to consider the date received
' dtmSomeDay = DateAdd("d", -1, dtmSomeDay)
Do Until IsWorkDay(dtmSomeDay)
dtmSomeDay = DateAdd("d", -1, dtmSomeDay)
Loop
LastWorkDay = dtmSomeDay
End Function

And since you want the Last Work Day, you can find the last day of the month
of the next month like this:

dtmLastWorkDay = DateSerial(Year(Date), Month(Date) + 2, 0)

The 0 causes it to return 6/30/2007 (based on today's date)

Then calling the function:

=LastWorkDay(dtmLastWorkDay)

will return 6/29/2007 which is a Friday.

It may work like this if you want to use it in your control source, but I
haven't tested it:
=LastWorkDay(DateSerial(Year(Date), Month(Date) + 2, 0))

--
Dave Hargis, Microsoft Access MVP


:

That’s awesome, thank you a lot. If I want to execute those functions on the
form or report can I do in the Control Source of the control or I need to do
it in the MBV view and if it’s that how I do it?
You gave me function for the first working day of next month, but I need the
last working day of the month.
Thank you


:

First, here is a function that counts the number of working days between two
dates. You will want to have a Holidays table to enter non working weekdays
in. For this function, it is called tblHolidays. It has two fields HolDate
(The Date of the Holiday) and HolDescription (Plain Text to show what holiday
it is.) The description isn't used in this function.

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "holidays", "[holdate] between
#" _
& dtmStart & "# And #" & dtmEnd & "#")

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function

Now, here is a function using the same table that determines if any given
date is a work day:

Public Function IsWorkDay(dtmSomeDay As Date) As Boolean
Dim blnWorkingDay
blnWorkingDay = Weekday(dtmSomeDay, vbMonday) < 6
If blnWorkingDay Then
blnWorkingDay = IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmSomeDay & "#"))
End If
IsWorkDay = blnWorkingDay
End Function

So, using a variation of mscertified's suggestion on finding the first day
of the next month:

dtmFirstOfMonth = DateSerial(Year(Date),Month(Date)+1,1)

And the other functions above, you should be able to get what you want. Now
if you need to know the first working day of the next month, this function
will do it:

Public Function NextWorkDay(dtmSomeDay As Date)
Dim dtmDayOff As Date
dtmSomeDay = DateAdd("d", 1, dtmSomeDay)
Do Until IsWorkDay(dtmSomeDay)
dtmSomeDay = DateAdd("d", 1, dtmSomeDay)
Loop
NextWorkDay = dtmSomeDay
End Function
--
Dave Hargis, Microsoft Access MVP


:

Hi,

I was struggling to create an expression that would show the last working
day of the next month. Or do any date calculation using only working days. I
don't now if it exist and would appreciate if someone could help me.