calculate the number of working hours between two dates

[sureshchinna_sb]

I need to calculate the number of working hours between two dates and display the result as the number of hours and minutes in an excel sheet. This in itself is not a problem, but I want to use only office hours in the function, i.e., ignore weekends and do not calculate hours after 6:00 and before 19:00, public holidays excluded etc.

[daddylonglegs]

If you have start date in A2 and end date in B2 try

=NETWORKDAYS(A2,B2,holidays)*("19:00"-"06:00")

format result cell as [h]:mm

where holidays is a named range containing holiday dates

Note: NETWORKDAYS is part of Analysis ToolPak addin, this includes hours on both start and end date

[sureshchinna_sb]

Previously i worked with the following formula to calculate number of working hours

If start date in A2 and end date in B2

Networkdays(A2,B2)-1-Mod(A2,1)+Mod(B2,1)

i got the number of working hours now i should subtract the non working hours from 19:01 to 5:59

[daddylonglegs]

OK, That means you have date and time in A2 and B2, yes?

You can amend in a similar fashion

=(Networkdays(A2,B2)-1)*("19:00"-"06:00")-Mod(A2,1)+Mod(B2,1)

Note: A2 and B2 should be within working hours. If this may not be the case post back.....

[karimpoor]

I simply need to subtract two dates and two hours and display the reult in hour. Example below is :

1/25/2012 18:17 01/27/2012 02:57

The result shoul be 32:40 min. Can someone help to provide the formula