Time Additions on Monthly, 3 Monthly, Biannual and Annual Basis.

Hi.
I have a worksheet in which I want to add the times for each entry on the basis of months. As follows.
1. Monthly
2. Every 3 Months
3. Every 6 Months
4. Every 12 Months
5. Last 30 days
6. Last 60 days
7. Last 90 days
8. Last 120 days.

It can be a formula or a macro or some vb script..

Much thanks in advance.

Hi
how are your data stored, and how/where do you want to return information? For example, do you have a column with date values in, and want to return whether that date is within the past 120 days?

SAMPLE.xlsx

attached is the format. thanks.

Hi

I'm, still not 100% sure what you want, but the attached workbook shows how you can use SUMIFS and COUNTIFS to extract data counts and hours worked between particular dates, while the next 3 sheets give examples of using pivot tables to summarise data by quarter, year etc.

Hope this gives you some ideas

Hi.

Yeah. Thanks man. Thats definately helpful. I got the basic idea for the calculation. i am confused about the pivot tables though.

Can it be autonomous so that I do not have to enter every month's detail (eg from jan 12 to feb 12) in the summary section and it is automatically created. So if there are no entries for a month in the database, it automatically doesn't make a summary for that month.

Hi
the formulas on the first sheet give you the count and sum of hours worked between particular dates. You can make this relative to a particular point in time, either fixed (e.g. 1 january 2010) or moving (eg today's date).

To generate an ognoing time series of e.g. monthly or quarterly data, I think it's easier to use pivot tables.

The the third pivot table (monthly) should do what you seem to want - giving a count and sum of all observations by month. If there are no data for the month, by default the pivot table will not show that month in the table. You have to press "refesh" when your table changes to update the data (on the pivottable tools > options > data tab).