Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to count days per month between two dates using an Access function

Posted on 2016-11-08
23
Medium Priority
?
150 Views
Last Modified: 2016-11-11
Hi everyone,

Can someone help me choose the right function to count the number of days per month within a given set of dates?

A small sample file attached.

I can get the the total count of days between the two days, using this:
Total Days: ((((DateDiff("d",[StartDate],[EndDate]))+1)/7)*[DaysPerWeek]*[HoursPerDay])/8
but what I need is the total count of days for each month between the dates.

Optimally, I need to show something like this (summing the total days in each month for all Orders whose dates fall within those months):
Month     Total Days
Sep               90.23
Oct                125.15
Nov               150.85
Book1.xlsx
0
Comment
Question by:fabi2004
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
  • 3
  • +2
23 Comments
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 41879671
is it always all days in the month, or sometimes part month?
and will your dates span years?
and what about leap years?
use the code on this link.
http://www.iaccessworld.com/wp-content/uploads/2016/06/function.png
or
http://www.projectperfect.com.au/microsoft-access-sample-6.htm
0
 
LVL 1

Author Comment

by:fabi2004
ID: 41879697
The formula has to take into account the number of days per week as well as the hours per day, then divide the whole thing by a regular 8 hour day to get the "work days".

So if Start Date is 10/24/2016, End Date 11/4/2016
Days per Week are 4
Hours per Day are 10

Then the result should be
Oct:
  (8 days/7 days in a week) = work weeks
   Work Weeks * 4 Days per week * 10 hours per day= Total hours worked
  Total hours worked / 8 hour day = Total Work Days (based on an 8 hour day)
= 5.71 days
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 41879708
in response to your question 'Can someone help me choose the right function to count the number of days per month within a given set of dates', the functions provided (above) will do,
24/10/2016 to 4/11/2016 is 9 working days and 11 in total - neither have 4 days/week?
I believe I have answered your original question but do not have time to address the second - hopefully someone else will run with this.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:fabi2004
ID: 41879730
I may not have expressed my question correctly.  It is not necessarily the total days in month I need.  It is the total number of days between two sets of dates subtotaled by month.

For example, if the dates are 10/14/16 through 12/05/16, then I need to know how many of the total days fall in October, how many fall in November and how many fall in December.

There there's the additional calculation of days/week and hours/day, but I think I can add that in easily if I knew how to subtotal my days by month.

I found a custom SQL function by WayneS on the Internet:  http://www.sqlservercentral.com/Forums/Topic1022335-149-1.aspx
But I don't know how to modify this to work in Access.
0
 
LVL 23

Expert Comment

by:yo_bee
ID: 41879791
are Sat and Sun included?
0
 
LVL 31

Expert Comment

by:hnasr
ID: 41879825
two dates using an Access function

Open in new window

Upload sample data in an access database.
Add few representative records, and show the required output.
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 41879852
Run a while same-month loop, inside a while not last date loop, incrementing an integer array (of 12 months)  cell at the same time as incrementing the compare date toward the end-of-range date. Each time you reach the end of a month you should increment the array cell index.
The end result will be a number of cells containing the number of days in each month.
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 41880365
This query will return your list of days count:
PARAMETERS 
    DateStart DateTime, 
    DateEnd DateTime;
SELECT DISTINCT 
    10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10) AS Factor, 
    Year(DateAdd("m",[Factor],[DateStart])) AS [Year], 
    Month(DateAdd("m",[Factor],[DateStart])) AS [Month], 
        IIf(DateDiff("m",DateAdd("m",[Factor],[DateStart]),[DateEnd])=0,
            Day([DateEnd]),Day(DateSerial(Year(DateAdd("m",[Factor],[DateStart])),Month(DateAdd("m",[Factor],[DateStart]))+1,0))
                -IIf([Factor]=0,Day([DateStart])-1,0)) AS Days
FROM 
    msysobjects AS Uno, 
    msysobjects AS Deca
WHERE 
    (((10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10))<=DateDiff("m",[DateStart],[DateEnd])));

Open in new window


Example from 2015-12-15 to 2016-04-17:
Sample output/gustav
0
 
LVL 1

Author Comment

by:fabi2004
ID: 41880978
Gutav, your coding is so far above my skill level that I don't even know how to use it.  Do you happen to have enough patience to walk me through how to implement it in my db?
0
 
LVL 1

Author Comment

by:fabi2004
ID: 41881082
Gustav your code is flawless.  Now I "think" all I need is to find out how to pass a table field into the parameter.
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 41881348
That is quite simple to do. If your table is named Dates, then:
SELECT DISTINCT 
    Dates.Id, 
    Dates.DateStart, 
    Dates.DateEnd, 
    10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10) AS Factor, 
    Year(DateAdd("m",[Factor],[DateStart])) AS [Year], 
    Month(DateAdd("m",[Factor],[DateStart])) AS [Month], 
        IIf(DateDiff("m",DateAdd("m",[Factor],[DateStart]),[DateEnd])=0,
            DateDiff("d",[DateStart],[DateEnd]),Day(DateSerial(Year(DateAdd("m",[Factor],[DateStart])),Month(DateAdd("m",[Factor],[DateStart]))+1,0))
                -IIf([Factor]=0,Day([DateStart])-1,0)) AS Days
