To calculate number of working days between 2 dates

  • Thread starter Thread starter Chris1
  • Start date Start date
C

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
 
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?

Allen Browne said:
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.

Chris1 said:
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
Click to expand...
Click to expand...
 
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!)



cdb said:
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?

Allen Browne said:
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.

Chris1 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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?

Allen Browne said:
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
Click to expand...
Click to expand...
.
Click to expand...
 
Back
Top