# reached my limit .......

#### buz

##### Board Regular
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

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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

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

....... 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

=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)

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

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)

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

i was thinking something like the following, where you generate a new variable to run the pivot from:
Book6
ABCDEFGHIJ
1DateEmployeePayPeriodHours
210/02/2002aJan025
311/02/2002aJan0210
412/02/2002bJan0215SumofHoursPayPeriod
513/02/2002bJan025EmployeeFeb02Jan02Mar02GrandTotal
614/02/2002cJan0210a3015550
715/02/2002cJan0215b402060
816/02/2002aFeb025c10252560
917/02/2002aFeb0210GrandTotal806030170
1018/02/2002bFeb0215
1119/02/2002bFeb025
1226/02/2002cFeb0210
1312/03/2002aFeb025
1413/03/2002aFeb0210
1514/03/2002bFeb0215
1615/03/2002bFeb025
1716/03/2002cMar0210
1817/03/2002cMar0215
1918/03/2002aMar025
Sheet1

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.

Replies
12
Views
235
Replies
4
Views
389
Replies
1
Views
182
Replies
4
Views
396
Replies
4
Views
2K

1,218,593
Messages
6,143,379
Members
450,483
Latest member
santvik234

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back