Count of Weekdays Between Two Dates

Task:

With a given start date and end date, print a list of Months
showing a count of weekdays and weekend days in each.

It seemed like such a simple request... but how to get
started?  If I already had a table of all dates between the
start and end dates, I'd have it whipped.

Any thoughts appreciated.

-- 
croy
0
croy
10/18/2007 2:37:24 PM
access.forms 6864 articles. 2 followers. Follow

3 Replies
1485 Views

Similar Articles

[PageSpeed] 47

Here are some ideas you can use for this.  First, you need to know how may 
days are in the month.  Then you can use the function below to return the 
number of work days in the month.  The subtract the work days from the number 
of days, and it will return the week end days.

To Get the last day of the month:

Dim dtmFistDay as Date    'The First Day of a month
Dim dtmLastDay as Date   'The Last Day of a month
Dim lngTotDays As Long   'Total number of days in the month
Dim lngWorkDays as Long 'Work days in the month
Dim lngWkendDays As Long    'Number of Week End days

    dtmFirstDay = DateSerial(Year(Date),Month(Date),1)
'Returns the last day of the month
    dtmlastday = DateSerial(Year(dtmFirstDay),Month(dtmFirstDay)+1,0)
    lngTotDays = DateDiff("d",dtmFirstDay,dtmLastDay) + 1
    lngWorkDays = CalcWorkDays(dtmFirstDay,dtmLastDay)
    lngWkendDays = lngTotDays - lngWorkDays

Now the function:

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

    On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
    CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
        (DateDiff("ww", dtmStart, dtmEnd, 7) + _
        DateDiff("ww", dtmStart, dtmEnd, 1)) + 1

CalcWorkDays_Exit:

    On Error Resume Next
    Exit Function
 
CalcWorkDays_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & _
        ") in procedure CalcWorkDays of Module modDateFunctions"
    GoTo CalcWorkDays_Exit
-----

