Count number of work days for each month between a range of Dates

[Wrinkles]

Hi,

I need to be able to count the number of workdays for each month between any given range of dates.

Example....

If a date range of [startdate] 23/04/2008 - [enddate] 22/05/2008 I need to know how many workdays there were in April and also May separately. Workdays are Monday - to Friday.

April 08 = 6 days
May 08 = 16 days

The date ranges can be within one month and can extend for multiple months like the example above.

Many thanks,

 

[namliam]

Have you tried the search ability on the forum?

This question comes around about ones a week... I am sure you can find something out there.

http://www.access-programmers.co.uk/forums/search.php?searchid=2109225

 

[DCrake]

Simple Software Solutions

There are many posts on this subject but to give you a quick solution, that some may say is overcooked this is what you need to do.

Create a function called working days with two parameters

Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer

Dim tDays As Integer
Dim wDays As Integer
Dim CheckDate As Date

tDays = DateDiff("d",StartDate,EndDate) + 1

CheckDate = StartDate

For x = 0 To tDays -1
   If Left(Format(CheckDate,"ddd"),1) <> "S" Then
      wDays = wDays + 1
   End If

   CheckDate = DateAdd("d",1,CheckDate)
Next

WorkingDays = wDays

End Function

In a query (whatever) employ this function using the two dates in question

It iterates through the duration of the date range checking if the CheckDate is a Sat or Sun. If not is adds 1 to a running total. This running total is then passed back to the function.

CodeMaster:

 

[Wrinkles]

Thanks

Many thanks for your help.

As I havent had the need to use these forums for sometime, I wasnt aware that it had been posted many times before.

Thanks again for both responses.

 

[Wrinkles]

Just looked at your code DCrake and noted that it only gives the number of days between the range of dates which is fine but what I'm after is the number of days for each month within the range eg.

April 08 = 6 days
May 08 = 16 days

Thanks.

 

[namliam]

So... ? Split the range into 2 periods, one for april and one for may...

 

[Wrinkles]

So..... not practical to split range into two periods as users will be entering one date range over multiple months period as per my first thread.

 

[namliam]

As you will be coding it, Right? You can loop the function from DCrake for each period/month/part of month get the number of days and print it...

Or do you need it to be a query?

The easy, but time consuming way out would be to make a calander table which contains working days, that way you can 'simply' query said table "group by month" and count the number of days.

 

[Wrinkles]

Right!
I will be coding it and have already been looking at looping for each month and have already thought of the Calendar Table option too.

Also looking at whether a cross tab query can be developed too...

Think I'm getting there but will play around with it over the next few days.

 

[namliam]

Crosstab?? What will that do for you? Not much I dont think.

 

[khawar]

Solution

Download the attached db

regards

Khawar

 

[Wrinkles]

Thank you so much Khawar - just what I was looking for. You read my post exactly.

I havent been to this site for sometime as I havent used Access for a while but nice to know I can get something unusal fixed.

Nanliam, a word. Thanks for attempting to help me but your bluntness was something I didnt appreciate.

 

[namliam]

Nanliam, a word. Thanks for attempting to help me but your bluntness was something I didnt appreciate.

Blunt? I dont see where, but I appologize if I offended you in any way...

Times are "unusual" (see sig) for me and can sometimes appear to mean one thing and say another...

 

[raskew]

Very interesting code! However, the original post asked for work days, not calendar days.

Bob

 

[namliam]

Very interesting code! However, the original post asked for work days, not calendar days.

Bob

That code is excluding (atleast) the Saturday and Sundays

If Left(Format(CheckDate,"ddd"),1) <> "S" Then

 

[khawar]

Ammended

Ammended to include workdays only

 

[raskew]

Namliam -

Actually was referring to the code posted by Khawar, which uses a Cartesian product query to return numbers 000 to 999, when formatted as "Medium Date" returns dates from 30-Dec-99 to 25-Sep-02. While able to reproduce it, I'm baffled as to why format(000, "Medium Date") returns 30-Dec-99. The code goes on to use/modify this info to count the number of calendar days between start and end dates, by month. If you haven't already downloaded the attachment, recomend you do. Some techniques there that I haven't seen before.

Agreed that DCrake's function, as well as DateDiffW() at http://support.microsoft.com/kb/288194, and many others returns number of work days between two dates, but there's no mechanism to return a count by month.

Would be interesting to see a solution that combines the two.

Best wishes - Bob

 

[raskew]

Khawar -

Absolutely awesome! That's what I was attempting to do with your original post, but couldn't get the syntax correct.

Any comments on the format(123, "Medium Date") rationale?

Best Wishes - Bob

 

[namliam]

A date is actually a double, try excel...

Type in any date/time thing in a cell, now change the cell to a number
29-May-2008 will turn into 39597

Any integer part of the double is X days from 1-jan-1900 = 1
format(000, "Medium Date") returns 30-Dec-1899

The decimal part is the time of the day.
.5 = 12 hours
.25 = 6 hours
0,010416666 = 15 minutes...

 

[raskew]

Thanks, I'm fully aware of that. Today, 29-May-08 is 39597. No need to use Excel, the cdbl()
function will do it for you, e.g.
? cdbl(date())
returns
39597

What i don't understand is how (from the debug window)

? cdate(format(3072,"medium date"))
returns
5/29/08

...and this mechanism, with some modification, was used very aptly in Khawar's code.

Best Wishes - Bob