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
1522 Views

Similar Articles

[PageSpeed] 57

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:

Using external query (mdb) for dynamic date range
I'm trying to set up an Excel workbook with a dynamic link to an Access database containing quality data, involving dates. I want the workbook to always import data for the last 90 days. When I try to set this up using MS Query, to try to filter data I've tried several different variations where time is greater than or equal to: date()-30 today()-30 now()-30 And every time, it comes back as an error. It seems to be setting the query so that the entire statement is a date variable, eg "Syntax error in query expression '((Table1.Time>=#date()-30#))'...

Date Time Picker lost
I created a form using the DateTimePicker Control to write in a field. Then I removed the DateTimePicker Control. When I publish the form to another computer where the DateTimePicker Control is not installed, the form does not work. If I install the DateTimePicker Control the form is working without problems. I want to sent this contact form to other people who does not have this control. In the fact I am not using the DateTimePicker Control but it must be somewhere in the form, but I cannot find it. If I put in the form another DTP Control in the list of the fields it is shown as DTP2 b...

Need Fixed Date Formula
Users submit new lines of info on our excel database. We want the dat to self-generate when the adjacent fields are filled out, and we wan the date to be fixed to the day the line was filled out. =now() changes daily, so we cannot go back and see when the data wa filled in. We don't want to leave it a text field, because users often do not fil the field in. A formula would be greatly appreciated. Feel free to E-mai wwoodall@riversideca.or -- Message posted from http://www.ExcelForum.com I don't think you can do this with formulas. But you could use an event macro: Option Explic...

returning a date 7 business day excluding holidays in the future
How would I write a formula that would do this. If I input a date in cell A1 and I have holidays in cells R1:R11. Can I write a formula that would give me a date in cell B2 that is 7 business days excluding holiday from the date in A1. I am having a hard time getting the networkdays and holidays working if I am not inputing 2 dates. Thanks >I am having a hard time getting the networkdays >and holidays working Use the WORKDAY function: =WORKDAY(A1,7,R1:R11) Format as Date -- Biff Microsoft Excel MVP "excelrookie" <excelrookie@discussio...

Manual change of a fixed asset cost basis should use User Date
The manual increase or decrease of the cost basis of an asset uses the computer system date to record the transaction date. Companies that prevent computer system dates from being changed cannot record the change in cost on the correct date. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree"...

date problem in excel
I have a problem that when I open a file, like text or csv file, the cell with number dash number, ex. 10-5, will be automatically changed to date format. But this really affect my data analysis. I know we could add ' before typing or change cell format before typing, but how to cancel this autoformat when opening another file?? Thanks is not number (dash) number is not a number only a text. in that case type '(single apostrophe)10-5 Wozniak <Wozniak@discussions.microsoft.com> wrote in message news:0F94D89E-29B4-4FDC-8C12-C8D3EF76700A@microsoft.com... > I have a probl...

Outlook - Exchange Rule ?
We are currently using Outlook 2000 SP3 with Exchange 2000 Standard SP3 on a W2k SP4 server. To work around the 16 gig limitation of the Information Store, I want to create a rule within Outlook to move emails messages older than 6 months to a user public folder within Exchange. The idea would be similar to the way auto-archiving works except I don't want the email to be archived in a pst file. Through the rules wizard I can move emails to a specific folder based on dates range, but these dates are static. Is there any way to either adjust a rule to work with the current date (i.e. 'al...

I cannot set the maximum date for the axis of a line chart
I have a line chart with a (horizontal) date axis along the bottom. Currently I have data from Sep-2002 to Sep-2009. All of this data is shown on the chart and the axes also go from Sep-2002 to Sep-2009. My problem is that I want to set the maximum of the date axis to an earlier date (say Jun-2008) hence hiding the later part of the data (without deleting the original data). But Excel 2007 will not let me do this (apparently). I have selected the axes and fixed the "maximum" date at an earlier date (like Jun-2008). Surprisingly, Excel leaves the axis exactly the s...

Please help..Need a report due within 7 Weekdays
I tried with all my efforts to create a report that is due within weekdays 7 days from today but no joy... I have a data element that states due date but cannot figure from the due date and todays date when is it due in a weekday not calendatr date... Thank You Al What is the SQL statement of the query you are using now...? -- Regards Jeff Boyce Microsoft Access MVP Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any ...

