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


"Ra" wrote:

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.

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

Regards
Fred

"Ra" wrote in message
...
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.

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))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Ra" wrote:

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.