Im trying to set up a holiday accrual worksheet for my employees, so that i
can tell at a glance how much holiday they have left.

basicaly, employees acrue holiday entitlement for every hour they work upto
their contracted hours per calendar month.

after each employees name, i have a box which shows what their contracted
hours are. how do i get excel to refer to this figure and use it as the
maximum when determining the amount of hours worked in any particlular
month?

Cheers!

steve

Perhaps something like

=MIN(M1,SUM(A1:A30)

where M1 is the contracted hours, A1:A30 holds the hours worked

--

HTH

RP
(remove nothere from the email address if mailing direct)


"R.P.McMurphy" wrote in message
...
Im trying to set up a holiday accrual worksheet for my employees, so that
i
can tell at a glance how much holiday they have left.

basicaly, employees acrue holiday entitlement for every hour they work
upto
their contracted hours per calendar month.

after each employees name, i have a box which shows what their contracted
hours are. how do i get excel to refer to this figure and use it as the
maximum when determining the amount of hours worked in any particlular
month?

Cheers!

steve

Ok, Now I've got -

=MIN(C4,SUM(G4,L4,Q4,V4,AA4,AF4,AK4,AP4,AU4,AZ4,BE 4,BJ4))

i take it this will add up G4,L4,Q4,V4,AA4,AF4,AK4,AP4,AU4,AZ4,BE4,BJ4 as
long as each of these doesnt go over C4, and if any do, then they are
counted as the value in C4?

If it does thats great! Next problem is how do i divide the answer by
0.09615 in the same formula?

Hehe! Sorry about this. I'm a newbe to Excell!

steve



Perhaps something like

=MIN(M1,SUM(A1:A30)

where M1 is the contracted hours, A1:A30 holds the hours worked

--

HTH

Im trying to set up a holiday accrual worksheet for my employees, so that
i
can tell at a glance how much holiday they have left.

basicaly, employees acrue holiday entitlement for every hour they work
upto
their contracted hours per calendar month.

after each employees name, i have a box which shows what their contracted
hours are. how do i get excel to refer to this figure and use it as the
maximum when determining the amount of hours worked in any particlular
month?

Cheers!

steve

"R.P.McMurphy" wrote in message
...
Ok, Now I've got -

=MIN(C4,SUM(G4,L4,Q4,V4,AA4,AF4,AK4,AP4,AU4,AZ4,BE 4,BJ4))

i take it this will add up G4,L4,Q4,V4,AA4,AF4,AK4,AP4,AU4,AZ4,BE4,BJ4 as
long as each of these doesnt go over C4, and if any do, then they are
counted as the value in C4?

As long as the sum of those cells does not exceed C4.


If it does thats great! Next problem is how do i divide the answer by
0.09615 in the same formula?

Ooh, watch carefully, it's tricky <vbg

=MIN(C4,SUM(G4,L4,Q4,V4,AA4,AF4,AK4,AP4,AU4,AZ4,BE 4,BJ4))/0.09615

but are you sure you shouldn't multiply? I assume they get 0.09615 hours per
hour worked so that would be multiply

=MIN(C4,SUM(G4,L4,Q4,V4,AA4,AF4,AK4,AP4,AU4,AZ4,BE 4,BJ4))*0.09615

"Bob Phillips" wrote in message
...


"R.P.McMurphy" wrote in message
...
Ok, Now I've got -

=MIN(C4,SUM(G4,L4,Q4,V4,AA4,AF4,AK4,AP4,AU4,AZ4,BE 4,BJ4))

i take it this will add up G4,L4,Q4,V4,AA4,AF4,AK4,AP4,AU4,AZ4,BE4,BJ4 as
long as each of these doesnt go over C4, and if any do, then they are
counted as the value in C4?

As long as the sum of those cells does not exceed C4.


If it does thats great! Next problem is how do i divide the answer by
0.09615 in the same formula?

Ooh, watch carefully, it's tricky <vbg

=MIN(C4,SUM(G4,L4,Q4,V4,AA4,AF4,AK4,AP4,AU4,AZ4,BE 4,BJ4))/0.09615

but are you sure you shouldn't multiply? I assume they get 0.09615 hours
per
hour worked so that would be multiply

=MIN(C4,SUM(G4,L4,Q4,V4,AA4,AF4,AK4,AP4,AU4,AZ4,BE 4,BJ4))*0.09615


Ah yes...you spotted the deliberate mistake! ;-)

so, just going back to the first calculation... if C4(the contracted hours)
= 39 and G4(actual hours worked) was 41 and L4(actual hours worked) was 33
(and all the others were zero) the outcome would be 39+33?

ta

steve

"R.P.McMurphy" wrote in message
...

Ah yes...you spotted the deliberate mistake! ;-)

so, just going back to the first calculation... if C4(the contracted
hours)
= 39 and G4(actual hours worked) was 41 and L4(actual hours worked) was 33
(and all the others were zero) the outcome would be 39+33?

No, the formula is

=MIN(contracted_hours, worked_hours)

In this case, worked_hours is 41 (G4) and 33 (L4), or 74, contracted hours
is 39, so the result is the minimum value of 29 and 74, i.e. 39.

As I understand it, a worker gets .09615 hours per hour worked up to the
contracted total for the month. So the formula calculates then, then
multiply by .09615 and you have it

=MIN(contracted_hours, worked_hours)*.09615