x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 398

# Creating a Date conversion by Quarter and Hours

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
gracie1972
• 2
• 2
1 Solution

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

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

Commented:
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 Commented:
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

Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.