Hi

i have created a staff in post excel document but i am now stuck on a formula

a full time member of staff in our office works 37 hours per week which equates to 1.00.

But we also have staff that work part year and term time i need the formula to work out to the decimal place

i can work this out manually but there are quite a few staff in the office and continually changes

manual way to do this would be:
TO WORK OUT PART TIME HOURS
26 hours (staff members part time hours) divided by 37 (the full full time weekly hours) the SIP (staff in post) would be 0.70

BUT TO WORK OUT PART YEAR AND TERM TIME
The calculation would be
someone working 30 hours per week for 37 weeks per year would be
(30 [staffs part time hours]/37 [full time equivalent hours]) x (37 [weeks they will work in the year/52 [full working year]) = 0.58 (rounded to nearest decimal place

i need a formula to work this out for me in cells H5 to H53

Hi

Not sure where or why you came up with your formula:

If you have 30 then divided it by 37, then multiply by 37 again, you end
up back at your starting point of 30.

So it would have been simpler to have just done 30 divided by 52 which
gives you 0.5769 or in your case 0.58..

So, let us assume the following:

Column A = Hours
Column B = Weeks

Column H = Decimal Pay Rate/Structure

In H5 ( formatted to 2 decimal places ) you would simply do:

=IF($A2="","",($A2/$B2)).

You could conversely approach it from another angle being the overall
full time hours worked v. the overall part time / casual hours, BTW
equates to the same, e.g.

Part Time: Hours 30 x Weeks 37 = Total Hours 1110
Full Time: Hours 37 x Weeks 52 = Total Hours 1924

1110/1924 = 0.5769

In as much as I am not privy to why you would need it this way, my own
dealing with this type of structure in the past has always been focused
on the decimal factoring of full time hours not tenure.

HTH
Mick.

Hi Mick

i managed to find the formula on the work intranet

=(I5*J5)/52/37
__________________________________________________ ______________
But i need help with something that looks simple but i cant for the life of me work it out

In cells E10 i want to input a date e.g 01/02/12
i want the worksheet to automatically work out
what target i have hit in cell G10

my targets are
13 weeks
26 week
39 week
52 week
52+

i want the cell G10 to populate the content into one of the 5 targets based on the date i put in

so if i input

01/02/12 cell G10 should automatically have text entered "39 Week"

i also want the formula to work in all cells E10 and G10 onwards