This, of course, does not include any holidays.  If you need to count 
holidays, you will need a holiday table and you can use something like this 
to count the holidays in the month:

    CalcWorkDays = CalcWorkDays - DCount("*", "holidays", "[holdate] between 
#"  & dtmFirstDay & "# And #" & dtmLastDay & "#")

-- 
Dave Hargis, Microsoft Access MVP


"croy" wrote:

> Task:
> 
> With a given start date and end date, print a list of Months
> showing a count of weekdays and weekend days in each.
> 
> It seemed like such a simple request... but how to get
> started?  If I already had a table of all dates between the
> start and end dates, I'd have it whipped.
> 
> Any thoughts appreciated.
> 
> -- 
> croy
> 
0
Utf
10/18/2007 6:17:02 PM
On Thu, 18 Oct 2007 11:17:02 -0700, Klatuu
<Klatuu@discussions.microsoft.com> wrote:

Thanks to Klatuu.  I'm trying to get my head around that.
See below.

>Here are some ideas you can use for this.  First, you need to know how may 
>days are in the month.  Then you can use the function below to return the 
>number of work days in the month.  The subtract the work days from the number 
>of days, and it will return the week end days.
>
>To Get the last day of the month:
>
>Dim dtmFistDay as Date    'The First Day of a month
>Dim dtmLastDay as Date   'The Last Day of a month
>Dim lngTotDays As Long   'Total number of days in the month
>Dim lngWorkDays as Long 'Work days in the month
>Dim lngWkendDays As Long    'Number of Week End days
>
>    dtmFirstDay = DateSerial(Year(Date),Month(Date),1)
>'Returns the last day of the month
>    dtmlastday = DateSerial(Year(dtmFirstDay),Month(dtmFirstDay)+1,0)
>    lngTotDays = DateDiff("d",dtmFirstDay,dtmLastDay) + 1
>    lngWorkDays = CalcWorkDays(dtmFirstDay,dtmLastDay)
>    lngWkendDays = lngTotDays - lngWorkDays
>
>Now the function:
>
>Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
>
>    On Error GoTo CalcWorkDays_Error
>
>'Calculates the number of days between the dates
>'Add one so all days are included
>    CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
>        (DateDiff("ww", dtmStart, dtmEnd, 7) + _
>        DateDiff("ww", dtmStart, dtmEnd, 1)) + 1


Hmmm.  The number of days between start and end, minus the
number of weeks who's start is Saturday, plus the number of
weeks who's start is Sunday, plus 1...???


There's definitely something I'm not understanding or
interpreting right.


>CalcWorkDays_Exit:
>
>    On Error Resume Next
>    Exit Function
> 
>CalcWorkDays_Error:
>
>    MsgBox "Error " & Err.Number & " (" & Err.Description & _
>        ") in procedure CalcWorkDays of Module modDateFunctions"
>    GoTo CalcWorkDays_Exit
>-----


Would this work for start and end dates that are a few years
apart?


>This, of course, does not include any holidays.  If you need to count 
>holidays, you will need a holiday table and you can use something like this 
>to count the holidays in the month:
>
>    CalcWorkDays = CalcWorkDays - DCount("*", "holidays", "[holdate] between 
>#"  & dtmFirstDay & "# And #" & dtmLastDay & "#")


Haven't even got to the holidays part yet!

-- 
Thanks again,
croy

0
croy
10/19/2007 9:13:09 PM
See below
-- 
Dave Hargis, Microsoft Access MVP


"croy" wrote:

> On Thu, 18 Oct 2007 11:17:02 -0700, Klatuu
> <Klatuu@discussions.microsoft.com> wrote:
> 
> Thanks to Klatuu.  I'm trying to get my head around that.
> See below.
> 
> >Here are some ideas you can use for this.  First, you need to know how may 
> >days are in the month.  Then you can use the function below to return the 
> >number of work days in the month.  The subtract the work days from the number 
> >of days, and it will return the week end days.
> >
> >To Get the last day of the month:
> >
> >Dim dtmFistDay as Date    'The First Day of a month
> >Dim dtmLastDay as Date   'The Last Day of a month
> >Dim lngTotDays As Long   'Total number of days in the month
> >Dim lngWorkDays as Long 'Work days in the month
> >Dim lngWkendDays As Long    'Number of Week End days
> >
> >    dtmFirstDay = DateSerial(Year(Date),Month(Date),1)
> >'Returns the last day of the month
> >    dtmlastday = DateSerial(Year(dtmFirstDay),Month(dtmFirstDay)+1,0)
> >    lngTotDays = DateDiff("d",dtmFirstDay,dtmLastDay) + 1
> >    lngWorkDays = CalcWorkDays(dtmFirstDay,dtmLastDay)
> >    lngWkendDays = lngTotDays - lngWorkDays
> >
> >Now the function:
> >
> >Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
> >
> >    On Error GoTo CalcWorkDays_Error
> >
> >'Calculates the number of days between the dates
> >'Add one so all days are included
> >    CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
> >        (DateDiff("ww", dtmStart, dtmEnd, 7) + _
> >        DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
> 
> 
> Hmmm.  The number of days between start and end, minus the
> number of weeks who's start is Saturday, plus the number of
> weeks who's start is Sunday, plus 1...???

No, it returns the number of days that are not Saturday or Sunday between 
the two dates.  Adding one includes all the days.

> 
> 
> There's definitely something I'm not understanding or
> interpreting right.
> 
> 
> >CalcWorkDays_Exit:
> >
> >    On Error Resume Next
> >    Exit Function
> > 
> >CalcWorkDays_Error:
> >
> >    MsgBox "Error " & Err.Number & " (" & Err.Description & _
> >        ") in procedure CalcWorkDays of Module modDateFunctions"
> >    GoTo CalcWorkDays_Exit
> >-----
> 
> 
> Would this work for start and end dates that are a few years
> apart?

Yes.  calcworkdays(#1/1/2000#,date) returns 2036 (not including holidays 
because I don't have a holiday table in the db i have open right now.
> 
> 
> >This, of course, does not include any holidays.  If you need to count 
> >holidays, you will need a holiday table and you can use something like this 
> >to count the holidays in the month:
> >
> >    CalcWorkDays = CalcWorkDays - DCount("*", "holidays", "[holdate] between 
> >#"  & dtmFirstDay & "# And #" & dtmLastDay & "#")
> 
> 
> Haven't even got to the holidays part yet!
> 
> -- 
> Thanks again,
> croy
> 
> 
0
Utf
10/19/2007 9:45:01 PM
Reply:

Similar Artilces:

system date returns null
Testing for the year returns a null using the system date. I am using Access2007. This worked in Access2003. My code is: 190 If Right(Year(Date), 2) = txtEYear Then txtEYear = "" When I run the code and check "date" with debug, it shows 'null'. On Sun, 14 Feb 2010 14:21:02 -0800, Dave <Dave@discussions.microsoft.com> wrote: That's unusual. Does your code compile? Check a code window > Debug > Compile. -Tom. Microsoft Access MVP >Testing for the year returns a null using the system date. I am using &...

counting records shown in a report
greetings to all I have a report bound to a query called qAttendance. The report has its Filter property set to limit the records showing in the Detail section. So far so good. In the report footer, i want to show the count or records in the report. How do I do this?? (I can get a count in forms using recordsetclone and bookmarks, but I can't figure out a good method for reports) Becky oops. Got it using DCount. "Becky" wrote: > greetings to all > > I have a report bound to a query called qAttendance. The report has its > Filter pro...

Format a Date Using Field Properties
I know how to do this in Visual Basic and Assembler but having trouble with the Syntax in Access On the Format Event on the Properties Menu for a Field I have ("mm/d/yy") I have been guessing and trying to find an example on the WEB and in Access Help and still cannot determine what I am doing wrong. All I want to do is display the year as two characters instead of four! On my Format Tab, On the Format Line (The first line) What Syntax does it want to do this very simple task! Thanks in Advance Len mm/d/yy (no quotes) -- Doug Steele, Microsoft Access MVP http://I.Am/Do...

Suddenly -- Two PERSONAL FOLDERS???
Hi, All of a sudden, I have 2 Personal Folders!!!! They seem identical, and I cannot get rid of one or the other. * The only thing recently changed is the installation of XP SP-2. * All previous XP-Pro updates have been installed * Running Outlook 2000, and all updates are installed (never had any problems with these...they were installed "long ago." Thank you for your help! "Howie" ...

How do I change the footer bar from count to sum for highlighted .
just changed to office 2007 and can't select a range of cells to immediately show the sum total on the footer stats bar Have you tried right mouse clicking on the status bar and then choosing the function(s) you want to see? 2007 allows multiple functions (e.g. Sum, Avg, Min). "RobEgg" wrote: > just changed to office 2007 and can't select a range of cells to immediately > show the sum total on the footer stats bar ...

Counting the total number of cells with specified condition(freque
Hi, Can anyone pls tell me how do i count the total number of cells that has a certain word appear twice or nth times in a column? The below-mentioned will be the end result. The frequency will be specified by the user and excel will count the number of cells that has a certain word appearing twice in a column. Likewise, for Freqeuncy 3, sums the number of cells with that has a certain word in a string that appears thrice in the column. Frequency(input) Number of occurences(output) 2 10 3 \30 ...

Convert Date Format
I receive data with the date format as "20051106" [6 Nov 2005] which is OK for me but end users want to see it in the more conventional "dd/mm/yyyy" format. How can I transpose the data and insert separators. Tks Mike You could use a formula such as =RIGHT(E20,2)&"/"&MID(E20,5,2)&"/"&MID(E20,3,2) assuming your data was in cell E20 HTH -- _______________________ Naz, London "Mike" wrote: > I receive data with the date format as "20051106" [6 Nov 2005] which is OK > for me but end users want to see ...

Auto enter date?
Hi! me again! is there any way i can get excel to auto enter dates that jump by 7 days every 7th cell along? cheers steve Ive worked part of this out. in cell G1 =DATE(105,1,3) in cell M1 =G1+(WEEKDAY(G1)>=1)*7-WEEKDAY(G1)+1 but its displaying the time...i just want the date.... steve "R.P.McMurphy" <rpmcmurphy@ntlworld.com> wrote in message news:43118dff$0$17506$ed2e19e4@ptn-nntp-reader04.plus.net... > Hi! me again! > > is there any way i can get excel to auto enter dates that jump by 7 days > every 7th cell along? > > cheers > > s...

count cells in a document, but excude cells with a "0"
I want to count the number of cells that have a value greater than "0", but not use a range. The cells that will be counted are every other cell in that column. EX: 45 22<-- 18 6<-- 9 17<-- 2 0<-- so my answer would be "3", the amount of cells that have a value greater than "0". the arrows are not on the work sheet they are just used to point out which cell I need counted. You have not given any cell references where your data is but a fomual like this should be close... =SUMPRODUCT(--(MOD(ROW($A$1:$A$10), 2) = 0), --($...

If Statement with Two Conditions
I'm trying to do an if statement with two conditions like the following If a value falls between two dates then give it a value of 1, if not give it a value of 0. The if statement works well with one condition but when I try to do something like this = if (a2<=b2<=c2,1,0) it does not work. Help Leroy Leroy, Try something like =IF(AND(A2<=B2,B2<=C2),1,0) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Leroy" <anonymous@discussions.microsoft.com> wrote in message news:EF57802D-CA6F-4A45-95FB-9807FFEA1...

Merge customers from two different stores
I have had plenty (OK, too much) experience with merging accounts at the Store Ops level. The KB article on this topic includes the necessary queries and it seems to work just fine. Today I came across my first duplicate customer where the second account was created at another store. Is there a way to merge the purchase history & A/R for two accounts from separate databases? Tom -- The worst words in business: "We''ve always done it that way" -- Stop Fishing for eMail. This is a multi-part message in MIME format. ------=_NextPart_000_0035_01C6A72E.DEA7A220 Cont...

Combined bar and line chart (two Y axis) how to make excel know which data goes where
Hello all, after the super fast problem fixing last time i had a problem, i'd like to ask the excel collective for some more help... :) now trying to plot a combined bar chart and line graph. I basically want all the sets of data except one to appear as columns plotted against the axis on the left and the last set of data (which is a cumulative value of the data appearing for the columns) plotted against the right hand axis as a line. I can plot the graph fine, but out of the 7 sets of data which i want to appear as 6 sets of columns and one line, i get four sets of columns and three ...

Dates 06-18-07
My problem: I am trying to have one date minus another date. I want to display the difference in days. Help! -- TeeDEe Hi DateDiff("d", [StartDate], [EndDate]) HTH -- Wayne Manchester, England. "TeeDee" wrote: > My problem: I am trying to have one date minus another date. I want to > display the difference in days. Help! > -- > TeeDEe ...

Duplicate Detection Count
Is there a way to add a count onto the duplicate detection job results grid to show how many duplicates were found in each job? ...

Sending mail out via two routes
Hi, I wonder if anyone here can help me here... We have an Exchange 5.5 server (Will be upgrading this to 2003 at some point!) Currently, all mail in and out of the company goes via another (In-House) server with GFI Mail Essentials installed. We have two ADSL providers, and a firewall that routes all mail traffic over one provider (It routes everything else over the other provider, unless that line is down, then it routs everything over the working connection) - we would like to configure it so, if this "mail" ADSL line goes down, the other line will then take over sending ...

conditional counting #4
Here is my data (in actuality, it's about 35,000 rows long and will be growing at the rate of 1,000 rows per month) and it's in a sheet I've labeled as "Format 1". TYPE (column A) PERIOD (column B) VERSION (column C) DS 02/2005 1 BS 02/2005 2 WS 03/2005 2 DS 02/2005 1 I want to be able to count all of the different combinations that occur (i.e., count all of the "DS and 02/2005 ...

Two separate Exchange environments
Are there any third party tools that allow any kind of calendar info sharing between two autonomous Exchange environments in two completely separate AD environments? Users in company A would like to be able to see the free/busy data of users in company B. E2K3 used by both companies, and a mix of Outlook versions from 2000 to the latest used as clients. Thanks! Look at the Inter-Org Replication Tool http://www.microsoft.com/downloads/details.aspx?familyid=e7a951d7-1559-4f8f-b400-488b0c52430e&displaylang=en -- Bharat Suneja MVP - Exchange www.zenprise.com NEW blog location: e...

Two Cherries 520-6020 10-Inch Brass Back Dovetail Saw
Price:$16.35 Image: http://discountadvisors.info/image.php?id=B000JRBL48 Best deal: http://discountadvisors.info/index.php?id=B000JRBL48 I took this saw for a test drive and its' ok straight out of the box. The set is a really wide (which makes the kerf wide). The finish on the handle was disappointing. Some areas were missing finish and other areas had too much finish. But that's no big deal. For the price, this is a good deal but it requires some work. I put the saw on an anvil and took out the set (gently!) with a hammer. (I also heard of people clamping it between the...

Sum of counted values in a query
Hello, My question,.... I have a query which count the number of records in a table (as a result of the menuoption view-> totals ->count) as a result of the group by function for unique records. The result is: column1 column2 A 10 B 15 C 8 Now I want to insert a column3 which gives the sum of that counted values. I'd tried something like: expr1:Sum(Count[column2]) The result should be for each record in column3: 33 (result of the sum 10+15+8) It doesn't work. Somebody knows a solution ? Thanks regards, Johan You can always writ...

Date format in header/footer..
Is there a way of editing the format in the header / footer? I am running Office 2000 on a 933MHz P3 with Win98SE and 512MB RAM. TIA, -GHB Hi AFAIK only possible with VBA -- Regards Frank Kabel Frankfurt, Germany "Sonja" <burmapb@hotmail.com> schrieb im Newsbeitrag news:C9sUc.268963$a24.58397@attbi_s03... > Is there a way of editing the format in the header / footer? I am > running Office 2000 on a 933MHz P3 with Win98SE and 512MB RAM. > > TIA, > -GHB > You can use a macro: Put this in your workbook's ThisWorkbook code module (if you're unf...

COUNTING DATES #4
How do i write a formula to count the number of lines on a database of information that have a date that matches todays date? Say I have a huge database with 10,000 lines of info and I want it to look at all the info and put a number in a cell that says how many of the lines in the datbase contain todays date. -- na See COUNTIF Worksheet Function in help --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Cody" &l...

How do I convert a two-page Publisher newsletter into a Microsoft.
...

Conditional formatting of date
I want to compare a date in a cell to the current date. If the current date is past the date in the specified cell, and the adjacent cell is blank, I want the specified date to display in red. Would someone be so kind as to assist me with this please. You need conditional formatting. Select the cell to format, and let's assume that the compare cell is A1 Menu Format>Conditional Formatting Change Condition1 to Formula Is Add a formula of =AND(A1<TODAY(),B1="") Click Format Choose the text colour OK OK -- HTH Bob Phillips "cebubum" <cebubum@discussio...

Automatic fill a static date? #2
Thx, working on it... -- Luck ----------------------------------------------------------------------- Lucky's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1487 View this thread: http://www.excelforum.com/showthread.php?threadid=26501 ...

matches in two collums of numbers?
How do I find out how many matches in two collums of numbers in Excel? http://www.cpearson.com/excel/duplicat.htm -- Regards, Peo Sjoblom (No private emails please) "kdbeal" <kdbeal@discussions.microsoft.com> wrote in message news:15397E07-F564-4982-94F7-E250717770EB@microsoft.com... > How do I find out how many matches in two collums of numbers in Excel? ...