Formulas--NEED HELP!

[CSUFurloughTracker]

Basically, I am trying to do a tracking sheet for hours and the "California
State" Furloughs. If some one works on their Furlough day they should be
entering "FW" into the cell. The totals at the bottom of the page should then
read that FW=8 hours plus any additional time entered for the week. If
someone works over 40 hours then i need to have the weekly total split into
"regular hours" and "overtime hours."
This is what I have
Week 2
August Date REG OT
Sunday 8/2/09
Monday 8/3/09 10.00
Tuesday 8/4/09 10.00
Wednesday 8/5/09 8.00
Thursday 8/6/09 9.00
Friday 8/7/09 FW
Saturday 8/8/09
Total weekly hours 37.00

This is what I need:
Week 2
August Date REG OT
Sunday 8/2/09
Monday 8/3/09 10.00
Tuesday 8/4/09 10.00
Wednesday 8/5/09 8.00
Thursday 8/6/09 9.00
Friday 8/7/09 FW
Saturday 8/8/09
Total weekly hours 40.00 5.00

I am trying to do two things...

1) Convert the sum of a range of cells {=SUM(C115)} to reflect the number
8 if "FW" is typed a cell within that range.
Next,
2) If the formula above is 40> then the cell need to convert over the values
if the above reaches over 40.

 

[Bernard Liengme]

It is such a pity we do not have a better way to display part of a
worksheet. Asy you will see beloew your data got a bit out of line.
However....
I have your numbers 10,19,8,9 in C3:C6, in D7 I have "FW"

To count the FW's and teat them as 8 hours, I will use:
=COUNTIF(D28,"FW")*8
If FW could occur in column C, change this to
=COUNTIF(C28,"FW")*8

So the total hours is given by =SUM(C28) + COUNTIF(D28,"FW")*8
We want to max this out at 40, so we use MIN function
=MIN(SUM(C28)+COUNTIF(D28,"FW")*8,40)
I have this in
To compute over time, in D9 I used
=MAX(0,(SUM(C28)+COUNTIF(D28,"FW")*8-40))
The MAX stops us getting a negative value

best wishes

 

[JoeU2004]

The totals at the bottom of the page should then read that FW=8 hours
[...].
If someone works over 40 hours then i need to have the weekly total split
into "regular hours" and "overtime hours."

Bernard provided a suitable answer based on your assertions.

However, if you are tracking hours for other state workers, I think you
should review the Calif overtime regulations. Your assertions and examples
do not match my understanding.

First, if an employee is paid hourly, I question the assumption that FW
should always mean 8 hours. You might want to choose a different form of
recording the information, e.g. 1FW, 2FW etc, meaning 1 FW hour, 2 FW hours,
etc.

Second, overtime hours are, in part, the hours over 8 in a day or the hours
over 40 in a week. (As well as any hours worked on the 7th consecutive day
in a "workweek", which has specific definition.)

So I think the hours for Mon and Tue should be recorded as 8 REG and 2 OT,
and the hours for Thu should be 8 REG and 1 OT.

Finally, my interpretation is: daily OT hours should not be included in the
weekly hour total; otherwise, you could doubly-account OT hours.

For example, if someone worked 10 hours for each of 5 days in a week, you
should count 10 (2*5) OT hours in excess of 8 per day. If you include those
OT hours in the week hour total, you would also count 10 (50-40) OT hours in
excess of 40 for the week. A total of 20 OT hours. But I believe there are
only 10 OT hours in that week.

If my understanding is correct, this has implications for the formulas that
you should use.

PS: Calif law also requires paying at a different rate for OT hours in
excess of 12 on any day or all hours in excess of 8 on the 7th consecutive
workday. So perhaps you should have two categories (columns) of OT hours.
See http://www.dir.ca.gov/dlse/FAQ_Overtime.htm .


----- original message -----

 

[CSUFurloughTracker]