Date formula #8
How do you put in a formula for a date to change with corresponding number of years added to it? example. column 1 is # of years column 2 is date column 3 is calculated current date (# of years added to date) =date(Year(A2)+A1,Month(A2),Day(A2)) -- Kind Regards, Niek Otten Microsoft MVP - Excel "Confused" <Confused@discussions.microsoft.com> wrote in message news:060D5867-359A-42C4-A907-6418405B4B22@microsoft.com... > How do you put in a formula for a date to change with corresponding number > of > years added to it? > > example. > > column 1 is...

Date/Time Macro Puzzle
Hi, everyone-- Here is a macro that deposits the date and time into a cell, but which = is=20 supposed to pop up a warning box when the time is after 5:00PM: Sub NewDateAndTime() Dim mPrompt As String Dim mBoxStyle As Long Dim mTitle As String Dim mMsg As Variant mPrompt =3D "It's after 5:00 PM! Click OK to enter time, but please = remember=20 to enter TOTAL HOURS WORKED TONIGHT in the yellow box at right.=20 Thanks!" mBoxStyle =3D 64 mTitle =3D "AFTER-HOURS ENTRY" With ActiveCell .Value =3D Now .NumberFormat =3D "mm/dd/yy h:mm AM/PM" If Now...

Show two value ranges on one axis
How do you show two value ranges of the same data on one axis? (the axis range is separated by a zigzag/heartbeat style line). e.g. instead of showing 0-50, the axis could show 10-25 and 40-45. Any help appreciated! Take a look at Jon Peltier's Broken Y axis example. http://www.peltiertech.com/Excel/Charts/BrokenYAxis.html "NOKIA" <NOKIA@discussions.microsoft.com> wrote in message news:2564B7F7-0693-4A7E-A5C8-9D3180B4EA6B@microsoft.com... > How do you show two value ranges of the same data on one axis? (the axis > range is separated by a zigzag/heartbeat style l...

Adding Item to Stock Count Schedule
Can an inventory item be added to a stock count schedule after the count has been started and counted quantities have been entered? Thanks! Hi Don, Once a Stock-count schedule is started, you can't add any other items... And you'll be able to cancel it only if there are not Qty entries made in the Stock-count Entry window.... or you cleared all data in the SC entry form (which is either by posting the SC or clearing all entered data Qty): However, if you are confortable enough with SQL, you may enter additionnal records in the table directly. Those tables are only used as work-...

Fetch XML and Relative Dates
Is it possible to use relative dates beyond the preconfigured conditions in Fetch XML? For example, I want to return all records created 30 days ago to present. I would want something similar to this: <fetch mapping='logical'> <entity name='account'> <attribute name = 'name'/> <attribute name = 'accountid'/> <filter type='and'> <condition attribute = 'createdon' operator='on-or-after' value='Now()-30'/> </filter> </entity> </fetch> I u...

Most recent payment date
I want to request annual payments for membership. I have a query that pulls payments by month, but I need only the most recent payments. For example: January 2008, I need to know who has not made any payments for at least 12 months, and the most recent payment being in January, any year. What is the best way to do this? Use a subquery to get the most recent date. If subqueries are new, see: http://allenbrowne.com/subquery-01.html Another example of how to get a related field from a GroupBy: http://www.mvps.org/access/queries/qry0020.htm -- Allen Browne - Microsoft MVP. Pe...

Payment term with Specific Due Date
We are doing 'dating program' where stores can pay at a certain date for our promotions. Example: Pay now and pay by Jan 31. How can we do that? Thanks a lot Gaelle Gaelle, When setting up payment terms you can specify the term to be a certain day vs a number of days. However there isn't a good way say Jan 31 specifically. I think you specify the day and the Due Date would default to this month or next month depending on the day of entry. You can, of course, when you enter in the transaction set the "Due Date" to whatever date you want but the user would have to r...

Sumif with two criteria
I want to do a sumif() command if cell a=x AND b=y. Dan, =SUMPRODUCT((A1:A10="x")*(B1:B10="y")*(C1:C10)) Change the range references and criteria as needed. PC "Dan Perez" <danperez@joannstores.com> wrote in message news:040101c36c0b$2874d010$a301280a@phx.gbl... > I want to do a sumif() command if cell a=x AND b=y. Paul Corrado wrote > =SUMPRODUCT((A1:A10="x")*(B1:B10="y")*(C1:C10)) > Change the range references and criteria as needed. Very good solution. One little problem I have found with this in the past. Suppose...

