I am creating a spreadsheet for payroll that would be huge without the use of text and numerical values together. The purpose of the spreadsheet is to track overtime hours and calculate pay variations. Each cell indicates one of 11 variables for the 'type' of overtime worked (i.e. Reg OT, Stat holiday OT, Acting Captain OT etc.) then the actual number of hours worked, then *sometimes* a code for the pay period. At its most complicated, a cell could look like this:
APCSTAT 10 LB4 (Acting PC on a STAT for 10 hours submitted late by 4 weeks)
or as simple as:
OTSTAT 6
I can have 11 different columns for each type of overtime, but I need a formula to add up the hours for each code, as well as a way to check for the 'late by' code and add it if necessary....help please anyone?
***UPDATE***
So I have come up with this formula which works for summing the hours worked:
=SUMPRODUCT(--(TRIM(REPLACE($C$5:$I$5,1,SEARCH(" ",$C$5:$I$5&" "),""))="AC"),--LEFT(0&$C$5:$I$5,SEARCH(" ",$C$5:$I$5&" ")))*5.39
but I still need to figure out a formula to catch late entries (cells containing LB#) since the above formula does not capture them at all...
Since this is a new process- I am open to ideas to better track this information, bearing in mind there are aprox. 200 employees and this is a 24/7 operation, so there will be a lot of information
dummy workbook attached - thanks!
Bookmarks