| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to calculate hours worked on timecard using "IF" command
I have total hours in cell M11. In cell M13 I have a formula calculating
overtime hours: =IF(M1140,M11-40,0). What I'm trying to do is cause the cell with this formula to calculate the hours ONLY if the overtime hours are over 40 AND outside of PTO and/or Holiday hours. Here's an example: total hours (cell M12)= 42.33 overtime hours (cell M13)= 2.33 PTO hours (cell M14) = 8.00 Holiday hours (cell M15) = 8.00 Total work hours INCLUDES pto and holiday hours, so overtime hours should NOT be showing 2.33, but rather zero. How do I create/modify my current formula (above) to calculate this properly? Thanks! Melissa |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Trying to calculate hours worked on timecard using "IF" command
Maybe this
=MAX(M11-(M14+M15)-40,0) Mike "Melissa" wrote: I have total hours in cell M11. In cell M13 I have a formula calculating overtime hours: =IF(M1140,M11-40,0). What I'm trying to do is cause the cell with this formula to calculate the hours ONLY if the overtime hours are over 40 AND outside of PTO and/or Holiday hours. Here's an example: total hours (cell M12)= 42.33 overtime hours (cell M13)= 2.33 PTO hours (cell M14) = 8.00 Holiday hours (cell M15) = 8.00 Total work hours INCLUDES pto and holiday hours, so overtime hours should NOT be showing 2.33, but rather zero. How do I create/modify my current formula (above) to calculate this properly? Thanks! Melissa |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Trying to calculate hours worked on timecard using "IF" comman
Ok, but if the 42.33 hours WERE regular worked hours (not including PTO or
holiday), how do I make the formula look right with the other part of the formula {eg =IF(M1140,M11-40,0)}? Where do I put all the parethesis, commas, etc? How do I put both formulas together so that both calculations take effect? "Mike H" wrote: Maybe this =MAX(M11-(M14+M15)-40,0) Mike "Melissa" wrote: I have total hours in cell M11. In cell M13 I have a formula calculating overtime hours: =IF(M1140,M11-40,0). What I'm trying to do is cause the cell with this formula to calculate the hours ONLY if the overtime hours are over 40 AND outside of PTO and/or Holiday hours. Here's an example: total hours (cell M12)= 42.33 overtime hours (cell M13)= 2.33 PTO hours (cell M14) = 8.00 Holiday hours (cell M15) = 8.00 Total work hours INCLUDES pto and holiday hours, so overtime hours should NOT be showing 2.33, but rather zero. How do I create/modify my current formula (above) to calculate this properly? Thanks! Melissa |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Trying to calculate hours worked on timecard using "IF" comman
Hi,
You clearly never tried this formula which returns exactly the same answer as Shane's much longer version. Mike "Melissa" wrote: Ok, but if the 42.33 hours WERE regular worked hours (not including PTO or holiday), how do I make the formula look right with the other part of the formula {eg =IF(M1140,M11-40,0)}? Where do I put all the parethesis, commas, etc? How do I put both formulas together so that both calculations take effect? "Mike H" wrote: Maybe this =MAX(M11-(M14+M15)-40,0) Mike "Melissa" wrote: I have total hours in cell M11. In cell M13 I have a formula calculating overtime hours: =IF(M1140,M11-40,0). What I'm trying to do is cause the cell with this formula to calculate the hours ONLY if the overtime hours are over 40 AND outside of PTO and/or Holiday hours. Here's an example: total hours (cell M12)= 42.33 overtime hours (cell M13)= 2.33 PTO hours (cell M14) = 8.00 Holiday hours (cell M15) = 8.00 Total work hours INCLUDES pto and holiday hours, so overtime hours should NOT be showing 2.33, but rather zero. How do I create/modify my current formula (above) to calculate this properly? Thanks! Melissa |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Trying to calculate hours worked on timecard using "IF" command
Hi,
Try =IF(M11-M14-M1540,M11-M14-M15-40,0) -- If this helps, please click the Yes button Cheers, Shane Devenshire "Melissa" wrote: I have total hours in cell M11. In cell M13 I have a formula calculating overtime hours: =IF(M1140,M11-40,0). What I'm trying to do is cause the cell with this formula to calculate the hours ONLY if the overtime hours are over 40 AND outside of PTO and/or Holiday hours. Here's an example: total hours (cell M12)= 42.33 overtime hours (cell M13)= 2.33 PTO hours (cell M14) = 8.00 Holiday hours (cell M15) = 8.00 Total work hours INCLUDES pto and holiday hours, so overtime hours should NOT be showing 2.33, but rather zero. How do I create/modify my current formula (above) to calculate this properly? Thanks! Melissa |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Trying to calculate hours worked on timecard using "IF" comman
Shane,
Thank You, Thank You!! Your formula worked! You've just saved my bacon! Melissa "Shane Devenshire" wrote: Hi, Try =IF(M11-M14-M1540,M11-M14-M15-40,0) -- If this helps, please click the Yes button Cheers, Shane Devenshire "Melissa" wrote: I have total hours in cell M11. In cell M13 I have a formula calculating overtime hours: =IF(M1140,M11-40,0). What I'm trying to do is cause the cell with this formula to calculate the hours ONLY if the overtime hours are over 40 AND outside of PTO and/or Holiday hours. Here's an example: total hours (cell M12)= 42.33 overtime hours (cell M13)= 2.33 PTO hours (cell M14) = 8.00 Holiday hours (cell M15) = 8.00 Total work hours INCLUDES pto and holiday hours, so overtime hours should NOT be showing 2.33, but rather zero. How do I create/modify my current formula (above) to calculate this properly? Thanks! Melissa |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Trying to calculate hours worked on timecard using "IF" comman
Glad to help, and thanks for the feedback.
Cheers, Shane Devenshire "Melissa" wrote: Shane, Thank You, Thank You!! Your formula worked! You've just saved my bacon! Melissa "Shane Devenshire" wrote: Hi, Try =IF(M11-M14-M1540,M11-M14-M15-40,0) -- If this helps, please click the Yes button Cheers, Shane Devenshire "Melissa" wrote: I have total hours in cell M11. In cell M13 I have a formula calculating overtime hours: =IF(M1140,M11-40,0). What I'm trying to do is cause the cell with this formula to calculate the hours ONLY if the overtime hours are over 40 AND outside of PTO and/or Holiday hours. Here's an example: total hours (cell M12)= 42.33 overtime hours (cell M13)= 2.33 PTO hours (cell M14) = 8.00 Holiday hours (cell M15) = 8.00 Total work hours INCLUDES pto and holiday hours, so overtime hours should NOT be showing 2.33, but rather zero. How do I create/modify my current formula (above) to calculate this properly? Thanks! Melissa |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
| Functions for "current" & "previous" month to calculate data | Excel Worksheet Functions | |||
| Drivers Hours Timesheet - Calculate Hours Worked on Weekly Basis | Excel Discussion (Misc queries) | |||
| How do you subtract time as in a timecard to calculate hours work | Excel Worksheet Functions | |||
| inserting a conditional "go to" command on a excel "if" function | Excel Worksheet Functions | |||
Hybrid Mode
