Hours difference

R

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
 
L

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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top