reached my limit .......

[buz]

I finally managed to create my first 'complicated' formula - although it was based on one given to me in this forum. It's a small victory for me.

Problem is I still am not quite where i want to be with my data and pivottables.

I have this formula working <=GETPIVOTDATA([JobCostingcopy]Sheet1!$D$4," apr brian")>

First off - the formula seems to work regardless of the abs ref used - I've used $A$4 thru $D$4 and the formula still works ???? would someone try and explain this to me?

Back to my question. I have redone my DB to include a month column and a day column instead of only a date column. My aim is to extract 'pay period' data per employee. Being able to get the monthly totals is working with the formula I have. The problem is my pay period is not based on straight 'monthly' data.

My pay period runs from the 16th to the 15th, with a draw based on hours from 16th to end of month payable the 5th and payroll check based on the 16th to the 15th (less draws) payable the 20th.

As I enter my DB data, the employee pivottable extracts the data and the employee time sheet will similarly extract from the pivottable but with the details/formulas of the 'pay period' ($/hr; hrs worked; draws; totals; etc).

This is gonna be a huge help to me - any clues for me?

tfyh

Buz

 

[Dave Patton]

On 2002-09-01 10:39, buz wrote:

I have this formula working<=GETPIVOTDATA([JobCostingcopy]Sheet1!$D$4," apr brian")>

First off - the formula seems to work regardless of the abs ref used - I've used $A$4 thru $D$4 and the formula still works ???? would someone try and explain this to me?

Back to my question. I have redone my DB to include a month column and a day column instead of only a date column. My aim is to extract 'pay period' data per employee. Being able to get the monthly totals is working with the formula I have. The problem is my pay period is not based on straight 'monthly' data.

My pay period runs from the 16th to the 15th, with a draw based on hours from 16th to end of month payable the 5th and payroll check based on the 16th to the 15th (less draws) payable the 20th.

As I enter my DB data, the employee pivottable extracts the data and the employee time sheet will similarly extract from the pivottable but with the details/formulas of the 'pay period' ($/hr; hrs worked; draws; totals; etc).

Buz

I will not go into the details of using the function since my version of Excel may be different from yours.

1. read help on the function
2. try referencing the pivot table and then
specify what information you want

the formula below specifies a name "Dave" and month "1"


=GETPIVOTDATA(A2,"Dave 1")

With criteria in cells "D" and "E", the following also works with my PT

=GETPIVOTDATA($A$2,""&D11&" "&E11)
This message was edited by Dave patton on 2002-09-01 11:36

 

[buz]

Thank you Dave - I have read the getpivotdata exhaustively and am able to create the PT's no prob. It's a matter of gleaning the data from the PT.

My DB has column headings month>day>employee>customer>hours and has proved quite useful. One step to go. How do I go about getting mid month to mid month data.

Mac OS9.2 and XL 2001. (Have XL5 manual - not much re PT's in XL2001 help)

tfyh

Buz

 

[buz]

....... so it's been most of the morning and well into the afternoon and .......

=GETPIVOTDATA([JobCostingcopy]Sheet1!$A$3,"may 15 buz") will give me the hours worked the 15th. If I try "may1:may15 buz" I get a #N/A. I have tried every concievable combination of dates and separators - sometimes a #N/A and sometimes a #REF.

Can't seem to get it to give me total hours for a range of dates, such as "apr 16:may15 buz"

anybody?

tfyh

buz

 

[PaddyD]

=GETPIVOTDATA([JobCostingcopy]Sheet1!$A$3,"may 15 buz") will give me the hours worked the 15th. If I try "may1:may15 buz" I get a #N/A. I have tried every concievable combination of dates and separators - sometimes a #N/A and sometimes a #REF.

Can't seem to get it to give me total hours for a range of dates, such as "apr 16:may15 buz"

buz,

GETPIVOTDATA on its own will only return you the value from an element in the pivot, it's not going to perform a sum on a range of values. Also, in an earlier post, you said you'd seperated out month & day values into two columns - are they still date values, or are they just text / numbers? I ask 'cos I recon what you are trying to do will be easier via a formula route.

Alternatively, you could add another column to your source data that identifies the pay period for each record, then run the pivot table from the new field.

Post back with a bit more info about how your 'date' info is set up (& why you've changed from date to month & day)

Paddy

 

[buz]

Paddy - thank you.

Let's see ... my month and day values are as you suspected. The month value is a custom format (mmm) to get the 3 letter abbreviation and the day value is 'general'.

When i said I changed the date to month and day I negelected to say I didn't delete the date column. currently my DB has columns for month; day; date; employee; customer and hours. I added the month and day columns trying to figure a way to get the payroll period data I needed. Obviously, I haven't gotten there.

So the data I am trying to access is in a PT taken from my DB. I have been using a new wkbk and =GETPIVOTDATA....... in my attempts to extract payroll numbers for my employees. As I said earlier ... my pay periods run the 16th to the 15th (orders of my accountant) with a draw taken based on the 16th to the end of month and then a payroll check cut for the month ending the 15th less draws.

Your idea of a pay period column rings an idea. I could have 24 pay periods, odd pay periods would be draw periods and even pay periods would be end of period payroll.

Then again .... I bet you have a better idea -

tfyh

buz

 

[PaddyD]

OK, last few questions:

1) what dates will your data range over (i.e. is it cumulative with 'everything' in there? or will the data only be for specific periods one page a year for example)
2) what is the ealiest date
3) can you have date values in there greater thatn today?
4) Are you looking to generate a 'full history' for each employee, or just the result for a particular period (I guess this will relate to your answer to (1) above)