FROM 
    msysobjects AS Uno, 
    msysobjects AS Deca,
    Dates
WHERE 
    (((10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10))<=DateDiff("m",[DateStart],[DateEnd])));

Open in new window

Note please, that I've corrected an error in the day count of the first month in those cases where both dates fall in the same month, and in the day count of the last month which was off by 1.

Example:
Sample/gustav
0
 
LVL 1

Author Comment

by:fabi2004
ID: 41881371
I tried so many variations and none of them worked.  Thank you for clarifying that for me.  It works great now except for the last month of the date span.  I seem to be getting strange results.

Sample Results
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 41881850
Sorry, introduced an error.
Here is the corrected query. I've added a field for the total count of days as a control:
SELECT DISTINCT 
    Dates.Id, 
    Dates.DateStart, 
    Dates.DateEnd, 
    10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10) AS Factor, 
    Year(DateAdd("m",[Factor],[DateStart])) AS [Year], 
    Month(DateAdd("m",[Factor],[DateStart])) AS [Month], 
        IIf(DateDiff("m",[DateStart],[DateEnd])=0,
            DateDiff("d",[DateStart],[DateEnd]),
            IIf(DateDiff("m",DateAdd("m",[Factor],[DateStart]),[DateEnd])=0,
                Day([DateEnd])-1,
                Day(DateSerial(Year(DateAdd("m",[Factor],[DateStart])),Month(DateAdd("m",[Factor],[DateStart]))+1,0))
                    -IIf([Factor]=0,Day([DateStart])-1,0))) AS 
    Days, 
    DateDiff("d",[DateStart],[DateEnd]) AS DaysTotal
FROM 
    msysobjects AS Uno, 
    msysobjects AS Deca, 
    Dates
WHERE 
    10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10)<=DateDiff("m",[DateStart],[DateEnd]);

Open in new window

/gustav
0
 
LVL 1

Author Comment

by:fabi2004
ID: 41882623
Hi Gustav,

Thank you for sticking with me and helping with this.

It seems that Total Days and the day count of the last month may be off by 1.

I can easily modify the total days formula, ((DateDiff("d",[StartDate],[EndDate]))+1)

But I honestly don't know how to change your other formulas to add a day to the count of the last month.

Sample1
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 41882882
No, the counts are not off by one. That's why I included the total column as control.
The count is 0 if DateStart equals DateEnd. If you wish to raise the count to include the end date, just add 1, but then you will never be able to have a count of zero.

/gustav
0
 
LVL 1

Author Comment

by:fabi2004
ID: 41882917
Correct, there should never be a count of zero.  StartDate 11/10/16  to EndDate 11/10/16 should be a count of 1 day.  I don't know how to fix that in your formulas because all but the last month of the count come out correctly.  How do I adjust just the last month of the date range's total?  The total column is off by one but that is an easy fix.
0
 
LVL 51

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 41883378
To cover all scenarios, you will to raise the end date by one; so either update the enddate, create an interim query where you add one day to the enddate and use this as source for the query, or add one day in the query itself:
SELECT DISTINCT 
    Dates.Id, 
    Dates.DateStart, 
    Dates.DateEnd,
    DateAdd("d",1,Dates.[DateEnd]) As DateEndNext, 
    10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10) AS Factor, 
    Year(DateAdd("m",[Factor],[DateStart])) AS [Year], 
    Month(DateAdd("m",[Factor],[DateStart])) AS [Month], 
        IIf(DateDiff("m",[DateStart],DateAdd("d",1,[DateEnd]))=0,
            DateDiff("d",[DateStart],DateAdd("d",1,[DateEnd])),
            IIf(DateDiff("m",DateAdd("m",[Factor],[DateStart]),DateAdd("d",1,[DateEnd]))=0,
                Day(DateAdd("d",1,[DateEnd]))-1,
                Day(DateSerial(Year(DateAdd("m",[Factor],[DateStart])),Month(DateAdd("m",[Factor],[DateStart]))+1,0))
                    -IIf([Factor]=0,Day([DateStart])-1,0))) AS 
    Days, 
    DateDiff("d",[DateStart],DateAdd("d",1,[DateEnd])) AS DaysTotal
FROM 
    msysobjects AS Uno, 
    msysobjects AS Deca, 
    Dates
WHERE 
    10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10)<=DateDiff("m",[DateStart],DateAdd("d",1,[DateEnd]));

Open in new window

/gustav
1
 
LVL 23

Expert Comment

by:yo_bee
ID: 41883707
@gus

You should be paid for this help!    ;)
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 41883713
Thanks! That's not the way it goes, but you can start by clicking a thumbs up.

/gustav
0
 
LVL 23

Expert Comment

by:yo_bee
ID: 41883714
I know.
0
 
LVL 1

Author Closing Comment

by:fabi2004
ID: 41883963
Thank you so very much for all of your help.  You've gone above and beyond with your patience and sharing your knowledge.  It is very much appreciated.
0
 
LVL 1

Author Comment

by:fabi2004
ID: 41883965
@yo_bee

Thanks man :-(  It wasn't like I wasn't feeling bad enough already.
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 41883969
Thank you. You are welcome!

/gustav
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question