reached my limit .......

buz

Board Regular
Joined
May 30, 2002
Messages
230
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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,970
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Board Regular
Joined
May 30, 2002
Messages
230
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

Board Regular
Joined
May 30, 2002
Messages
230
....... 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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234

ADVERTISEMENT

=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

Board Regular
Joined
May 30, 2002
Messages
230
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234

ADVERTISEMENT

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

Board Regular
Joined
May 30, 2002
Messages
230
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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
 

buz

Board Regular
Joined
May 30, 2002
Messages
230
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.
 

Forum statistics

Threads
1,144,117
Messages
5,722,576
Members
422,446
Latest member
srclife

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top