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

x
Solved

# Creating a Date conversion by Quarter and Hours

Posted on 2013-01-18
Medium Priority
393 Views
Is there an easy way in VBA to create a simple date calculation that converts Quarters to days and hours?

I have a table that will capture this information.
0
Question by:gracie1972
[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
• 2
• 2

LVL 26

Expert Comment

ID: 38793866
Quarter years, as in Jan-Mar, Apr-Jun? All hours, or workdays?
0

Author Comment

ID: 38793872
Quarters are normal quarters, Jan-Feb, 40 hour work weeks, 5 days a week.
0

LVL 48

Expert Comment

ID: 38793896
Question is rather vague, can you give us an example of the output you are looking for.

Are you looking for something like:

CY     Qtr       WorkDays        WorkHours
13       1               #                      # * 40

If so, do you want to take into account holidays?
0

Author Comment

ID: 38793921
We are not looking to take into account holidays.  This is for a high level project planning to get an idea of assigning resources.

Year   QTR      DAYS    HOURS
2013   Q1       #          # *40

I will then take my Project %  time HOURS to get Allocated hours.   My last step will be to take the Total allocated hours per resource / HOURS = Total FTE Needed.

These are our calculations based on need.  I just was not sure if there was a standard formula that incorporated week by month by QTR.  I think it is 4.5 weeks per month on average, however, I wanted to be more accurate if I chose the date range, the Q is assigned.
This Q will drive these calculations based on Date Diff.
From there I was not sure how to calculate work weeks , etc.

Does this make sense?
0

LVL 48

Accepted Solution

Dale Fye earned 2000 total points
ID: 38794052
1.  Create a table (tbl_Numbers) with one field (intNumber) and 10 records (the values 0-10)
2.  Create a query (qry_Numbers) that will generate numbers from 0-99:

SELECT Tens.intNumber * 10 + Ones.intNumber as intNumber
FROM tbl_Numbers Tens, tbl_Numbers Ones

3.  To get a list of all of the weekdays within each quarter of each year (2013,2014) you could use the following query:
``````SELECT 2013+[Years].[intNumber] AS [Year]
, [Quarters].[intNumber]+1 AS Quarter
, DateSerial(2013+[years].[intNumber],[Quarters].[intNumber]*3+1,[qry_Numbers].[intNumber]) AS SomeDate
FROM , tbl_Numbers AS Years
, tbl_Numbers AS Quarters
, qry_Numbers
WHERE Years.intNumber<2
AND Quarters.intNumber<4
AND qry_Numbers.intNumber>0
AND DatePart("q",DateSerial(2013+[years].[intNumber],[Quarters].[intNumber]*3+1,[qry_Numbers].[intNumber])=[quarters].[intNumber]+1
AND Weekday(DateSerial(2013+[years].[intNumber],[Quarters].[intNumber]*3+1,[qry_Numbers].[intNumber]),2))<6
ORDER BY DateSerial(2013+[years].[intNumber],[Quarters].[intNumber]*3+1,[qry_Numbers].[intNumber])
``````
The various elements of this WHERE clause:
1.  Limit the results to 2 years
2.  Limit the quarters to 1-4
3.  Limit the date generated by the DateSerial() function to the quarter that it belong in
4.  Limits the dates to weekdays

It is a short hop from that list to a Group By query on the Year, Quarter, Count the Date column which will generate:

Year      Quarter      WeekDays
2013      1      64
2013      2      65
2013      3      66
2013      4      66
2014      1      64
2014      2      65
2014      3      66
2014      4      66
Quarters.mdb
0

## Featured Post

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
###### Suggested Courses
Course of the Month4 days, 23 hours left to enroll