| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding hours
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) -- Regards YM |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding hours
sorry, this question was for excel programimg
-- Regards YM "Yitzhack" wrote: 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) -- Regards YM |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding hours
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 "Yitzhack" wrote in message ... 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) -- Regards YM |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding hours
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. "Sandy Mann" wrote in message ... 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 Replace @mailinator.com with @tiscali.co.uk "Fred Smith" wrote in message ... 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 "Yitzhack" wrote in message ... 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) -- Regards YM |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding hours
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 Replace @mailinator.com with @tiscali.co.uk "Fred Smith" wrote in message ... 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. "Sandy Mann" wrote in message ... 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 Replace @mailinator.com with @tiscali.co.uk "Fred Smith" wrote in message ... 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 "Yitzhack" wrote in message ... 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) -- Regards YM |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Adding hours
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. "Sandy Mann" wrote in message ... 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 Replace @mailinator.com with @tiscali.co.uk "Fred Smith" wrote in message ... 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. "Sandy Mann" wrote in message ... 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 Replace @mailinator.com with @tiscali.co.uk "Fred Smith" wrote in message ... 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 "Yitzhack" wrote in message ... 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) -- Regards YM |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need help adding hours exceeding 24 hours | Excel Worksheet Functions | |||
| Adding hours and minutes | Excel Worksheet Functions | |||
| adding like job hours | Excel Discussion (Misc queries) | |||
| Adding hours and minutes | Excel Discussion (Misc queries) | |||
| Adding hours and minutes | Excel Discussion (Misc queries) | |||
Linear Mode
