| Home |
| Search |
| Today's Posts |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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(D2:D8,"FW")*8 If FW could occur in column C, change this to =COUNTIF(C2:D8,"FW")*8 So the total hours is given by =SUM(C2:D8) + COUNTIF(D2:D8,"FW")*8 We want to max this out at 40, so we use MIN function =MIN(SUM(C2:D8)+COUNTIF(D2:D8,"FW")*8,40) I have this in To compute over time, in D9 I used =MAX(0,(SUM(C2:D8)+COUNTIF(D2:D8,"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(C1:D15)} 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 | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions | |||
| lookup formulas dependent upon lookup formulas | Excel Worksheet Functions | |||
| automatically copy formulas down columns or copy formulas all the | Excel Worksheet Functions | |||
| Formulas not evaluated, Formulas treated as strings | Excel Discussion (Misc queries) | |||
| formulas for changing formulas? | Excel Discussion (Misc queries) | |||
Threaded Mode
