| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with IF function
I'm trying to work on a payroll spreadsheet and have setup columns with Mon
through Fri and rows with employees name. For each day by employee I will enter the hrs worked or a letter if there is something other than hours worked (a "V" for vacation, for example). I have a column at the end that totals hours worked, vacation hours, and holiday hours. I have setup an IF statement that if a "V" is entered on any day it will automatically put 8 hours in the vacation column. The problem I am having is that if 2 vacation days are used, I can't get 16 hours to come up (or 24 hours for 3 days vacation, etc.). I've included an example below what it looks like: M T W T F Hrs Worked Vac Hrs Jane Doe 8 8 8 8 V 32 8 This works! John Doe V V 8 8 8 24 8 Vac hrs should be 16! My IF statement is =IF(C6="v",8,IF(D6="v",8,IF(E6="v",8,IF(F6="v",8,I F(G6="v",8,""))))) I cannot figure out how to add two or more V's together (in 8 hour increments) to accurately reflect the vacation days. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with IF function
Use
=Countif(C6:G6,"V")*8 "JKA" wrote: I'm trying to work on a payroll spreadsheet and have setup columns with Mon through Fri and rows with employees name. For each day by employee I will enter the hrs worked or a letter if there is something other than hours worked (a "V" for vacation, for example). I have a column at the end that totals hours worked, vacation hours, and holiday hours. I have setup an IF statement that if a "V" is entered on any day it will automatically put 8 hours in the vacation column. The problem I am having is that if 2 vacation days are used, I can't get 16 hours to come up (or 24 hours for 3 days vacation, etc.). I've included an example below what it looks like: M T W T F Hrs Worked Vac Hrs Jane Doe 8 8 8 8 V 32 8 This works! John Doe V V 8 8 8 24 8 Vac hrs should be 16! My IF statement is =IF(C6="v",8,IF(D6="v",8,IF(E6="v",8,IF(F6="v",8,I F(G6="v",8,""))))) I cannot figure out how to add two or more V's together (in 8 hour increments) to accurately reflect the vacation days. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with IF function
Oh man....worked perfectly....Thanks! I need to brush up on my functions :-O
"Sheeloo" wrote: Use =Countif(C6:G6,"V")*8 "JKA" wrote: I'm trying to work on a payroll spreadsheet and have setup columns with Mon through Fri and rows with employees name. For each day by employee I will enter the hrs worked or a letter if there is something other than hours worked (a "V" for vacation, for example). I have a column at the end that totals hours worked, vacation hours, and holiday hours. I have setup an IF statement that if a "V" is entered on any day it will automatically put 8 hours in the vacation column. The problem I am having is that if 2 vacation days are used, I can't get 16 hours to come up (or 24 hours for 3 days vacation, etc.). I've included an example below what it looks like: M T W T F Hrs Worked Vac Hrs Jane Doe 8 8 8 8 V 32 8 This works! John Doe V V 8 8 8 24 8 Vac hrs should be 16! My IF statement is =IF(C6="v",8,IF(D6="v",8,IF(E6="v",8,IF(F6="v",8,I F(G6="v",8,""))))) I cannot figure out how to add two or more V's together (in 8 hour increments) to accurately reflect the vacation days. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with IF function
Hi,
Since the standard work hours for the week are 32, you can also do 32-H6 -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "JKA" wrote in message ... I'm trying to work on a payroll spreadsheet and have setup columns with Mon through Fri and rows with employees name. For each day by employee I will enter the hrs worked or a letter if there is something other than hours worked (a "V" for vacation, for example). I have a column at the end that totals hours worked, vacation hours, and holiday hours. I have setup an IF statement that if a "V" is entered on any day it will automatically put 8 hours in the vacation column. The problem I am having is that if 2 vacation days are used, I can't get 16 hours to come up (or 24 hours for 3 days vacation, etc.). I've included an example below what it looks like: M T W T F Hrs Worked Vac Hrs Jane Doe 8 8 8 8 V 32 8 This works! John Doe V V 8 8 8 24 8 Vac hrs should be 16! My IF statement is =IF(C6="v",8,IF(D6="v",8,IF(E6="v",8,IF(F6="v",8,I F(G6="v",8,""))))) I cannot figure out how to add two or more V's together (in 8 hour increments) to accurately reflect the vacation days. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
| LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
| Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
| Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
| Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions | |||
Linear Mode
