Proportion of 1/2 hour per week

TheMatrix71

New Member
Joined
Mar 30, 2009
Messages
7
I can't seem to work out how to work out the proportion of 1/2 hour for any given "x" hrs/week?

Full time colleagues works at 36.5 hours/week, but paid for 37 hours/week.

Question:
If a part-time wishes to work at 33 hours/week (as an example), how do I work out the proportion of 1/2 hour (pro-rata) to ensure it is treated fairly with full time counterparts?

I've come across a table something like this from somewhere:
A = Wkly Hours Worked
B = Proportion of Weekly 1/2 Hr (Hrs-per-Week)
C = Proportion of Weekly 1/2 Hr (Hrs-per-Year)
D = Annual Allowance in hours (calculated to the nearest 15 minutes)

A-----------B---------C---------D
7.0000----0.0959----5.0005----5.00
7.2500----0.0993----5.1778----5.25
7.5000----0.1027----5.3551----5.25
7.7500----0.1062----5.5376----5.50
8.0000----0.1096----5.7149----5.75
8.2500----0.1130----5.8921----6.00
8.5000----0.1164----6.0694----6.00
8.7500----0.1199----6.2519----6.25

And yet, I can't see how it is being formulated? Any ideas?

Ta!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,452
Office Version
  1. 365
  2. 2021
  3. 2016
Platform
  1. Windows
Hi and welcome to MrExcel!

The formula in cell B2 is =A2/73, where 73 is the number of half hours in 36.5 hours.

The formula in cell C2 is =B2*52.14 where 52.14 appears to be the number of weeks per annum

The formula in cell D2 = Mround(C2,.25) which rounds to the nearest 0.25.

To use the MRound function you need to install the Analysis Toolpak - see Excel menu option Tools > Add-Ins > tick 'Analysis ToolPak'.

Andrew
 
Upvote 0

TheMatrix71

New Member
Joined
Mar 30, 2009
Messages
7
That was incredibly fast! Never did I thought of the number of 1/2 hrs in a 36.5 hrs, i.e. 73!!

A BIG thanks for your help in this matter. I have successfully condensed into 2 columns, with the 1st column containing the number of hours per week and the 2nd column to contain the following:-

MROUND(((1st column cell/73)*52.14),0.25)

Cheers for that.

I also have one other big problem with the timesheet calculations, but I will post this separately.

Once again, a big thank you!
 
Upvote 0

TheMatrix71

New Member
Joined
Mar 30, 2009
Messages
7
Out of curiosity, why 52.14 weeks to a year? I thought it would be 52?

Sorry for being a simple-minded person!

Tim



Sorry, I worked it out now... it's 365 days divided by 7 days a week = 52.14286 weeks!
 
Last edited:
Upvote 0

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,452
Office Version
  1. 365
  2. 2021
  3. 2016
Platform
  1. Windows
You're welcome!

365/7 = 52 + 1/7 except for in leap years. So maybe use 365/7 instead of 52.14?

(y)
 
Upvote 0

Forum statistics

Threads
1,191,191
Messages
5,985,209
Members
439,947
Latest member
fabiannic

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top