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
###########

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
###########

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))

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
###########

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
###########