need help with Formula

[AshKhan]

Hi

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

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

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

i can work this out manually but there are quite a few staff in th
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 ful
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 (3
[weeks they will work in the year/52 [full working year]) = 0.5
(rounded to nearest decimal place

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

 

[Living the Dream]

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.