Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum and subtract hours
Hi,
I am trying to create a formula =IF(SUM(H7:H13)44,SUM(H7:H13)-44,SUM(H7:H13)) these are hours being added anything over 44 hrs a wk is overtime so I need the formula to be something like this: =IF(SUM(H7:H13)44,SUM(H7:H13)-N4,SUM(H7:H13)) +IF(SUM(H14:H20)44,SUM(H14:H20)-N4,SUM(H14:H20)) even the formula =SUM(H7:H13)-44 gives me ########### |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum and subtract hours
Hi
Are these hours in time format in Excel? Something like: 10:00 or some such? If so, the actual number (decimal) that this represents is actually 10/24 or 0.416667 since hours are stored as the fraction of a day, and 1.0 = 1 day. Hence, when you attempt to deduct 44 (rather than 44/24) you get ####### as Excel does not handle negative times. In which case, you may only have to use the following: =IF(SUM(H7:H13)44/24,SUM(H7:H13)-44/24,SUM(H7:H13)) Hope thsi helps! Richard On 4 Feb, 10:18, "D." wrote: Hi, I am trying to create a formula =IF(SUM(H7:H13)44,SUM(H7:H13)-44,SUM(H7:H13)) these are hours being added anything over 44 hrs a wk is overtime so I need the formula to be something like this: =IF(SUM(H7:H13)44,SUM(H7:H13)-N4,SUM(H7:H13)) +IF(SUM(H14:H20)44,SUM(H14:H20)-N4,SUM(H14:H20)) even the formula =SUM(H7:H13)-44 gives me ########### |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum and subtract hours
On Feb 4, 4:14 am, "RichardSchollar"
wrote: Hi Are these hours in time format in Excel? Something like: 10:00 or some such? If so, the actual number (decimal) that this represents is actually 10/24 or 0.416667 since hours are stored as the fraction of a day, and 1.0 = 1 day. Hence, when you attempt to deduct 44 (rather than 44/24) you get ####### as Excel does not handle negative times. In which case, you may only have to use the following: =IF(SUM(H7:H13)44/24,SUM(H7:H13)-44/24,SUM(H7:H13)) Hope thsi helps! Richard It Sure does thanks alot here's what I finished with =IF(SUM(H7:H13)44/24,SUM(H7:H13)-44/24,0)+IF(SUM(H14:H20)44/24,SUM(H14:H20)-44/24,SUM(H14:H20)) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum and subtract hours
So if I do 45 hours it results in 1 hour overtime
yet if I only do 43 - the result is 43 Hope you don't pay out on that basis :) But if you do - let me know Steve On Sun, 04 Feb 2007 11:14:20 -0000, RichardSchollar wrote: Hi Are these hours in time format in Excel? Something like: 10:00 or some such? If so, the actual number (decimal) that this represents is actually 10/24 or 0.416667 since hours are stored as the fraction of a day, and 1.0 = 1 day. Hence, when you attempt to deduct 44 (rather than 44/24) you get ####### as Excel does not handle negative times. In which case, you may only have to use the following: =IF(SUM(H7:H13)44/24,SUM(H7:H13)-44/24,SUM(H7:H13)) Hope thsi helps! Richard On 4 Feb, 10:18, "D." wrote: Hi, I am trying to create a formula =IF(SUM(H7:H13)44,SUM(H7:H13)-44,SUM(H7:H13)) these are hours being added anything over 44 hrs a wk is overtime so I need the formula to be something like this: =IF(SUM(H7:H13)44,SUM(H7:H13)-N4,SUM(H7:H13)) +IF(SUM(H14:H20)44,SUM(H14:H20)-N4,SUM(H14:H20)) even the formula =SUM(H7:H13)-44 gives me ########### |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum and subtract hours
What's in N4?
If you just want to calculate hours over 44 =MAX(total hours-44/24,0) "D." wrote: Hi, I am trying to create a formula =IF(SUM(H7:H13)44,SUM(H7:H13)-44,SUM(H7:H13)) these are hours being added anything over 44 hrs a wk is overtime so I need the formula to be something like this: =IF(SUM(H7:H13)44,SUM(H7:H13)-N4,SUM(H7:H13)) +IF(SUM(H14:H20)44,SUM(H14:H20)-N4,SUM(H14:H20)) even the formula =SUM(H7:H13)-44 gives me ########### |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|