To calculate number of working days between 2 dates

[Chris1]

Hi,

How do I caluclate the number of working days taken
between 2 dates?

My calculating field is:
[DaysTaken]: [EndDate]-[StartDate], it return a number of
days but obviously does not descriminate between week days
and weekends, nor public holidays.

Any help will be greatly appreciated!
Many thanks,
Chris1

 

[Allen Browne]

See:
Doing WorkDay Math in VBA
at:
http://www.mvps.org/access/datetime/date0012.htm

 

[Guest]

Is there no way of doing this without having to use VBA?

If not, can you tell me how this fits in with a Query? How do you call the
procedure within a query to calculate between two dates in a table?

See:
Doing WorkDay Math in VBA
at:
http://www.mvps.org/access/datetime/date0012.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

How do I caluclate the number of working days taken
between 2 dates?

My calculating field is:
[DaysTaken]: [EndDate]-[StartDate], it return a number of
days but obviously does not descriminate between week days
and weekends, nor public holidays.

Any help will be greatly appreciated!
Many thanks,
Chris1

 

[Douglas J. Steele]

If you copy the code to which Allen pointed you into a module, you can
modify your query to have
[DaysTaken]: dhCountWorkdaysA([StartDate], [EndDate])

If you really object to having to add VBA code, you can use

[DaysTaken]: DateDiff("d", [StartDate], [EndDate]) -
DateDiff("ww", [StartDate], [EndDate], 1) * 2 -
IIf(Weekday([EndDate], 1) = 7,
IIf(Weekday([StartDate], 1) = 7, 0, 1),
IIf(Weekday([StartDate], 1) = 7, -1, 0))

(that's to be entered as a single line)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Is there no way of doing this without having to use VBA?

If not, can you tell me how this fits in with a Query? How do you call the
procedure within a query to calculate between two dates in a table?

See:
Doing WorkDay Math in VBA
at:
http://www.mvps.org/access/datetime/date0012.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

How do I caluclate the number of working days taken
between 2 dates?

My calculating field is:
[DaysTaken]: [EndDate]-[StartDate], it return a number of
days but obviously does not descriminate between week days
and weekends, nor public holidays.

Any help will be greatly appreciated!
Many thanks,
Chris1

 

[Chris1]

Thanks a lot to you all,
I have 1 last question on this:
I am in the UK, the holidays will be different from the
ones in code to be copied. How do I enter my holidays? Do
I need a separate table called SkipHolidays? to which the
module will look in?

Thanks.
Chris1

-----Original Message-----
Is there no way of doing this without having to use VBA?

If not, can you tell me how this fits in with a Query? How do you call the
procedure within a query to calculate between two dates in a table?

See:
Doing WorkDay Math in VBA
at:
http://www.mvps.org/access/datetime/date0012.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

How do I caluclate the number of working days taken
between 2 dates?

My calculating field is:
[DaysTaken]: [EndDate]-[StartDate], it return a number of
days but obviously does not descriminate between week days
and weekends, nor public holidays.

Any help will be greatly appreciated!
Many thanks,
Chris1

.