Adding hours

[Yitzhack]

All,
I'm trying to add hours per week B2:G2 but i cannot make it work please
help. I have this formula to add the hours daily. the formula needs to return
hours worked in the same format

=+IF(+(B3-B2)*24<0,-(B3-B2)*24,+(B3-B2)*24)+IF(+(B5-B4)*24<0,-(B5-B4)*24,+(B5-B4)*24)

 

[Yitzhack]

sorry, this question was for excel programimg

 

[Fred Smith]

Here's a simplified version of your formula. First, ditch the supurfluous +
signs,
=IF((B3-B2)*24<0,-(B3-B2)*24,(B3-B2)*24)+IF((B5-B4)*24<0,-(B5-B4)*24,(B5-B4)*24)

Second, the Abs function simplifies this significantly:
=Abs(b3-b2)*24+Abs(b5-b4)*24

My guess as to the answer to your question is:

=abs(sum(b3:g3)-sum(b2:g2))*24+abs(sum(b5:g5)-sum(b5:g5))*24

Regards,
Fred

 

[Sandy Mann]

Fred,

Did you test your formula?

Assuming that the OP is using the test: =+IF(+(B3-B2)*24<0 because B3 is
smaller then B2, (ie something like 23:00 in B2 and 05:00 in B3) then your
formula returns 108 when formatted as General whereas it is obvioulsy 6 x 6
hours = 36

=SUMPRODUCT(MOD((B3:G3-B2:G2),1))*24
returns 36 when formatted a General and assuming all Row 4 entries are 6:00
and Row 5 are 12:00

=SUMPRODUCT(MOD((B3:G3-B2:G2),1)+(B5:G5-B4:G4))*24

returns 72 when formatted as General.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

Here's a simplified version of your formula. First, ditch the supurfluous
+ signs,
=IF((B3-B2)*24<0,-(B3-B2)*24,(B3-B2)*24)+IF((B5-B4)*24<0,-(B5-B4)*24,(B5-B4)*24)

Second, the Abs function simplifies this significantly:
=Abs(b3-b2)*24+Abs(b5-b4)*24

My guess as to the answer to your question is:

=abs(sum(b3:g3)-sum(b2:g2))*24+abs(sum(b5:g5)-sum(b5:g5))*24

Regards,
Fred

 

[Fred Smith]

As I said, it was a guess, because it wasn't obvious to me what the OP
wanted. He talked about range b2:g2, but it's only an assumption what's in
b3:g5.

Regards,
Fred.

Fred,

Did you test your formula?

Assuming that the OP is using the test: =+IF(+(B3-B2)*24<0 because B3 is
smaller then B2, (ie something like 23:00 in B2 and 05:00 in B3) then your
formula returns 108 when formatted as General whereas it is obvioulsy 6 x
6
hours = 36

=SUMPRODUCT(MOD((B3:G3-B2:G2),1))*24
returns 36 when formatted a General and assuming all Row 4 entries are
6:00
and Row 5 are 12:00

=SUMPRODUCT(MOD((B3:G3-B2:G2),1)+(B5:G5-B4:G4))*24

returns 72 when formatted as General.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

Sorry Fred, I did not mean to sound offensive, I'm just guessing as well.

I see that I failed to notice that the OP had the same test for the second
set of times so my fomrula should have been:

=SUMPRODUCT(MOD((B3:G3-B2:G2),1)+(MOD((B5:G5-B4:G4),1)))*24


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Fred Smith]

No offense taken, Sandy. Once you pointed out that the OP is likely handling
the situation of starting at 2300, ending at 0500, your formula makes more
sense. Maybe we'll see who guessed right.

Fred.