Calculating Overtime In Pivot Table For Multiple Weeks
Feb 5, 2014
I have employees hours worked entered in multiple rows. Columns are Date, Name, Hours worked; then a column that calculates the week number.
The data is entered each day so the Names are not in order and I'd rather not have to resort by name each week.
I'm collecting that data along with several other fields in a pivot table, then I have a summary sheet that uses GETPIVOTDATA formulas to compile a ton of statistics.
I want to be able to filter the pivot table on any given number of weeks. I need to be able to calculate overtime (greater than 40 hours in a week) for each name over the filtered weeks. So I could want to see OT for weeks 4, 5, 7 combined.
Have been going round and round on this! I have a set of data that shows sales dollars by weeks. Currently, that data is in a Pivot table. I'd like to have a macro that selects the weeks (1-52) based on my input. For example. If I want to see weeks 5-52, I want to be able to input "5-52" somewhere and have a macro run that will select weeks 5-52 in the pivot table instead of having to go in and manually select each.
I have a large spreadsheet which lists individual dates over two years and circulation figures next to each date. Sometimes there are two entries for a particular date e.g. 1/10/08 there were 150 readers of magazine A and 200 readers of magazine B. When displaying this on a graph it give a bar for each day, whereas I would like a bar for each week.
Is there a formula to convert the individual dates into weeks and then total the circulation figures for that week?
I have a raw data in one file and then another file contains pivot table linked to the raw data. In the dashboard i need to show last 6 weeks trend which is linked to the pivot table. Currently i am manually unselecting previous week and selecting the latest week.
Ex: This week i select weeks 5, 6, 7, 8, 9, 10 from the pivot for the dashboard. When new data is updated next week, i want to select 6, 7, 8, 9, 10, 11. I cannot delete as i need the historical data.
I'm looking to calculate OT wages when they happen vs only at the End of Week totals. ie... if the employee hits 40 hours midshift on a Wed, I want to calculate what the total dollars would be for Wed.... a few hours at regular time plus what ever hours above 40 at time and a half.
We are guaranteed (right now) a 10 hour day. (we are on 4 - 10 hour shifts). So if we work 9 hours say on Monday, we get 9 hours of straight time and an hour of short work week (approx 80% of pay). Now if we work 11 hours on Tuesday (which they can do unfortunately) I get 11 hours of straight time and no overtime. We have to make up for the short work week hour. So a less complicated explanation would be if I dont work more than 40 hours per week, no overtime no matter what I actually worked per day. Seems pretty simple but what I want and need to do is to calculate it per day.
I got it to the point where If the day where we get short work week is first and overtime after that, it works. But if we work overtime first then get short work week later in the week, it wont calculate it. I know why it wont work now but I dont know how to make it work. LOL Here's a link to the file.
I have a very large pivot table. 1 output is the sum of seconds it takes to complete a task. I would like to convert this field from seconds to minutes. the data is all in seconds, so i need to divid the sum by 3600. is there a way to do this calculation inside the pivot table?
I have 3 basic job categories... each of those categories start overtime at a different hour.
So, if employee A is a dock worker, he starts overtime at 25 hours. If employee A is an office worker, he starts overtime at 40 hours and if he is a driver, he starts overtime at 55 hours. So based on that info, I'm wanting my spreadsheet to figure out how many hours each employee has left for the week.
The 2nd part question is how many hours per day is left for the week. Rather than making a separate tab for each day of the week, I'd rather the spreadsheet know what day of the week it is and divide accordingly.
I thought I had everything worked out with this timesheet but I've discovered one more problem.
Weekly Timesheet.xlsx
The total overtime hours needs to show that anything over 40 hours in the Total Regular Hours cell is overtime. And it also needs to show only up to 40 hours in the Total Regular Hours cell.
This pivot table is used to track referrals for an employment service, and needs to show monthly totals of referrals, enrolments, cancellations and placements.
I have the referral date as one of the row lables, but it seems that each of the others now only reflectes back to that date rather than the date entered in that value
EG:
5 clients referred in September, One was placed in October, 2 in December and 1 in Jan but the totals for placed are all showing in September rather than the month the clinet was placed.
I need to worked out Hours worked in a timesheet. This was the easy part, the hard part is the clause tha HR threw in, which is:
If you have worked and 8 hour day WITH 1 hr lunch then you qualify for overtime. if you work a 8 hr day and work through your lunch (1 hour) (so equivelant to 9 hrs) you still do NOT qualify for overtime there for Overtime = 0.
This is cause some people work though their lunch to get overtime, but legally they have to have a break so we are not paying overtime for it. I have basically tried in a formula to replicate this but it works with some data and not with all. attached is an example, as you'll see the formula works in some cells, but not others.
1 provides sales data for companies in a time period, 2 provides market share data for companies in a time period.
Both pivots can be filter by page fields country and category. Now i want to calculate 4 average market share values (because i have 4 defined periods): 1 average MS in period 1, 1 average MS in period 2, etc. In this average i want to exclude companies that are not active in a certain period (pivot table shows MS = "0%") and companies that totally not active in all periods (pivot table doesn't shows market share data at all after a certain selection of the page fields).
Attachment : calculate averages from pivot table.zip
I created two pivots: 1 provides sales data for companies in a time period, 2 provides market share data for companies in a time period. Both pivots can be filter by page fields country and category. Now i want to calculate 4 average market share values (because i have 4 defined periods): 1 average MS in period 1, 1 average MS in period 2, etc. In this average i want to exclude companies that are not active in a certain period (pivot table shows MS = "0%") and companies that totally not active in all periods (pivot table doesn't shows market share data at all after a certain selection of the page fields). Check out my attachment too to symplify this question. How how i formulate this formula?
I am trying to calculate over time based on shift time.
For example: Regular shifts are between 7:00 AM to 3:00 PM (Monday thru Friday). Anything between those hours and on those days should be considered REGULAR TIME. Anything between 12:00 AM to 6:59 AM or between 3:01 PM to 11:59 PM should be calculated as OVERTIME.
Anything on Saturday or Sunday should be calculated as OVERTIME as well.
I have a pivot table in the first sheet which includes the field "Date" as a column label.
In the remaining sheets, except for one, there are pivot tables based on the same underlying dataset which also include the field "Date" as a column label.
I would like to adjust the selection (i.e., exclude some dates) from the column label in the first sheet and see if it is possible to make the same adjustments automatically to the pivot tables in the remaining sheets as well.
note that the field "Date" is used as a Column label, i.e., it is not a Report filter.
My input data for Pivot table has a column named "Month". The month values are like April 07, April 08, Nov07 in random order for period between Jan 07 to Aug 08.
When I create a pivot Table, this column is sorted alphabetically (April 07 is followed by April 08) but I need it to be sorted in the ascending order with respect to month (April 07 is followed by May 07).
I further use this data to plot a Pivot Chart. There is another issue here. I want to use separate colors for each series. I do not know how to achieve above 2 things.
- a list of data - a pivottable based on these data
When moving this worksheet this worksheet to another workbook, the pivot table can't refresh anymore. This throws an error message "Reference is not valid". To work around this problem I need to adapt the datasource. The same occurs if the list and the pivot table are on separate sheet, with the added strange behaviour that, when data an PT are split, it is not possible to move both sheet together.
This would not be a big issue if my problem had to be solved manually. The real problem is that I need to move the sheets from a C# program.
I have a huge excel file. This file contains Projects, Project Manager, departments and sales for different quarters. Each department has sales and the quarter the sale was done. Now I want to create a pivot table where I can see the sales for each quarter for each project or each project manager. Attached file may elaborate the problem. ShaA1.xlsx
I have a list of people whose direct reports have either completed or not completed a task at work, and there are four tasks. The records look like this (I've removed the direct report's name, which is to the left):
I want to pivot the data so that for each manager I can see the % who completed Task 1, the % who completed Task 2, etc. preferrably in a horizontal format.
So, I can get this easy enough for the first task:
Count of Task 1Column LabelsRow LabelsCompleteIncompleteGroup 1Danny Ledbetter67%33%Mickey Smith75%25%
However to the right of this I want the results for Task 2, 3, and 4. The best I can do is the following:
Column LabelsCompleteIncompleteRow LabelsCount of Task 1Count of Task 2Count of Task 1Count of Task 2Danny Ledbetter67%67%33%33%Mickey Smith75%75%25%25%Grand Total71%71%29%29%
The results for Task 2 are incorrect. I've tried % of Row, % of Parent Row, and all the other calculations, and can't seem to get it. I've also tried putting the fields in the Column Labels box, the Values Box, etc.
I can even get this if I put the SumValues field in the Row Labels, which yields a vertical result, but still, the values are incorrect (Mickey has 100% completion under Task 2):
Column LabelsRow LabelsCompleteIncompleteDanny LedbetterCount of Task 167%33%Count of Task 267%33%Mickey SmithCount of Task 175%25%Count of Task 275%25%Total Count of Task 171%29%Total Count of Task 271%29%
How do I create a pivot table with multiple data columns? My fixed asset software will not let me create a report to list multiple months/ quarters. I've created a spreadsheet that I can dump each month into, but I'd like to be able to sort by G/L acct or Department. When I try to create a pivot table, I can't get it to accept each month as a data field. Ive attached a copy of the spreadsheet that I'm using & the report that I'd like it to look like.
I am looking a macro that will create as many graphs as there are counties (column a) in the data range (A4:N89 for this example). There could be a maximum of ~3000 counties, which would place the data range at roughly excel's limit (A4:N65000).
In any case, the graphs would take data from column F and look like the 3 graphs i have provided in the attachment. The graphs are simply graphing share for the top 25 lenders in each county. So, for example, Texas contains 254 counties--I would like a macro that creates 254 graphs, one next to the other, starting at cell O4 through column IV4 and then repeating at O22 through column IV22 and so on, until all the graphs for each county in the data range are created.
Also, would it be possible, just as in the attachment, for the macro to change the color of the data bar associated with a particular lender, in this case JPMORGAN CHASE BANK? In the attachment, I made the three bars associated with JPMORGAN CHASE red. If there is a county in which this particular is not in the Top 25, then all of the bar colors in the graph remain blue.
Would it be possible to do the exact same (macro that graphs off the pivot table) but include the data for the Top 25 Totals, Non-Top 25 Totals, and the County Total?
In other words, the graphs would look exactly as they do now, but, for example, the first graph would contain data from F5:F32 rather than just from F5:F29. Furthermore, the graph's axis label would pull from C5:C29 AND B30:B31 AND A32.
calculate overtime in California. I found a few previous posts, but none that meet all of the requirements. Employees get overtime in CA for:
More than 8 hours are worked in a day (up to 12)
More than 40 hours are worked in a week
Hours worked on the 7th consecutive day (up to 8)
Employees get double overtime for:
More than 12 hours are worked in a day
More than 8 hours worked on the 7th consecutive day
To eliminate the the 7th day issues, I am just using helper cells for hours worked on the 7th day of the work week. So far, what I have is what I found in a previous post:
=MAX(0,SUM(A13:A26)-40-SUMIF(A13:A26,">8")+8*COUNTIF(Daily Total Hours,">8"))+SUMIF(A13:A26,">8")-8*COUNTIF(A13:A26,">8")
This will calculate the hours of overtime over 40 in a week and 8 in a day, but will not differentiate between hours 8-12 and hours 12-?
Is there a way to create a pivot from multiple external worksheets. I have the same format and field names in different folders on our network. I am proabably looking at reading from 5 different files. Is it an option to write a query with UNION ALL?
I was wondering if it were at all possible to add multiple columns to the values portion of a pivot table at once, instead of individually dragging. When you click on them, it auto-sends them to Row Labels, which I don't want. I have a bunch of colums I want to add to Values and don't want to waste a bunch of time dragging them individually.
