TheSubject
New Member
- Joined
- Feb 16, 2016
- Messages
- 23
HI all, hopefully someone can help me out. I submit weekly payroll for work, and however many years in, it's turn out my HR department can't read the payroll documents and rather than doing their job, they're asked us to double enter a load of data.
So, We have columns with employee names, rows with days, and then below the days, we have a total hours row, a holiday row, a furlough row, etc...
Traditionally I'd just put an H for holiday in the day cell, with numbered details below of many hours, it's usually a count if multiplied by standard shift length 7 hours formula, but we do edit that sometimes (e.g. salaried staff getting no paid over time, so if they do overtime before a holiday I would take it off the used holiday hours rather than them doing unpaid overtime). However, it's come about that HR was only reading the H and counting it as 7 regardless, so where we had run through just an hour or two, HR was taking a whole days holiday, which explains years of arguments over remaining holiday allowances.
Similarly with Furlough, we'd put an F, but accounts asked for more details because where month end and week end don't line up, we've got to make sure furloughs going through in correct months etc, so I have the furlough row at the bottom which does (contracted hours - total hours worked, rounded to 1/2 an hour) we have manually been writing e.g. 8F, 9F, so payroll gets done correctly.
HR has now spotted this and asked for 3H, 7H etc etc.
Now Furlough was a pain but its nearly over. Holiday is not.
So the formula I want is something like =if(F, "F"&(cell ref)/count of Fs in column),(if = H, "H"& (cell ref)/count Hs in column)
Problems I can see being that we just want to enter the F or H, so it has to be in conditional formatting rather than entering a formula?
Also when I made my first few attempts, I was getting into circular references. I can do a hidden how much holiday in 15, divided by number of Hs, then all I need is a conditional "If H, then "H"& hidden cell..." but I can't see how to make that happen.
Is this doable? Can I do it? Picture below, so column C is actually wrong - 9F, 8F and 9F do not equal the 25.5 below in row 16. Human mistake, but this is what I'm trying to avoid. So all Fs and Hs, need to automatically turn themselves into the letter & an average of the number in row 15 or 16, split across however many Hs or Fs there are in the column.
So, We have columns with employee names, rows with days, and then below the days, we have a total hours row, a holiday row, a furlough row, etc...
Traditionally I'd just put an H for holiday in the day cell, with numbered details below of many hours, it's usually a count if multiplied by standard shift length 7 hours formula, but we do edit that sometimes (e.g. salaried staff getting no paid over time, so if they do overtime before a holiday I would take it off the used holiday hours rather than them doing unpaid overtime). However, it's come about that HR was only reading the H and counting it as 7 regardless, so where we had run through just an hour or two, HR was taking a whole days holiday, which explains years of arguments over remaining holiday allowances.
Similarly with Furlough, we'd put an F, but accounts asked for more details because where month end and week end don't line up, we've got to make sure furloughs going through in correct months etc, so I have the furlough row at the bottom which does (contracted hours - total hours worked, rounded to 1/2 an hour) we have manually been writing e.g. 8F, 9F, so payroll gets done correctly.
HR has now spotted this and asked for 3H, 7H etc etc.
Now Furlough was a pain but its nearly over. Holiday is not.
So the formula I want is something like =if(F, "F"&(cell ref)/count of Fs in column),(if = H, "H"& (cell ref)/count Hs in column)
Problems I can see being that we just want to enter the F or H, so it has to be in conditional formatting rather than entering a formula?
Also when I made my first few attempts, I was getting into circular references. I can do a hidden how much holiday in 15, divided by number of Hs, then all I need is a conditional "If H, then "H"& hidden cell..." but I can't see how to make that happen.
Is this doable? Can I do it? Picture below, so column C is actually wrong - 9F, 8F and 9F do not equal the 25.5 below in row 16. Human mistake, but this is what I'm trying to avoid. So all Fs and Hs, need to automatically turn themselves into the letter & an average of the number in row 15 or 16, split across however many Hs or Fs there are in the column.
