Calculating net working hours

Hello Everyone!

I am trying to calculate net working hours difference between 2 cells where date and time are in "12/21/2012 1:32:00 PM" format, so say for example cell A2 has "12/21/2012 1:32:00 PM" and b2 has "12/24/2012 6:24:00 AM" then i would like to know a formula which should give me the output as mentioned below:

cell a2 = 12/21/2012 1:32:00 PM; cell b2 = 12/24/2012 6:24:00 AM = cell c2 should give me the difference as 16:52
cell a2 = 12/20/2012 1:00:00 PM; cell b2 = 12/21/2012 6:00:00 PM = cell c2 should give me the difference as 29:00

Would be really great if someone can guide me, i just tried with this formula (hour(b2-a2)&":"&minute(b2-a2)) however it only works correctly if the difference between time are in AM & PM, but the formula does not work for the second scenario about where it gives me 05:00 which is wrong.

Regards,
Navin

The formula in column C is just:

=B1-A1

With the results formatted as hh:mm

Hello Andrew,

Thanks for your reply! I tried out this formula as well however it does not work correctly for the second scenario as it gives me output of 05:00 instead of 29:00

would appreciate if you can help with this scenario as well.

Regards,
Navin

Originally Posted by Andrew-R

The formula in column C is just:

=B1-A1

With the results formatted as hh:mm

12/20/12 1:00:00 PM
12/21/12 6:00:00 PM

Your format hh:mm = 5:00

format it like this: [h]:mm = 29:00

This produces the correct result for the 2nd example...

=TEXT((24*(INT(B1-A1)))+HOUR(B1-A1),"00") & ":" & TEXT(MINUTE(B1-A1),"00")

...but doesn't match your expected result for the 1st scenario, although I'm not quite sure why you expect there only to be 16 working hours between the 21st and the 24th of the month.

@ teetless mama thanks that works out well for the second scenario, however for the first scenario I am having problem

@andrew yes you are correct for the first scenario i forgot to mention is that we need to exclude holidays so the first scenario 21Dec2012 is Friday and 24Dec2012 is a Monday so the time should be exclude hours for saturday and sunday.

thanks to both for responding, however looking forward to have a good formula to solve this query

Thanks daddylonglegs!!! The formula provided by yourself is certainly helpful!!

However just to understand this stuff more clearly!

- Can you also share the complex formula that you are referring to as i might also have scenario mentioned in your reply.

- also since I have never used MOD funcrtion before can you please give me a little insight into this function?

Thanks a ton fren this is really helpful!!!!