Number of working days

[learning_codes]

Hi,

I use the codes to calculate the working days but how do I know if it
will calculate the days including leap year. Is there a way to know
how many working days of the leap year withing the date range from jan
1 2000 to jan 1 2010 ?

Can you help me how to use the codes to calculate working days plus
the leap year days?

Your help would be much appreciated.

Thanks

 

[Michel Walsh]

Hi,


I would try to compute the number of week changes with a week starting the
same day as the first date, multiply by 5, then, assuming the last date is
also a week date, add its day of the week number, asw if the week still
start as the first day.

From #02-07-2007# to #02-23-2007#:

? DatePart("w", #02-07-2007#)
4

? DateDiff("ww", #02-07-2007#, #02-23-2007#, 4) ' note the third argument
2

? DatePart("w", #02-23-2007#, 4) ' again, note the third argument
3

? 2*5+3
13

as in the 13 works day.

You have to remove non-working days that are special holidays on a weekday.
You probably have put them in a table, then, you COUNT the number of
records, in that table, that have a date between your two dates, to get the
number of special holidays.

That was assuming the working days were Monday to Friday, included. Probably
irrelevant though, but it is clearly untested for other cases.

If the last day is not a working day, you have to correct the formula
accordingly. Same if the first day is not a working day.


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

I usually have a table of holidays (tbl_Holidays) that I update each year,
that contains Hol_Date and Hol_Name fields.

I also have a table of numbers (tbl_Numbers) with a int_Value field and data
ranging from 0 to 999.

To find out the number of working days between two two dates, I run a query
that looks something like the following. What this query does is start out
by creating a nested subquery that identifies all of the dates (SomeDate)
between a [Start_Date] and an [End_Date]. It then joins this subquery to my
holidays table with a left join (to get all of the dates). Finally, I sum on
the value (1) for each of the days where the datepart("w", SomeDate) is
between Monday (2) and Friday (6) where the day is not in the holidays table.

PARAMETERS [Start_Date] DateTime, [End_Date] DateTime;
SELECT Sum(1) AS WorkDays
FROM (SELECT DateAdd("d",[Start_Date],[int_Value]) AS SomeDate
FROM tbl_Numbers
WHERE DateAdd("d",[Start_Date],[int_Value])
Between [Start_Date] And [End_Date]) AS InclusiveDates
LEFT JOIN tbl_Holidays
ON InclusiveDates.SomeDate = tbl_Holidays.Hol_Date
WHERE DatePart("w",[SomeDate]) Between 2 And 6
AND tbl_Holidays.Hol_Date Is Null;

HTH
Dale

 

[Michel Walsh]

.... forgot my previous solution, it does not work well, as, by
counter-example, from #02-07-2007# to #02-20-2007#.


Vanderghast, Access MVP

 

[Michel Walsh]

Maybe a working solution, based on DateDiff("ww",...) would be:

7-DatePart("w", firstDate)
+
DatePart("w", lastDate)-1
+
5*DateDiff("ww", firstDate, 7)



firstDate=#02-07-2007#: lastDate=#02-23-2007#: ? (7-DatePart("w",
firstDate)) + (DatePart("w", lastDate)-1) + 5*DateDiff("ww", firstDate,
lastDate, 7)

18

firstDate=#02-07-2007#: lastDate=#02-20-2007#: ? (7-DatePart("w",
firstDate)) + (DatePart("w", lastDate)-1) + 5*DateDiff("ww", firstDate,
lastDate, 7)

15


That still assumes that firstDate and lastDate are weekdays, and that
Saturday and Sunday are not workdays.


Hoping it may help,
Vanderghast, Access MVP

 

[learning_codes]

Thank you very much,

I will try. Much appreciated of your help.

 

[Jamie Collins]

I use the codes to calculate the working days but how do I know if it
will calculate the days including leap year. Is there a way to know
how many working days of the leap year withing the date range from jan
1 2000 to jan 1 2010 ?

See:

Why should I consider using an auxiliary calendar table?
http://www.aspfaq.com/show.asp?id=2519

"A calendar table can make it much easier to develop solutions around
any business model which involves dates. Last I checked, this
encompasses pretty much any business model you can think of, to some
degree. Constant problems that end up requiring verbose, complicated
and inefficient methods include the following questions: How many
business days between x and y? ... As you can imagine, all of these
answers require complex iterative code that steps through and applies
logic to each of the dates in a range. Or do they...? "

Jamie.

--