Paddy

 

[buz]

1) what dates will your data range over (i.e. is it cumulative with 'everything' in there? or will the data only be for specific periods one page a year for example)
2) what is the ealiest date
3) can you have date values in there greater thatn today?
4) Are you looking to generate a 'full history' for each employee, or just the result for a particular period (I guess this will relate to your answer to (1) above)

1. My DB was started in feb 02 and includes emloyee time from whenever they started through current.

2.Feb xx, 02

3. I update the DB once a week. It is where I (will eventually) pull all my job costing from, (invoicing customers & determining payroll). Eventually I will have a second DB for materials and suppliers.

4. My intension is to annualize this. Meaning in 2003 start a fresh copy of my DB and other customer and employee wkbks. For jobs that run across the new year I will probably copy and paste their 2002 data into the 2003 database.So I think the answer is 'yes', I want to have a full history for each employee.

Till now I have been using a 'template' workbook for each new customer. In this wkbk are sheets for material, labor, subs, invoice and proposal. The invoice has simple formulas that link to the other relevant sheets and total labor, material and subs. I seem to be opening and closing a lot of wkbks. With a central DB all I need is a template wkbk with an invoice in it and it will be completed once I make one minor change in the =GETPIVOTDATA function - insert their unique name. Same will go for each employee.

Does this make any sense? - seems to to me. At the touch of a button I can have info on any job or employee.

Sorry if this a bit long winded.

and tfyh

Buz

 

[PaddyD]

i was thinking something like the following, where you generate a new variable to run the pivot from:

Book6
	A	B	C	D	E	F	G	H	I	J
1	Date	Employee	PayPeriod	Hours
2	10/02/2002	a	Jan02	5
3	11/02/2002	a	Jan02	10
4	12/02/2002	b	Jan02	15		SumofHours	PayPeriod
5	13/02/2002	b	Jan02	5		Employee	Feb02	Jan02	Mar02	GrandTotal
6	14/02/2002	c	Jan02	10		a	30	15	5	50
7	15/02/2002	c	Jan02	15		b	40	20		60
8	16/02/2002	a	Feb02	5		c	10	25	25	60
9	17/02/2002	a	Feb02	10		GrandTotal	80	60	30	170
10	18/02/2002	b	Feb02	15
11	19/02/2002	b	Feb02	5
12	26/02/2002	c	Feb02	10
13	12/03/2002	a	Feb02	5
14	13/03/2002	a	Feb02	10
15	14/03/2002	b	Feb02	15
16	15/03/2002	b	Feb02	5
17	16/03/2002	c	Mar02	10
18	17/03/2002	c	Mar02	15
19	18/03/2002	a	Mar02	5
Sheet1

 

[buz]

Paddy

Two things: 1) Feb02 payperiod would be Jan16 - Feb15 and 2) The formula returns a #NAME? for dates => 15. For =<16 it returns an mmmyy pay period.

Looks like it will work when 1 & 2 are addressed.