Printable View
Hello, does anyone know the excel formula if I want to calculate the total working hours between two date and time stamps like so:
3/8/02 1:00
3/9/02 15:00
start work at 9:00
end work at 17:00
Result: 6.00 hrs (total "active" working time)
Thank you for any direction! ;)
Have a look at this, if it isn't exactly what you want it should still give you an idea of what to do:
Hello, Thank you but that does not calculate networkdays at all.
Can it you explain it a bit more then please?
Hello Martin,
Sorry for the delay, and I do appreciate your hint. Only this problem is really bugging me! I have attached all my work (including a rearrangement of your tip), see other sheets too for other examples that come close... I have surfed and surfed and adopted different little codes, but there does not seem to be a solid solution where the networkday and time calculations work for all tests! Thank you for any input or direction.
Take good care.
Hello Martin,
Sorry for the delay, and I do appreciate your hint. Only this problem is really bugging me! I have attached all my work (including a rearrangement of your tip), see other sheets too for other examples that come close... I have surfed and surfed and adopted different little codes, but there does not seem to be a solid solution where the networkday and time calculations work for all tests! Thank you for any input or direction.
Take good care.
I think I have done it :D
It works with all the examples you gave.
You can probably make it all in to one formula if you like.
I got bored:I am sure it can be shortened!Code:=IF(DAY(C8)=DAY(B8),(IF(((G8-(IF((G8-E8)<0,0,(G8-E8))))-(F8+(IF((D8-F8)<0,0,(D8-F8)))))<0,0,((G8-(IF((G8-E8)<0,0,(G8-E8))))-(F8+(IF((D8-F8)<0,0,(D8-F8))))))),((IF(((G8-F8)-(IF((G8-E8)<0,0,(G8-E8))))<0,0,((G8-F8)-(IF((G8-E8)<0,0,(G8-E8)))))+(IF(((NETWORKDAYS(B8,C8)-2)*(G8-F8))<0,0,((NETWORKDAYS(B8,C8)-2)*(G8-F8))))+(IF(((G8-F8)-(IF((D8-F8)<0,0,(D8-F8))))<0,0,((G8-F8)-(IF((D8-F8)<0,0,(D8-F8)))))))))
Wow, that is one big line!