Hours difference

[Ra]

Need to calculate difference in terms of hours....weekend issue.
1st date and time - 1/29/10 (friday) 1:00am
2nd date and time - 2/1/10 (monday) 1:00am
By calculating manually, the result that i would need to show is 24 hrs
(based on my shop floor operations). Anything more than 24hrs is an issue at
my operations.
How would i do this in excel?
Thank you.

 

[מיכ×ל (מיקי) ×בידן]

Hmmm...., if the 01:00 can be ignored you may try this:
In A1 - Start Date
In A2 - End Date
In A3 - The hereunder Array-Formula:
{=24*(SUM(N(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))<>7))-SUM(N(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))=1))-1))
The formula should be entered with CTRL+SHIFT+ENTER rather than with simply
ENTER.
The curly brackets {} are not to be typed manually, those are entered by the
“Excelâ€, when the formula is entered as an Array formula.
Micky

 

[Fred Smith]

Try this:
=(b1-a1)*24-(weekday(b1)<weekday(a1))*48

Regards
Fred

 

[Luke M]

If start time in A1, end time in A2, this will work provided neither start or
stop occurs on a weekend:

=A2-A1-(INT(A2)-INT(A1)-(NETWORKDAYS(A1,A2)-1))