filter dates by quarter
Im entering my weekly pay period end date (every Saturday) in N5 to N57
and through various steps Im getting to the gross in I5 to I57 , one sheet
52 weeks or 52 rows.
Now to figure my holiday pay, which is calculated by the previous quarters
weekly gross average in I5 to I57 I need to figure out how to filter my
dates in N5 through N57 by quarter and then average the quarterly I5 to
I57 then put all this in at D67 for the first quarter then repeat the
process to enter the average for the second quarter in E67 and so on to the
4th quarter.
=AVERAGE(OFFSET(I14,0,0,-13,1)) wont work as it only filters the gross in
column I and their may be weeks of no pay entry which can overlap quarters.
1st quarter = 1/1 to 3/31
2nd quarter = 4/1 to 6/30
3rd quarter = 7/1 to 9/30
4th quarter = 10/1 to 12/31
I have a feeling that < is used somewhere in the function but Im not
sure how to get there.
"Pete_UK" wrote:
In a helper column you could have a calculation which returned
something like "2007_Q4", or "2008_Q2", from the date, and thus
identify the previous quarter. There is some variability as to when
the quarters would start, so I can't give you a specific formula
unless you can give further details on this.
Hope this helps.
Pete
On Jan 9, 3:03 pm, Larry wrote:
I'm working on a spreadsheet for my payroll, in column
"A" is a weekly pay period end date
"I" is my gross pay for that week
Holiday pay is figured on a weekly average of the last quarter gross pay.
How do I "filter" the date per quarter and then average the gross for that
quarter?
|