Redundancy Table To Calculate Lenght Of Service
Apr 16, 2007
i am working on a redundancy table to calculate lenght of service at a certain age and what the redundancy in years will be,
age----------------years
less than 23------0.75 per year
23 to 42----------1.5 per year
43 and above----2.25 per year
these figures only count for the years they have been that age
examples
a person who is 22 and worked for two years would get 1.5 years redundancy, 2 x 0.75
a person who is 23 and worked for two years would get 2.25 redundancy 1 x .75 + 1 x 1.5
i can do this table manually adding on to each previous but wondered if someone might have a nice formula that would calculate it automatically
i would imagine there would have to be some kind of lookup to find out how many years they have been in each age bracket
View 9 Replies
ADVERTISEMENT
Dec 13, 2012
The formula that I currently have in E2, is giving me the number of years served by an employee. Is there another formula that can give me the number of years each employee has served? This is the formula that I have in E2
[Code] .....
Attached File : VACATION DAYS ACCURED.xls
View 9 Replies
View Related
May 28, 2014
Per the attached, I am looking to add restrictions to my formula based on YEARS OF SERVICE per the age restrictions each person falls into, those with 0-4 YOS who are under 20 years old etc.
Book10.xlsx
View 4 Replies
View Related
Dec 8, 2009
I have an excel spreadsheet with the column headings a1(Emp Name), b1(Hire Date[dd/mm/yy]). There around 1000 records of employees. Every month I have to generate a report for the following month, for example by the 3rd week of this December 2009 I have to extract the report of following staffs from those 1000+ records.
- those who will be completing 1 year in January 2010
- those who will be completing 5 years in January 2010
- those who will be completing 10 years in January 2010
- those who will be completing 20 years in January 2010
How shoud I do it? What should be the formula in which cells? I would appreciate if anyone could guide me with the complete formula with a very reliable way to do it.
View 9 Replies
View Related
Feb 2, 2010
how to use the text lenght value? I have a textbox where the employee enter its id number which is 6 characters long...i would like to have it prompted once the user enters less than 6 characters when pressing the tab key to move to the next textbox.
View 3 Replies
View Related
Oct 8, 2008
Setting up a worksheet to check a string of 7 numbers has the correct check letter attached.
Example
X4032818 X is the check letter. One method is take the 1st number multiply by 1, 2nd multiply by 2, 3rd multiply by 3, 4th x 4, 5th x 5, 6th x 6, 7th x 7. Add the results, in this case equals 123. Divide this result by 25 (the letter Z is not used) the remainder (23) matches the 23rd letter in the alphabet. To cater for when the remainder is 0, the range is 0=A Y=24.
I am using MID to extract each number.
View 6 Replies
View Related
Dec 30, 2008
I have a large number of workbooks, each with one sheet. I need to do the following, and am too pressed for time to try and work it out from scratch, so am hoping to get some pointers on how best to set up the macro. All workbooks are in one folder.
From a Summary Worksheet:
Open Each Workbook
Go to Sheet1
Copy values from "a2", "c7", "e26", etc.
Paste as values in the Summary Worksheet on a new row.
Close Workbook.
Repeat until all Workbooks are extracted.
View 9 Replies
View Related
Mar 7, 2007
I am trying to copy A1 into F8 only if F8 is empty, if not do nothing.
My code copy A1 into F8 regardless F8 is empty or not and overides my entry in F8.
Private Sub Worksheet_Activate()
If Len(F8) = 0 Then GoTo line1 Else GoTo line2
line1:
Range("A1").Copy
Range("F8").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
line2:
Exit Sub
End Sub
View 9 Replies
View Related
Jul 17, 2006
I would like to create a formula that would calculate the length of a person's employment, i.e. hired 8/14/94 till today, for example.
View 10 Replies
View Related
Jun 23, 2009
I have a large spreadsheet like this
NephrologyPrinted resent 26.05.09 chased
Nephrology28.04.09 resent 26.05.09Yes
Nephrology28.04.09 resent 26.05.09Yes
Nephrology28.04.09 resent 26.05.09Yes
Nephrology28.04.09 resent 26.05.09Yes
Nephrology28.04.09 resent 26.05.09Yes
There are plenty of other columns, but these are key. The first column is the service type, of which there are 40. I want to have another worksheet that totals all the entries in the third column that don't say 'yes', for each service. So, in this case the first line of my new workbook would say: Nephrology1
View 4 Replies
View Related
Feb 11, 2012
I run stats for an operating room and want to be able to look at total patient in room minutes between 7 AM and 3 PM. Problem is, many cases go past 3pm and I want to subtract that out. I have patient in room time and patient out of room time, so in essence I want the total number of minutes the patient was in the room between the two times minus turnover time. I have a pivot table ready to go.
View 7 Replies
View Related
May 14, 2014
I have a web service URL from vendor, i need to call the particular method in that web service and pass parameters to method from VBA excel. In C# its working fine.Same method like i need to do from from Excel VBA. My doubt is how to form authenticate xml and how to call a particular method using soap action URL.
View 1 Replies
View Related
Feb 10, 2010
Is it possible to restart a Windows Service with execution from a VBA Macro?
When you go into Windows Computer Management you can see a list of services. You can click a hyperlink to start, pause or restart.
View 9 Replies
View Related
Apr 12, 2012
I'm trying to find a formula for calculating the number of business hours a service request may be open; this request may have been opened and even closed outside of business hours. I found a formula that appears to be working:
=(NETWORKDAYS(StartDate&TimeCell,StopDate&TimeCell,holidays)-1)*(CloseTimeColumnLetter$CloseTimeRowNumber-OpenTimeColumnLetter$OpenTimeRowNumber)+IF(NETWORKDAYS(StopDate&TimeCell,StopDate&TimeCell,holidays),MEDIAN(MOD(StopDate&TimeCell,1),CloseTimeColumnLetter$CloseTimeRowNumber, OpenTimeColumnLetter$OpenTimeRowNumber),CloseTimeColumnLetter$CloseTimeRowNumber)-MEDIAN(NETWORKDAYS(StartDate&TimeCell,StartDate&TimeCell,holidays)*MOD(StartDate&TimeCell,1),
CloseTimeColumnLetter$CloseTimeRowNumber, OpenTimeColumnLetter$OpenTimeRowNumber)
What I don't know is HOW it works, I don't know what any of those formula's mean (with the exception of NETWORKDAYS). I'm trying to "show my work".
View 1 Replies
View Related
May 8, 2009
Have a small data base with customer details and comments. I would like a form to sit over that so that people on phones can enter customer # and check info and add further comments etc. for a specific customer. Before I try and design a form is there any templates for this type of thing. Have done a Google and Ozgrid search, but I maybe asking the incorrect names for the procedure.
View 2 Replies
View Related
Sep 24, 2009
Using Excel 2003 and am trying to do a calculation by customer, by service type. Attached is the workbook and I've tried numerous ways to do this. On the revenue calculation worksheet, I am tryng to calculate the type of service minutes from the data retrieve worksheet, multipled by the service rate on the rate table for the specifc type of minute by customer. I'd like to build it as a vlookup by customer and then by service type, but have run into difficulty.
View 4 Replies
View Related
May 16, 2014
What I'm basically trying to do is make a code that looks at where the "x" values are in my range and if the month matches the current month, open a pop up that says "Service Required: Equipment Name" when the file is opened.
If it's any easier, an actual date could be written instead of an x.
An alert system for service required if you will. I have attached a file.
TestSchedule.xlsx
View 4 Replies
View Related
Feb 19, 2014
I've been tasked with creating an updated booking system for a company which offers projects / services to the educational sector (UK Primary Schools.) I've done an alright job at augmenting their current Excel based system, but I would like to take it further so that the system is more all encompassing.
Currently, the booking form is filled in when a booking is made and the data from that is populated into their copy of the contract and our copy of the contract using simple "=" and to a certain extent, the text on the contracts is manipulated based on the booking form data using "IF" functions.
There is a basic macro which removes excess lines from the contracts if they aren't used, but that's about it. The difficulty we're having with the current system is that a lot of our projects are bespoke, happen over more than one date, and require a lot of manual manipulation in order to get them ready, which sort of defeats the object of having a 'clever' booking system.
I don't know very much about VB, but the sort of thing I would be looking to integrate is having drop down lists to select a 'project' which would load project-specific text into the contracts area. Also, instead of having to remove lines manually, I would like the data to be 'dropped in' creating rows as appropriate.
View 2 Replies
View Related
May 29, 2008
I am trying to automate something I've been doing manually. Let me explain.
1.) I am givien information about fees associated with a mutual fund product. A sample is reproduced in F4:F8.
2.) Next, this information is entered into the table. See A4:D12.
3.) Finally, from this table, I must calculate the fees associated with various account sizes.
4.) I did this manually in cells G15:G16 for 2 different account sizes.
View 14 Replies
View Related
Mar 4, 2009
Is there way to calculate the Standard Error within the Pivot Table using SE= StdDev/SQRT(n)? I've been doing it along side the pivot results, but it is really cumbersome.
View 1 Replies
View Related
Feb 14, 2014
I created a pivot table, but having a bit of difficulty. Here is what it tells me to do....
"...pivot tablet that calculates the number and average salaries by position."
I had no problem included in the table the average salaries by position, however I can't figure out how to calculate the number as well. It's apparently supposed to be another column since the instructions tell me to change the label above 'count' to 'number'.
View 2 Replies
View Related
Feb 23, 2009
I have attached a simple test file. The data tab has two columns in the pivot range. The third column is _not_ in the pivot range, but is there to show the result I'm trying to get with the pivot.
The idea is to count the number of people whose entry dates are greater than 2/1/2009. My third data column gives a correct result, but a similar formula in the calculated field of the pivot table gives a completely wrong result (a date), where my goal is to generate a zero, a one, or a sum of zeros and ones.
It could be a formatting issue, but that would not explain why all the pivot results have the _same_ wrong answer.
I'm new to PTs and calculated fields, and expect I've made a naive mistake that an expert could easily spot.
View 3 Replies
View Related
Jul 6, 2012
I have a pivot table in which 50 rows data and each row contains in the end %age. End of report i get grand total but it sums the percentage column. I need to calculate percentage in the end of grand total. How can i get it.
View 3 Replies
View Related
Sep 9, 2006
looking for a way to run some pivot tables on a large data table. Would like the result to show some different data extraction from the same field / column. The table is customer survey results for my employees, and the fields in question can have values from 1-5. I would like to finish the pivot table with all of these fields:
Row: Name (ok, that part is easy)
Data fields:
% of entries (column 2) that are 5
% of entries (column 2) that are 4 or 5
% of entries (column 2) that are 1 or 2
# of entries (column 2)
% of entries (column 3) that are 5
% of entries (column 3) that are 4 or 5
% of entries (column 3) that are 1 or 2
# of entries (column 3)
I'm hoping this is something I can do with calculated fields, but haven't been able to figure it out. So far all I have is a 'Count' function in the pivot wizard for the # of entries, but I'm not getting the % of entries at all. Column A = Name, Column B = 1st metric, Column C = 2nd metric. Fairly simple layout, but I have a small sample file I can attach if that's not explanatory enough.
View 7 Replies
View Related
Dec 20, 2007
With Pivot Tables, there is the ability to add Grand Totals to Rows or Columns, but I want to add Averages to the end of the row instead. Can this be done? I have tried Calculated Fields but can't get the right result. Auto Merged Post;Hi again,
After I posted this I found another similar post, where the answer was that the "Average" calculation has to be done outside the pivot table, ie. there seems to be no way the pivot will give averages for rows, only grand totals.
If this is the case then I will have to work around it.... I was hoping it could be done within the pivot because I have graphs linking to the pivot and they all go spak when I update the pivot with different data. The number of columns will change all the time, meaning the average will need to be reworked. Just trying to save time!
View 8 Replies
View Related
Aug 14, 2014
So I am running in to a large brick wall! I have a single pivot tablet with a column for items received and then a column for items shipped. I would like to create a calculated field in the pivot table that will subtract the items received from the items shipped columns. I have attempted to add the calculated field using the tool bar but it keeps giving me "0" for every item on the pivot table when there are obvious differences.
View 1 Replies
View Related
Feb 25, 2009
what i trying to learn is that im trying to get the delivery price calculate base on the delivery area(F10) and the total qty of the items(G10).
but i've tried with Vlookup, IF, lookup, Hlookup function and i still cant manage to get the right one to put the data in the H10
View 4 Replies
View Related
Aug 7, 2013
I have a data table with incidents in which one column is equipment number and one is data of occurrence. I now need to calculate how long ago the same equipment number occurred.
View 2 Replies
View Related
Aug 26, 2009
Is a way to calculate the difference between two columns in a Pivot table .
I have the data something like this in a pivot table....
Sum of MetricValueColumn LabelsRow LabelsFebruaryMarchAprilMayJuneJulyNet New My SitesNet New Team Sites0Number of Provisioned Users322059319906322372350239332914328404Total Capacity In Use (GB)1585.381722.761824.731947.772124.282262.78Total No. of My Sites208212316924682307313313134317Total No. of Team Sites101751082311396683273227940
What i need to do is to calculate the "Net New My Sites" and "Net New Team Sites" values something like this....
Net New My Sites (March) = Total No. of My Sites(March) - Total No. of My Sites(February)
Net New My Sites (April) = Total No. of My Sites(April) - Total No. of My Sites(March)
View 9 Replies
View Related
Feb 3, 2014
I have been using Excel to display, sort and present data in a professional way. I have created a pivot table with two columns below. Column C indicates the start date. Column D indicates the completion date. Blank cells show live data. I need to determine the number of days. 1) That have been - completed - a) That were < 2 Days, b) >2 but less than a week, c) > than a week (all). I have used the following formulas. a) countif(D4:D61, "<3"), b) countifs(D4:D61, ">2", D4:D61, "<=8"). Easy for completed data. My problem is, how do I determine the number of days, live data that following the same queries as above. I have figured out I can use countif to count the number of blanks by - countif(D4:D61, "(blank)") but how to put it all together and what formula should I use. I am trying to use countifs or if statements but am totally lost.
Column C Column D
---------- ----------
06/01/2014(blank)
25/11/2013(blank)
13/12/201314/01/2014
27/12/201317/01/2014
27/12/201310/01/2014
View 2 Replies
View Related