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

1. 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...

2. 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. Novice
Windows 7 64bit Access 2010 64bit
Join Date
Apr 2014
Posts
3

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. 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. 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. 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.

#### 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 - Senior Forums