I need to build out a salary budgeting formula that looks at start date and end date within a given fiscal year (April 1st - March 31st) and populates each month with the correct salary value given the start and end dates

Extra Challenge:

We have 26 pay periods in the year (3 in April, 3 in October, 2 in the rest of the months)

Desired outcome:

As an example, if I enter a start date of 5/1/2010 and an end date of 3/31/2011, I am looking for the month by month values to populate in cells D-O for the amount of salary for that month. In this example, the month of April would return a value of $0 and the months of May - March would return values equivalent to the pay per period multiplied by the number of pay periods in that month (listed in bullet point #2 above).

My current column headings are:

A: Start Date

B: End Date

C: Proposed Salary

D-O: Each month of the fiscal year, listed April through March

Any help would be greatly appreciated!

Extra Challenge:

We have 26 pay periods in the year (3 in April, 3 in October, 2 in the rest of the months)

Desired outcome:

As an example, if I enter a start date of 5/1/2010 and an end date of 3/31/2011, I am looking for the month by month values to populate in cells D-O for the amount of salary for that month. In this example, the month of April would return a value of $0 and the months of May - March would return values equivalent to the pay per period multiplied by the number of pay periods in that month (listed in bullet point #2 above).

My current column headings are:

A: Start Date

B: End Date

C: Proposed Salary

D-O: Each month of the fiscal year, listed April through March

Any help would be greatly appreciated!

- Monthly Pro Rated Salary Based on start and end date
- Help with formulas for slaries & fringes prorating
- Formula, Prorating costs
- Formulae for Salary %
- Help writing formula to determine salary/overtime
- Nesting Various Formulas using Dates and Salary
- Prorated Percentage Calculation
- Help writing formula to determine salary/overtime
- Prorate formula for weekly to monthly data
- Formulas for salaried workers
- I need a formula in excel for my Salary slip ....!
- Repeat array formula N times
- Using formula
- Calculation or Formula error HELP...
- Formulas for creating paid time off spreadsheet
- Prorated Bonus Percentage
- Array Formula
- Formula for 401K
- Calculate pro-rated salary based on date criteria
- Copy Formula & Increment Cell References Differently
- Logic In Nested IF AND Formula
- Formula To Calculate Pay Stub
- Calculate a salary earned between any two dates
- Conditional Formatting : logical formula

I need help with my excel file.

GOAL: to compute monthly prorated salary based on the inputs placed on the budgeted salary, start date and end date. The worksheet should automatically compute the monthly salary and should be based on the # of days of that month until the end date and put 0 on the months when employee is no longer employed (not within the start and end date).

I need this file to compute salary per month pro rated based on # of days and taking into account the exact start date and end date.

Attached is my file.

PROBLEM: The problem with my file is although it computes the monthly salary and puts in 0 in months employee is not employed, the salary calculation is not "to the dot". It does not calculate based on the number of days within the month. the budgeted salary only gets spread evenly thoughout the year and does not take into account the exact start date and end dates.

I know that my formula is using 12 month spreading, I cannot figure out what to use when I want it daily (probably salary divided by 365??)

Thanks and more power to this forum!!

I hope so!

Thanks for whoever reads this.

My problem is this.

I have two spreadsheets

The First has the following information

A B C D E

Name Salary UK USA ROW

Paul Baker £50,000

Paul Moody £50,000

The second has the following information

Name UK USA ROW

Paul Baker 100%

Paul Moody 50% 25% 25%

What I want is the first spreadheet to have a formulae written in C, D and E to do the following.

I want to look the Salary in Spreadheet 1 and allocate the split which is shown in spreadsheet 2.

So the results are as follows:

A B C D E

Name Salary UK USA ROW

Paul Baker £50,000 £50,000

Paul Moody £50,000 £25,000 £12,500 £12,500

Can this be done I have to do this to 1,000 people!

Your input would be valuable.

Regards

Simon

didn't manage to get my textbook in until 1 1/2 weeks left in the

semester and I need some help putting together a formula in Excel 2003

for the final that will determine the salary of multiple employees, at

different hours, pay per hour and overtime.

i.e.

employee 1: 40 hours at $9

emp. 2: 48 at 10

emp. 3: 42 at $8

emp. 4: 44 at $9.50

All hours after 40 hours are time and a half. If someone could help

construct a formula for this it would be hugely appreciated because my

efforts to do so have only resulted in profanity.

We'll also have to determine pay after 8% taxes, so I'm assuming that

can be done with a forumla sum=gross pay*8% followed by the auto sum

feature for all columns in that field?

--

sockmodel7

------------------------------------------------------------------------

sockmodel7's Profile: http://www.excelforum.com/member.php...o&userid=29366

View this thread: http://www.excelforum.com/showthread...hreadid=490771

I have attached a worksheet that lays out what I'm trying to do. The "data" worksheet will contain payroll data. In my example, you have the following columns: Name, Salary, Dept, Status, Termination Date, and Paid Through Date. The next worksheet is one called "Acctg." In the actual file that I have, there be multiple worksheets for each department, but in the example I only provided one, "Acctg." Basically, I need to create an IF(ISERROR formula, or maybe soemthing completely different, that will lookup the name on the "Acctg" worksheet (I have already created a formula that will bring over the people on the "Acctg" worksheet), and calculate if the month is actual, then 0, but if the month is forecast, AND less than the "Paid Through Date", then bring over the salary /12 or a weighted % times 12. Also, the formula must recognize that when the current month exceeds the paid through date, then it should be 0. I hope that made sense, I would appreciate any help. THANKS!!!

at 80% of plan pay 5% of salary

at 100% of plan pay 10% of salary

at 120% of plan pay 15% of salary

I have to prorate % of salary to pay based on the % of plan achieved.

(ie: 90% of plan = 7.50% of salary)

Is there a formula I can use to do this easily?

Thanks again!

i.e.

employee 1: 40 hours at $9

emp. 2: 48 at 10

emp. 3: 42 at $8

emp. 4: 44 at $9.50

All hours after 40 hours are time and a half. If someone could help construct a formula for this it would be hugely appreciated because my efforts to do so have only resulted in profanity.

We'll also have to determine pay after 8% taxes, so I'm assuming that can be done with a forumla sum=gross pay*8% followed by the auto sum feature for all columns in that field?

Below is a sample of data I use. The first week would have 479 applied to June (839/7*4 - for 4 days in June), while the last week would have 611 added to June (855/7*5), with June being a total of 3607.

6/4/10 839

6/11/10 839

6/18/10 846

6/25/10 832

7/2/10 855

Is there a ongoing formula I could use to copy down for it to always calculate the splits across the first weeks of each month and provide a running monthly total as the formula is copied down?

Thanks

At the right side of the spreadsheet I want the total hours to add up to the total cost for that person on that day. And at the bottom of the spreadsheet I will have total hours for the job.

So let say John had 9 hours Monday. John gets paid $13 an hour and the last column to the right will have the total (9x13) $113.

I can work this formula, my dilema is Pete. I pay Pete a salary, even though he worked three different jobs today totaling 10 hours he still makes $150 that day.Even though Pete is salaried if we do not work he does not receive any pay for that day. It is more like piece work salary.

In other words when I type a number of hours I still want the end column to read $150 whether he works 3 hours or 10 hours.

How do I type in Pete's hours and still keep his total costs at $150.

The reason this is important to me is I track my costs and hours on the same sheet (if possible). I do a lot of time and material jobs and have to keep an accurate count of hours for billing purposes. Can anyone help me with this formula? Thank you in advance.

Dears

I need a formula in excel for my Salary slip.

here is the details

If Total Salary >= 25000 and = 29999 then - Income Tax 6% of Total Salary

If Total Salary >= 30000 and = 34999 then - Income Tax 7% of Total Salary

If Total Salary >= 35000 and = 39999 then - Income Tax 8% of Total Salary

Thanks

Abdullah

First time poster, long time reader.

I am doing some New Year personal finance housekeeping and I have created a Google Docs workbook with 2 worksheets:2012 BUDGET - A yearly budget with my income and expenditure for the next 12 months. Rows are the individual items (income: salary, expenditure: rent etc), columns are my pay days (28th day of each month, or nearest week day). Expenditure is split up into different groups of rows: recurring expenditure (rent, rates etc.) and discretionary expenditure (holidays, big purchases that I am saving for).SAVINGS PLANNER - A savings planner, which contains data about what I am saving for (holidays etc) in the following year, including: target savings, number of months between saving start date and date the money is requiredWhat I want to do is automatically insert the amount I need to save per month, in the relevant row, into the 2012 BUDGET for only the number of months necessary i.e. for Thing F in SAVINGS PLANNER, I have a target of £200 and, having 6 months in which to save the cash (based on starting with this pay packet), I would like the figure of £33.33 to appear in the row for Thing F for the next 6 months automatically in 2012 BUDGET.

I've been messing around with array formulas and IF statements and have got a toehold, but further progress eludes me for now. Sorry for sharing the Google Doc link, but I use GD across multiple locations and I couldn't export the array formulas into Excel (also the Google Docs forum here appears to be quite quiet).

Huge thanks in advance as this is causing me considerable brain ache!

Cheers,

rsfx

I want to calculate end of service benefit our formula is ISt 3 year ie 1095 days 15days salary for per year (3Years) from 4 th year onwards 30 days salary for Py. I want to do th formula. please help me.

Thanks

Suresh

I am calculating the budget on increment % per month and total for full year. Calculation on pro data basis. Means if a staff joined in jan his salary calculate full year from jan-dec. If the staff joined in august then the calculation will be from aug-dec. i am calculating with a formula but getting error if the row or column is blank. Could you please help me to setup my requirement in proper and professional way. sheet is attached for your reference.

Your rapid response is highly appreciable.

Thanks in advance.

Best Regards

HECGroups

Example:

at 90% of Plan - pay 10% of salary

at 95% of Plan - pay 15% of salary

at 100% of Plan - pay 20% of salary

If they achieve 97% of plan then I would have pay them 12% of salary. That's the easy part, the hard part is figuring out what to pay them at 97.57% of plan.

Can anyone help?

Thank you.

For example: If Jane has been in her job for 2 years and falls within the 2nd quartile of the salary range, then she is entitled to an 5% increase based on the two-dimensional matrix.

How do I complete the formula to compute the new salaries?

If this sounds confusion, I have attached a sample of the database + matrix.

I desperately need to complete this project over the weekend.

Please help!

I have a question about a formula for 401k. This is the setup:

F4=401k costs to the company which is 3%

C13= the number of years the employee has been there

*the employee will only receive money into their 401k if they have been at the company for 1 year+

D13=their salary

I need to first deterimine the number of years the employee has worked there. IF 1 year or more, then they receive their 401K which is 3% (F4) multiplied by their salary(D13) multiplied by the number of years they have worked there(C13).

If the employee has worked their for less than a year, then the result is "0".

Thank you in advance,

David747

What formula can I use if I have the following data to use:

Current Bi-weekly: $1000

Salary Increase Percentage: 2%

New Bi-weekly: $1020

Anniversary date: 11/06/2011

Effective date: 07/01/2011

=Salary!ab2+Temp!cd1+Temp!ce1. This sums, for example the bi-weekly salary with the two weekly payrolls for the same period. My problem is, when coping the summary column in the to the next column for the next bi-weekly period, my Temp!xxx references move 1 column at a time and I need them to jump two columns while the Salary!xxx only jumps once.

In the above example, my next column in the summary sheet should be:

=Salary!ac2+Temp!cf1+Temp!cg1, but I'm getting =Salary!ac2+Temp!cd1+Temp!cf1.

How can I add additional columns for payperiods without manually adjusting the formulas?

I wonder if anyone can help - been struggling with this one for a while and only getting so far..

I'm trying to get a formula to work out salary increments that go up a spine point every year, but this increase can either be on a fixed date or on a start date anniversary. so my statement looks at the start date and end dates of each period and if it matches one of the dates specified in a range elsewhere it should increase the spine point - easy enough.

However this is complicated by the fact that we don't have a spine point 19, so I have to build in another IF statement along the lines of "if answer=19 then use previous cell plus 2 otherwise previous cell plus 1"

It works in the sense that it jumps from 18 - 20, but for some reason it then can't continue so rather than the next cell going to 21 it reverts to 0!

help - it must be something in my logic but i can't get my head round it any suggestion would be appreciated!

IF(AND($B$44"y",OR(H43=refer!$A$8,H43=refer!$C$8,H43=refer!$E$8,H43=refer!$G$8,H43=refer!$I$8,H43=refer!$K$8,H43=refer!$M$8,H43=refer!$O$8,H43=refer!$Q$8,H43=refer!$S$8,H43=refer!$U$8,H43=refer!$W$8,H43=refer!$Y$8)),IF(D43+1=19,D43+2,D43+1)))))

here is part of the formula where the error is found - The refer options are all to do with looking up dates (can't use a lookup as it contains other data that screws it up)..

Thanks

lins

I need help to create a formula to calculate pay stub. The calculation is based on a default start date of 1/1/2006. The end date will the pay period ending in the employee paystub.We would then use the Year To Date amount to calculate the monthly salary. I need to build this into an excel formula but could not find the correct formula. Appreciate your kind assistance.

I have attached a sample. Thanks.

My problem is that I don't know how to enter a formula which will give me the correct amount between 2 dates. For example

01/10/2005 to 16/09/2005 old salary PA 16000 new salary PA 17500. The manual formula would be:

((17500 - 16000)x1/12) + ((17500 - 16000) x 1/12 x 16/30)) = £191.67

Thanks

Brian

I have a maximum and minimum salary boundary for 4 different paybands. For an individual’s salary (one row per individual) I want the cell to go ‘RED’ when a salary outside the range for that payband is entered in the appropriate cell. I need to enter the formula in one condition only as I have other conditions I need to apply.

Grateful for some advice.

No luck finding an answer? You could always try Google.