Thank you for the post; however, your response did not help with my question.
I am in payroll and I know the rules and regulations of the overtime
policies. Your assumptions lead to a specific classifaction of employees, not
taking into consideration other types of employess plus how the furlough
system works within our departments.
Again, I appreciate your response but I really just needded help with the
formulas. This is why I rated your response as "not being able to answer my
question". Thank you for your response. Have a great day! =)

The totals at the bottom of the page should then read that FW=8 hours
[...].
If someone works over 40 hours then i need to have the weekly total split
into "regular hours" and "overtime hours."

Bernard provided a suitable answer based on your assertions.

However, if you are tracking hours for other state workers, I think you
should review the Calif overtime regulations. Your assertions and examples
do not match my understanding.

First, if an employee is paid hourly, I question the assumption that FW
should always mean 8 hours. You might want to choose a different form of
recording the information, e.g. 1FW, 2FW etc, meaning 1 FW hour, 2 FW hours,
etc.

Second, overtime hours are, in part, the hours over 8 in a day or the hours
over 40 in a week. (As well as any hours worked on the 7th consecutive day
in a "workweek", which has specific definition.)

So I think the hours for Mon and Tue should be recorded as 8 REG and 2 OT,
and the hours for Thu should be 8 REG and 1 OT.

Finally, my interpretation is: daily OT hours should not be included in the
weekly hour total; otherwise, you could doubly-account OT hours.

For example, if someone worked 10 hours for each of 5 days in a week, you
should count 10 (2*5) OT hours in excess of 8 per day. If you include those
OT hours in the week hour total, you would also count 10 (50-40) OT hours in
excess of 40 for the week. A total of 20 OT hours. But I believe there are
only 10 OT hours in that week.

If my understanding is correct, this has implications for the formulas that
you should use.

PS: Calif law also requires paying at a different rate for OT hours in
excess of 12 on any day or all hours in excess of 8 on the 7th consecutive
workday. So perhaps you should have two categories (columns) of OT hours.
See http://www.dir.ca.gov/dlse/FAQ_Overtime.htm .


----- original message -----

Basically, I am trying to do a tracking sheet for hours and the
"California
State" Furloughs. If some one works on their Furlough day they should be
entering "FW" into the cell. The totals at the bottom of the page should
then
read that FW=8 hours plus any additional time entered for the week. If
someone works over 40 hours then i need to have the weekly total split
into
"regular hours" and "overtime hours."
This is what I have
Week 2
August Date REG OT
Sunday 8/2/09
Monday 8/3/09 10.00
Tuesday 8/4/09 10.00
Wednesday 8/5/09 8.00
Thursday 8/6/09 9.00
Friday 8/7/09 FW
Saturday 8/8/09
Total weekly hours 37.00

This is what I need:
Week 2
August Date REG OT
Sunday 8/2/09
Monday 8/3/09 10.00
Tuesday 8/4/09 10.00
Wednesday 8/5/09 8.00
Thursday 8/6/09 9.00
Friday 8/7/09 FW
Saturday 8/8/09
Total weekly hours 40.00 5.00

I am trying to do two things...

1) Convert the sum of a range of cells {=SUM(C115)} to reflect the
number
8 if "FW" is typed a cell within that range.
Next,
2) If the formula above is 40> then the cell need to convert over the
values
if the above reaches over 40.

 

[CSUFurloughTracker]

Thank you so much! Your advice helped!

It is such a pity we do not have a better way to display part of a
worksheet. Asy you will see beloew your data got a bit out of line.
However....
I have your numbers 10,19,8,9 in C3:C6, in D7 I have "FW"

To count the FW's and teat them as 8 hours, I will use:
=COUNTIF(D28,"FW")*8
If FW could occur in column C, change this to
=COUNTIF(C28,"FW")*8

So the total hours is given by =SUM(C28) + COUNTIF(D28,"FW")*8
We want to max this out at 40, so we use MIN function
=MIN(SUM(C28)+COUNTIF(D28,"FW")*8,40)
I have this in
To compute over time, in D9 I used
=MAX(0,(SUM(C28)+COUNTIF(D28,"FW")*8-40))
The MAX stops us getting a negative value

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email