Date in a chart title
ex..... Sales for May 14 How do I put a the current date in the title of the chart above so it comes up automatically? I'm thinking something like ..=now() but not sure how put in the title. Then how do I change the =now() (or whatever is used) to the date it is saved? Thanks, Frank. Right click on the chart tab, click on view code, and enter the following code: Private Sub Chart_Activate() ActiveChart.ChartTitle.Characters.Text = Date End Sub - Mangesh "FJ Shepley & JM Pfohl" <fshepley@wincom.net> wrote in message news:42856e7f$1_1@netscape.ca.....

Item counts on X Report
Currently we see Department sales and % of total sales. Is there a way to replace the "Report.Department.PercentageSales" variable with a total item count for sales in that department? ...

Counting Individuals NOT Occurrences with than one criteria
I have a Excel 2007 Spreadsheet with 26 columns and 190 rows arranged as follows: A B C D E F 1 Harm Red Fully Ach Abstinence Partly Ach Harm Red Fully Ach 2 Harm Red Fully Ach Harm Red Fully Ach 3 Harm Red Partly Ach 4 Abstinence Not Ach Harm Red Partly Ach 5 Abstinence Partly Ach 6 Harm Red Fully Ach Harm Red Partly Ach 7 Harm Red Fully Ach Now I've been trying to use SUMPRODUCT and it only gives occurrences of say 'Harm Red' and 'Ful...

Count a value excluding duplicates depending on a set criteria
Hi, hope someone can help, I have data that has multiple entrys for a date, but I only want to count each day as one using a formula in excel 2007. A B C D 1 Dept Crew Type Date 2 301 A 1 1/02/2009 3 302 C 2 1/02/2009 4 301 A 1 1/02/2009 5 301 A 1 2/02/2009 6 302 C 2 1/02/2009 7 303 D 2 1/02/2009 8 301 B 1 9/02/2009 9 301 A 2 1/02/2009 10 303 D 2 9/02/2009 E.g. I want to know how many days a crew worked in the above, 'Dept' = "301", 'Crew' = "A", 'Type' = "1", how many days. Would equal 2 days. One way... ...

Dates Stored with incorrect Date&Time
When inserted/updated a date field in the contact form the date displayed (correct) is different from the date stored in the database (that is incorrect). Example: BirthDate (default crm field) : value introduced in the form: 18/04/2005 BirthDate (default crm field) : value stored in the contactbase table: 17/04/2005 23:00:00 We have MS CRM installed in a SBS2003 Server. All clents Clients and the SBS2003 Server have the Date&Time = (GMT) Greenwich Mean Time (Lisbon, London,...) Could anyone help me? Thank you!! vagg All datetime values are stored in GMT, so there will always be...

HELP! Totals by two variables?
How do I total up the amount of an item by month and also name? For example: Colum A: Colum B: Colum C: Dates: Units Sold Seller: 1/5/05 8 Josh 1/6/05 10 Josh 1/15/05 7 Rick 2/8/05 1 Josh 2/20/05 13 Rick 3/5/05 25 Rick So I am trying to set up a chart where January Gives the totals for Josh and Rick in seperate cells and etc. for each month. Try Pivot Table ...

Help with Date format convert Month 1 to 01.
How can I convert 1/8/2010 to 01/08/2010. I need to capture and replace date range like Month 1 -> 01 2 -> 02 ... 9 -> 09 Above is the input search parameter and the search failed because user input 1/6/2010. Any helps would greatly appreciated. DECLARE @SearchVal VARCHAR(50) SET @SearchVal = '1/8/2010' --9/4/2010 should return 09/04/2010 SELECT @SearchVal Expect result: --------- 01/08/2010 try this SELECT CAST('01/04/2010' AS DATETIME) SELECT CONVERT(VARCHAR(20)...

How do I add weekdays?
I have a column of dates. How do I get excel to automatically put the weekday in? I currently have excel 2003. Will I need to update to excel 2007 in order for it to be able to do this? -- http://existenceandreality.blogspot.com/ Highlight the column then click on Format | Cells | Number tab. Choose Custom from the list (near the bottom) and then type this format in the panel: dddd dd/mm/yyyy When you use dddd in the format string Excel will take it that you mean the full day, whereas ddd will give you Mon, Tue, Wed etc. If you want the weekday in a separate column, then assuming ...