Payroll attendance

[Guest]

Hello
I need help with a formula I'm using that is not functioning the way I need
it to. I am trying to represent employee hours worked, sick, vacation with a
letter such as "r"= 7.5 or 8 hrs, "u" =11 hrs, "s"= 8 or 11 hrs, "v"=8 or 11
etc....
I have three different shifts of hours the first one totals 81.25 (based on
bi-monthy average) the second and the third is 86.67. one of the shifts has
a modified schedule which usually consist of 11 days. This is where my
problem comes in. Because the fact that I have two shifts with the same
amount of scheduled hours, my formula will not calculate the proper number of
sick, vacation or other days I need to represent.
Now this is how it is working right now:

M7=COUNTIF($AP7:BT7, "S")*IF(E7=81.25,7.5,IF(E7=86.67,8,IF(E7=83.42,11)))
*note: I did not mention a shift with 83.42, that is because I don't have
one and need that to represent "11" hrs. Which means the above formula has
to also read 86.67 in the final part of the equation. How do I do this? Do
I need to represent it with another "countif" statement or an "if"?
How does 86.67 =8hr and/or 11 hrs without me having to manually adjust it
each time? Is there a way to colour code the selection in order for it to
tell the difference between the two and yet come up with the right answer?

HELP please!!! This has been plaquing me for sometime now.

 

[Ronald Dodge]

You could setup an index column with your other data to take both type of
pay and shift into account, then have a table like the one below:

R|1 7.5
R|2 8
R|3 8
S|1 8
S|2 11
S|3 11
U|1 8
U|2 11
U|3 11
V|1 8
V|2 11
V|3 11

You could then use either the VLOOKUP or the combination of INDIRECT,
ADDRESS, and MATCH functions to find and record the proper values, depending
on what your circumstances are.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[Guest]

Did Ronald's response help? It seems tha your describing a relatively easy
problem to solve with some more advanced functions, but its very difficult to
follow exactly what your spreadsheet is trying to accomplish. Assume reader
can not see your spreadsheet.

 

[Guest]

Hi Dave and Ronald,
I forgot to mention before that the solution that Ronald came up with, I
already thought about and tried to create the formula for. My problem is I
could not make the formula work. I need another "countif" for each one but I
haven't figured how to lay it out so it reads properly. Ronald's statement
reassured me that I was on the right track. Now with the formula I've given
you, how would you correct it so that it reads exactly what Ronald was
relating to so that any cell that read "s1 s2 s3 would reconize and count?
Sorry if I made this sound more complicated than it needs to be.

 

[Ronald Dodge]

Are you saying that you need to be able to count how many of those sick
leaves are first shift and so on? If so, let's look at the following:

Given:

Using a table similar to a database type format with each row representing
as a record starting from row 5 with row 4 being the header row.

Column A being the first name column
Column B being the last name column
Column C being the shift column.
Column D being the department column
Column E being the type of pay column
Column F being the number of hours
Column G being the index column for type of pay and shift
Just for example purpose, let's say you have 300 records, or the last record
is on row 304.

In cell G5, you would have the formula as:

=UPPER(E5) & "|" & C5

You setup a table in K1:O4
L1 = R
M1 = S
N1 = U
O1 = V
K2 = 1
K3 = 2
K4 = 3

Formula in L2:

=COUNTIF($G$5:$G$304,L$1 & "|" & $K2)

You could then copy that and paste it to the range of L2:O4

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000