Excel add up colour coded cells for Overtime

  CLONNEN 05:45 29 Jun 2005

The people who add up our company's overtime spreadsheet have NO CLUE how to get their figures right.

The Overtime Spreadsheet has the days of the month running across the top row (numerical days not actual day days) which run from the 28th to the 15th of each month. Each employee's name is listed in the leftmost column running downwards. Each month is a new page of the spreadsheet / workbook.

Three different Overtime rates are used to work out the Overtime.

1. All weekday evenings are paid at Basic Rate x 1.33

2. All Saturdays are paid at Basic Rate x 1.50

3. All Sundays are paid at Basic Rate x 2.00

The number of hours overtime we work is placed in each cell.

The spreadsheet is colour coded to make it easy to read. The 1.33 rate hours are Yellow - 1.50 rate hours are Blue - 2.00 rate hours are Red.

The spreadsheet DOES NOT do any adding up of any of the cells because of all the different rates.

The spreadsheet gets emailed to the HR department on the 16th of each month. They then add up all the cells for each colour code and submit our overtime to the external accounting firm we use.

  CLONNEN 05:59 29 Jun 2005

Is there ANY way of getting the Excel spreadsheet to add up each colour coded cell for each row. eg. Add all the Yellow cells together

For the last three months everyone at our company has been getting short-paid on their overtime because the people in the HR department who USED TO do the adding up have left and their replacements STILL are not doing it right despite having been told TWICE before that they are doing it wrong.

NOW people are saying that they WONT do any more overtime until the company does something about it and pays them what they are owed.

One of the problems with the spreadsheet is the numbering across the top - Why do they start it at the 28th when it would make more sense to start from the 16th of the previous month when the spreadsheet gets emailed to HR.

NONE of the overtime from 16th to the 27th has been paid to anyone in the company. The company's first response was "It's not on the spreadsheet" - of course it is not ON the spreadsheet you pillocks because the managers enter it on the previous months spreadsheet which has already been passed to HR. So technically the spreadsheet runs from 28th through to 27th. But only the figures up to the 15th are actually handed in.

The company conveniently forgets to add the last week of the previous month that we have worked and haven't been paid for.

They NEVER add on what they missed out the previous month either - at least I can't see an extra payment on my payslip anywhere so I assume they haven't bloody paid it.

One colleague sat down and worked out that to date she is owed about £200 worth of Overtime from these mistakes over the last four months. Over the course of a year the amount is astronomical.

Can ANYONE help us with a solution to this spreadsheet problem. Other than murdering the HR department that is.

  CLONNEN 06:15 29 Jun 2005

Traditionally the Overtime USED TO be handed over the Finance department on the 28th so all figures up to the 27th were handed in. Hence the reason for the numbering across the top of the spreadsheet.

Finance complained that they had too much work to do sorting out suppliers payments and customer payments and could someone else do the internal finance stuff like staff pay. So the actual printing of the payslips got passed to an external accounting firm and the HR department inherited the job of passing on the information despite the fact that none of them have ANY finance background whatsoever. In fact 2 of the 4 HR staff have recently left owing to the way the company treats them. So now we are left with 2 dipsticks (one of whom is barely out of nappies - he's in his twenties) adding up our overtime and making an AWFUL job of it.

They can't even add up the hours that have been emailed to them properly. For example at the beginning of June (1st - 15th) I worked 12 weekday evenings for 1.5 hours each at the Basic Rate x 1.33 - I only got paid for 12 hours - the idiots have totally ignored the fact that the cells say 1.5 and have rounded down to 1 for each cell - 12 x 1 equals 12 hours pay. It should be 12 x 1.5 equals 18 hours pay. They DO NOT seem to understand that half-hours we have worked count towards our overtime. They are ONLY counting Full hours worked.

  CLONNEN 06:22 29 Jun 2005

The 200+ employees in the company would be eternally grateful for a solution to the Overtime pay.

Many people are saying that if they get offered a job elsewhere they are definitely leaving because they are tired of being taken advantage of.

Some people have already QUIT over the problems with their pay. This means that those who are left are getting asked to do EVEN MORE OVERTIME and then at the end of the month the arguments start all over again and more people walk out.

The company keeps promising that it will be right next month but so far there is no sign that this promise will ever be fulfilled.

Please someone help us find a way to add each colour code cell for each row of the spreadsheet.

  VoG II 07:44 29 Jun 2005

Are these cells coloured "manually" or using Conditional Formatting?

  Simsy 11:02 29 Jun 2005

and it is an entirely appropriate use of a spreadsheet...

Calculating using "times" does make thing a little more complicated, but only a little.

However, a picture is worth a thousand words. If you messgae me using the yellow envelope by my name I'll send you my email. You can then email me a copy of the file and I'll have a look.

(I spent a long time working on something similar for myself)




  CLONNEN 21:57 29 Jun 2005

The cells are coloured (shaded) manually in Format - Cells - Background. I don't think anyone knows how to use Conditional Formatting.

The calculations don't really involve "times" as you put it just the Number of Hours worked at each rate. eg. If a row has four yellow cells which contain values 1,3,2.5 and 2 the calculation is 1 + 3 + 2.5 + 2 = 8 and a half hours of pay at the 1.33 rate.

  CLONNEN 22:06 29 Jun 2005

Some of the older people who I work with say that they have NEVER in their working lives come across such a ludicrous overtime system as this.

They say that all the other places they have worked overtime for paid them using a standard overtime rate. eg. time and a half (they had never heard of time and a third until they started working here). Having 3 different overtime rates is just silly - it would make calculations a lot easier if they used one rate for all the overtime hours.

  VoG II 22:09 29 Jun 2005
  CLONNEN 22:15 29 Jun 2005

Basically all the HR department do is tell the external accounting firm how many hours were worked by whom and at what rate. (eg. Joe Bloggs worked 14 hours at 1.33 rate, 7 hours at 1.50 rate, 4 hours at 2.00 rate)

The accounting people then do the actual money calculations since all the employees are on different Basic Rates. eg. My Basic Rate is £5.54 so if I do 5 hours overtime at 1.33 rate I get (5.54 x 1.33) x 5 = £36.84

  CLONNEN 05:13 30 Jun 2005

Brilliant code!

I have figured out how to add two ranges from different sheets as well so we can add together the hours from the end of one month and add them onto the beginning of the following month so we get paid properly.


I've emailed the info to my work email address and I will show it to my team manager today and see what he thinks.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

iMac Pro review

See iconic duo Smith and Foulkes' epic animation for the BBC's Winter Olympics coverage

iMac Pro review

Idées cadeaux pour geeks et tech addicts