Results 1 to 6 of 6
  1. #1
    Nauticalgent is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    3

    Fun with dates: Determining number of operating days in a quarter


    I apologize in advance if this is covered in another thread, I looked but did not find anything that answered my question.

    I have a fleet of vehicles that are owner-operated. To properly allocate thier fuel and determine forecasting I have to take the total days of thier lease time (thats the easy part!) and then break it down in Fiscal Year quarters.

    The fiscal year starts Oct 1 of the previous year and ends Sep 30th of the current year. For example, we are currently in FY14 which started Oct 1st, 2013 and will end Sep 30th, 2014. Naturally, each quarter is 3 months long...

    This issue: I have a unit that will be operating in my area Aug 18th, 2014 to Feb 14th, 2015. 180 days total with a portion of those days (137) in FY15. I need a query that will show the number of operating days for each quarter. I could do a constants table and do explicit date math for each quarter, but I am looking for something a bit more dynamic. I have over 50 units with stagered dates and something a little for plug and play would be ideal...

    Thanks in advance...

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    To count periods, I use a table that has about 10,000 records. Each record represents a date in time (day). There is a separate column (integer) to represent the month and a column (integer) to represent the weekday. I use the Weekday() and Month() functions to create this table. It probably would not hurt to have a column (integer) dedicated to the year too.

    With that, I can build queries to retrieve period numbers by days, weeks, months, and quarters for a given date. For your need, you would query two dates and compare the period numbers. It sounds like a lot of work but, I have not found a better way than to, first, understand what period number a given date falls under. With the period number, calcs are made much easier.

    Even with the new table, counting periods is not easy. You will need to test and verify your query of the new table. Once your query is built and tested, place it in a standard module as a UDF.

  3. #3
    Nauticalgent is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    3
    Thanks for your quick reply, ItsMe.

    My predecessor used an Excel spreadsheet. On the first row, he had five dates that he would reference: 1-Oct-13, 1-Jan-14, 1-Apr-14, 1-Jul-14 and 1-Oct-14. There represent the Start dates for each Quarter. Then in the individual rows, the formula is =MAX(0,MIN(G$1-1,$D6)-MAX(F$1,$C6)+1). The inclusive dates for this specific unit is on row 6 and it references the dates in Row 1 to determine the operating days for the 2nd quarter.

    I tried to duplicte this query in Access, but it tells me I am using the wrong number of aurguments. I have played with it a little but still have no favorable results...

    Really frustrating...

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    A table in Access that has a column for each quarter is not a normalized data structure. If you are logging a date, it should only have one possible column it would go into, regardless of the quarter/period.

    If it is easier for you to create a Select Case statement that then identifies the period and assigns that period to a variable, then, do that. I find it cumbersome when you want to quantify periods for multiple dates. You would need multiple variables, one for each date query. If you want to pursue this approach, a temp table that has columns for quarters may be helpful. Temp tables for accounting reports is common.

  5. #5
    lfpm062010 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Location
    US
    Posts
    415
    Attached is an example how you would get the report that you need.

    The problem is that the DateDiff is given the wrong value back. Therefore the quarter number does not match the total number as you would expect.

    Check it out. I am sure there is better way.
    Nauticalgent.zip

  6. #6
    Nauticalgent is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    3
    Excellant examples! I can work with this, thanks alot for the assist.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 06-21-2013, 12:28 PM
  2. Display previous quarter data when quarter is selected
    By rlsublime in forum Programming
    Replies: 1
    Last Post: 07-03-2012, 03:12 PM
  3. Dates before 30 days
    By fabiobarreto10 in forum Queries
    Replies: 7
    Last Post: 04-20-2012, 12:11 PM
  4. Select Dates between days and two days ago?
    By rstonehouse in forum Queries
    Replies: 4
    Last Post: 08-18-2010, 02:13 AM
  5. Replies: 4
    Last Post: 08-27-2009, 01:21 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums