Solved

Creating a Date conversion by Quarter and Hours

Posted on 2013-01-18
5
321 Views
Last Modified: 2013-01-18
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
Comment
Question by:gracie1972
  • 2
  • 2
5 Comments
 
LVL 26

Expert Comment

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

Author Comment

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

Expert Comment

by:Dale Fye (Access MVP)
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

by:gracie1972
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 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 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])

Open in new window

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now