Microsoft Excel 2007

im trying to make a formula that al my employees get a .20 cts raised every 6 months, since they started example

06/01/09 i know that he will get a raised on 12/01/09 but how i will make a formula to help me with this and put all my pay periods and see who is elegiblethank you

I don't know the layout of your spreadsheet, but maybe this will get you started: With 06/01/09 in A1 this will return 12/01/09:

=EDATE(A1,6)

You could wrap other functions around EDATE() to get the results you want.

thank you for your quick answer and see i will have in column a the start hiring date and then i will input all the payroll date that is every 2 weeks is it a formula that will help me to find out who will earn the progression salary example

a1 b1 c1 g1

06/01/09 06/15/09 ....... 12/01/09

12.00 12.00 12.00 12.25

OK, So here are my assumptions:

1 - The pay dates are on the 1st and 15th of each month.

2 - The six month anniversary date is always on the 1st or 15th of a month, based on the first pay date for that employee.

3 - The first pay date is A1.

4 - The first pay amount is in A2.

5 - The pay increase is .25

not.20 like you said in the OP.If all that is correct, put this in B2 and drag it across under your pay dates.

=IF(AND(DAY(B1)=DAY($A$1),OR(MONTH(B1)=MONTH($A$1),MONTH(B1)=MONTH($A$1)+6)),A2+0.25,A2)

Hi, Here is another solution for you to look at:

Employee Names in column B starting at B6

Employee Start dates in column C starting at C6Cell D3 - next pay date

Cell E3 - cts increment per 6 monthsThe number of completed 6 months is calculated in column D and

The total increment due for each employee is calculated in column EThe calculations in columns D and E can be combined, but for clarity and to check that the formulas work, I kept each step separate.

In D6 the number of months is calculated by the formula

=INT(DATEDIF(C6,$D$3,"m")/6)

This uses the poorly documented DATEDIF function which, using "m", returns the number of completed months between two dates, in this case the employees start date and the pay date. INT returns the integer part of the division of months employed / 6E6 is =D6*$E$3/100 to multiply number of completed 6 month periods x rate

Note the $ signs

Drag the two formulas down for each employeeB C D E 2 Pay date Rate 3 08-Oct-09 20 4 5 Name Start date # 6 months Increment 6 Anne 1-Feb-05 9 1.80 7 Zoe 2-Mar-04 11 2.20 8 Bill 3-Apr-03 13 2.60 9 Arthur 09-Oct-03 11 2.20 10 Wendy 07-Oct-03 12 2.40 11 Joe 02-Jun-09 0 0.00 12 Tim 01-Apr-09 1 0.20Regards

Ask Your Question

Weekly Poll

Do you think Google should sell budget phones in the US?

Discuss in The Lounge

Poll History