Time Difference in hours between two date time stamps

Hi I have a query that I cant quite figure out

Cell A1 = 10/03/2007 11:00
Cell A2 = 10/04/2007 15:30

Assuming an 8 hour working day, can anyone please tell me how I can find the number of working hours between these two date time stamps?

Dying to figure this one out.

Assuming both times are within the shift period,

=B2 - B1 - (INT(B2) - INT(B1)) * 16/24 = 12:30

Thanks shg, but what did you mean that they should be within the same shift period? One is on October 3rd and the other is on October 4th. Also what if Saturday/Sunday was within this range and I wanted that excluded? How would that be possible?

You can use NETWORKDAYS function which is part of Analysis ToolPak add-in

=(NETWORKDAYS(A1,A2)-1)/3+MOD(A2,1)-MOD(A1,1)

format result cell as [h]:mm

I'm assuming that A1 and A2 will always be within the working day. If they may not be you need a more complex formula.....post back

If you don't have Analysis ToolPak you might be able to install.

Tools > add-ins > Tick "Analysis ToolPak"

Networkdays is useful because it also allows you to exclude holidays from the hours, should you wish to do so......but if you don't or you can't use Analysis ToolPak then you could get the same result with this formula

=(A2-A1-(INT((INT(A2)-INT(A1)+WEEKDAY(A1,3))/7)-MOD(A2,1)+MOD(A1,1))*2)/3

Again, this works on the basis that your start and end times will always be within business hours (and days)

I have a similar problem and need to calculate difference between two date-time stamps.

A1 = 9/1/10 5:37 PM
A2 = 9/2/10 7:57 PM

The service window is 12.5 hours from 9:30 AM to 10 PM.

I have tried two formulas
(NETWORKDAYS(A1,B1)-1)*"12:30"-MOD(A1,1)+MOD(B1,1)
(NETWORKDAYS(A1,B1)-1)*("22:00"-"09:30")-MOD(A1,1)+MOD(B1,1)

Both of these work fine but the problem arises when A1 is out of the Service window time i.e between 10 PM to 9:30 am.In this case the formula is not giving the right results.Please help.