Problem with minutes

[kodzirko]

I have got a rota for bar staff, i have done that in excel.

Day | Staff No. | Staff Name | Start Shift | Finish Shift | Hours |
Minutes |
Mon | 1 | John | 12:00 | 17:00 | 5 | 0 |
Wen | 2 | George | 12:00 | 17:30 | 5 | 30 |
Sat | 1 | John | 12:00 | 17:00 | 5 | 0 |

Imagine that there is about 20 lines with 10 employes working on
different days, then under that i wanted to put their name and how much
they earn.
They have got different rates.

i.e.
| Staff No. | Staff Name | Hours | Minutes | Rate | Wages |
| 1 | John | 10 | 0 | £8 | £80 |

I have used sumif function thanks to Bob Philips, whom helped me
before. which is searching for an employee and adding up his hours
minutes etc.
i.e
SUMIF($B$2:$B$20,$A27,F$2:F$30) hours function
SUMIF($B$2:$B$20,$A27,G$2:G$30) minutes function
(C27+D27/60)*E27 wages function

Now i have got another problem

with minutes function
i.e.
| Staff No. | Staff Name | Hours | Minutes | Rate | Wages |
| 1 | John | 10 | *130*| £8 | £100.12 |

How can i change minutes to mx 59 and any additional minutes will be
added automatically to hours.
i.e
| Staff No. | Staff Name | Hours | Minutes | Rate | Wages |
| 1 | John | *12* | *10*| £8 | £100.12

Kind Regards
Kodzirko

 

[Pete_UK]

One way, instead of having separate columns for hours and minutes is to
combine these, so that you can have a formula like:

=E2-D2

in F2, formatted as [h]:mm and copied down your 20 rows. Then you would
only need one SUMIF function, i.e.:

=SUMIF($B$2:$B$20,$A27,F$2:F$20)

and this should also be formatted as [h]:mm. Your wages calculation
would then be:

=C27*E27*24

Hope this helps.

Pete

 

[Bob Phillips]

kodzirko,

Change the two aggregator formulae to

=SUMIF($B$2:$B$20,$A27,F$2:F$30)+INT(SUMIF($B$2:$B$20,$A27,$G$2:$G$20)/60)

and

=MOD(SUMIF($B$2:$B$20,$A27,G$2:G$30),60)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)