B
Babylynn
I have a table used for attendance tracking of violations, using a list of
Alpha codes: L = late, A= Absent. I then count these text values by using
Sumproduct to evaluate a 12 month period of time, based on the hire date of
the employee, with a current column of dates. In the same column, I want to
place numeric values to add up attendance hours, using the same rolling 12
month period, forward of the anniversary date.
Dan Smith anniversarry date 1/1/2009
Today is 4/30/2009
Date
1/1/2009 A (this value represents "absent")
1/2/2009
1/3/2009
1/4/2009 8 (this value represents 8 hours vacation)
1/5/2009
1/6/2009
1/7/2009
1/8/2009 L (this value represents "late")
ON the spread sheet there is an upper section that calculates in table how
many hours of vacation are available based on tenure, in column Q. It ranges
for 40 years. So if for example the employee has 5 years service, it will
return a value of 80 hours, in Column Q row 32. Then to the right in column
R, I accumulate the actual hours used, then in another cell, I calculate the
balance. So if it sees a value in column Q, it will reference the list of
dates in Column O, and sum the numbers in Column R, for a balance of vacation
hours used. The >< formulas compare their anniversary date to the dates in
column O, and keep a running range based on the current date.
My problem is this. I want to use Text and numbers in the same column, and
when I use the formula below, I get #VALUE! If I keep the text and numbers
in 2 separate columns, it all works just fine.
Formula for obtaining the numeric vacation totals on running 12 month range:
=IF(Q32>0,SUMPRODUCT((O$137:O$9000>=O32)*(O$137:O$9000<O33)*R$137:R$9000),0)
Formula for obtaining the text count for lates ("L"), on running 12 month
range:
m6 = 1 year ago from today
=SUMPRODUCT((O$137:O$9002>$M$6)*(Q$137:Q$9002="L"))
Alpha codes: L = late, A= Absent. I then count these text values by using
Sumproduct to evaluate a 12 month period of time, based on the hire date of
the employee, with a current column of dates. In the same column, I want to
place numeric values to add up attendance hours, using the same rolling 12
month period, forward of the anniversary date.
Dan Smith anniversarry date 1/1/2009
Today is 4/30/2009
Date
1/1/2009 A (this value represents "absent")
1/2/2009
1/3/2009
1/4/2009 8 (this value represents 8 hours vacation)
1/5/2009
1/6/2009
1/7/2009
1/8/2009 L (this value represents "late")
ON the spread sheet there is an upper section that calculates in table how
many hours of vacation are available based on tenure, in column Q. It ranges
for 40 years. So if for example the employee has 5 years service, it will
return a value of 80 hours, in Column Q row 32. Then to the right in column
R, I accumulate the actual hours used, then in another cell, I calculate the
balance. So if it sees a value in column Q, it will reference the list of
dates in Column O, and sum the numbers in Column R, for a balance of vacation
hours used. The >< formulas compare their anniversary date to the dates in
column O, and keep a running range based on the current date.
My problem is this. I want to use Text and numbers in the same column, and
when I use the formula below, I get #VALUE! If I keep the text and numbers
in 2 separate columns, it all works just fine.
Formula for obtaining the numeric vacation totals on running 12 month range:
=IF(Q32>0,SUMPRODUCT((O$137:O$9000>=O32)*(O$137:O$9000<O33)*R$137:R$9000),0)
Formula for obtaining the text count for lates ("L"), on running 12 month
range:
m6 = 1 year ago from today
=SUMPRODUCT((O$137:O$9002>$M$6)*(Q$137:Q$9002="L"))