Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 159
Default filter dates by quarter

Im 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?

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default filter dates by quarter

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?


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,123
Default filter dates by quarter

Hi Larry

In my EasyFilter add-in there is a option to do this.
Maybe you like it?
http://www.rondebruin.nl/easyfilter.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Larry" wrote in message ...
Im 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?


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,480
Default filter dates by quarter

Hi Larry
In cell J14
=AVERAGE(OFFSET(I14,0,0,-13,1))
would give the average of the previous 13 weeks Gross
--

Regards
Roger Govier

"Larry" wrote in message
...
Im 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?

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 159
Default 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?



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Quarter Dates LR Excel Worksheet Functions 8 October 31st 06 11:27 AM
How to filter out all the partial dates from a big list of dates? Tiwarr Excel Discussion (Misc queries) 1 September 14th 06 05:35 PM
Filter dates by the day Chris AM Excel Discussion (Misc queries) 2 September 12th 06 05:16 PM
Setting quarter dates in excel RGB Excel Discussion (Misc queries) 5 May 15th 06 03:27 PM
how to filter dates? abigail New Users to Excel 2 August 18th 05 12:45 AM


All times are GMT +1. The time now is 01:28 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"