If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Formulas--NEED HELP!
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. |
#2
|
|||
|
|||
Formulas--NEED HELP!
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 "CSUFurloughTracker" wrote in 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. |
#3
|
|||
|
|||
Formulas--NEED HELP!
"CSUFurloughTracker" wrote:
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" wrote in 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. |
#4
|
|||
|
|||
Formulas--NEED HELP!
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! =) "JoeU2004" wrote: "CSUFurloughTracker" wrote: 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" wrote in 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. |
#5
|
|||
|
|||
Formulas--NEED HELP!
Thank you so much! Your advice helped!
"Bernard Liengme" wrote: 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 "CSUFurloughTracker" wrote in 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. |
Thread Tools | |
Display Modes | |
|
|