Hi,

I have a table of holidays and a date
I want to be able to add 4 working days excl. holidays to the given date

How can I do this?

Thanks

See Workday math at:
http://www.mvps.org/access/datetime/date0012.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"news.microsoft.com" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> I have a table of holidays and a date
> I want to be able to add 4 working days excl. holidays to the given date

Yes, there is.

You could create your table of holidays, DCount() the number found in the
date range you are interested in, and subtract from the result.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"news.microsoft.com" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Thanks for this
> Is there a way so that the function can accept a variable for the Holiday
> dates based on a table of hols.
>
> If yes, how would I go about this?
>
> Thanks
>
> Public Function dhAddWorkDaysA(lngDays As Long, _
> Optional dtmDate As Date = 0, _
> Optional adtmDates As Variant) As
> Dim lngCount As Long
> Dim dtmTemp As Date
>
> If dtmDate = 0 Then
> dtmDate = Date
> End If
>
> dtmTemp = dtmDate
> For lngCount = 1 To lngDays
> dtmTemp = dhNextWorkdayA(dtmTemp, adtmDates)
> Next lngCount dhAddWorkDaysA = dtmTemp
> End Function
> "Allen Browne" <(E-Mail Removed)> wrote in message
> news:#(E-Mail Removed)...
> > See Workday math at:
> > http://www.mvps.org/access/datetime/date0012.htm
> >
> > --
> > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > Tips for Access users - http://allenbrowne.com/tips.html
> > Reply to group, rather than allenbrowne at mvps dot org.
> >
> > "news.microsoft.com" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > >
> > > I have a table of holidays and a date
> > > I want to be able to add 4 working days excl. holidays to the given
date
> >
> >
>
>

Thanks for this
Is there a way so that the function can accept a variable for the Holiday
dates based on a table of hols.

If yes, how would I go about this?

Thanks

Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Date = 0, _
Optional adtmDates As Variant) As
Dim lngCount As Long
Dim dtmTemp As Date

If dtmDate = 0 Then
dtmDate = Date
End If

dtmTemp = dtmDate
For lngCount = 1 To lngDays
dtmTemp = dhNextWorkdayA(dtmTemp, adtmDates)
Next lngCount dhAddWorkDaysA = dtmTemp
End Function
"Allen Browne" <(E-Mail Removed)> wrote in message
news:#(E-Mail Removed)...
> See Workday math at:
> http://www.mvps.org/access/datetime/date0012.htm
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "news.microsoft.com" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >
> > I have a table of holidays and a date
> > I want to be able to add 4 working days excl. holidays to the given date
>
>

Thanks but what I am trying to do is find a date 4 working days from a given
date.
The function that you pointed me to works fine if I hard code all the years
holiday dates - obviously I don't want to do this because they are always
changing.
I can't see how your next suggestion will help
Please explain
Thanks again for your help
"Allen Browne" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Yes, there is.
>
> You could create your table of holidays, DCount() the number found in the
> date range you are interested in, and subtract from the result.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> "news.microsoft.com" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> > Thanks for this
> > Is there a way so that the function can accept a variable for the
Holiday
> > dates based on a table of hols.
> >
> > If yes, how would I go about this?
> >
> > Thanks
> >
> > Public Function dhAddWorkDaysA(lngDays As Long, _
> > Optional dtmDate As Date = 0, _
> > Optional adtmDates As Variant) As
> > Dim lngCount As Long
> > Dim dtmTemp As Date
> >
> > If dtmDate = 0 Then
> > dtmDate = Date
> > End If
> >
> > dtmTemp = dtmDate
> > For lngCount = 1 To lngDays
> > dtmTemp = dhNextWorkdayA(dtmTemp, adtmDates)
> > Next lngCount dhAddWorkDaysA = dtmTemp
> > End Function
> > "Allen Browne" <(E-Mail Removed)> wrote in message
> > news:#(E-Mail Removed)...
> > > See Workday math at:
> > > http://www.mvps.org/access/datetime/date0012.htm
> > >
> > > --
> > > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > > Tips for Access users - http://allenbrowne.com/tips.html
> > > Reply to group, rather than allenbrowne at mvps dot org.
> > >
> > > "news.microsoft.com" <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > > >
> > > > I have a table of holidays and a date
> > > > I want to be able to add 4 working days excl. holidays to the given
> date
> > >
> > >
> >
> >
>
>