Calculating project time

[hansont]

I have a worksheet where column A is the Project start date and time and column B is the project end date and time. I need to calculate total project time minus weekends and holidays. Our work hours are 7 a.m. until 5 p.m.

I have been trying to use the following Excel formula but it doesn't calculte correctly.

=(NETWORKDAYS(A2,B2,C1:C8)-1)*(D1-C1)+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),D1,C1),D1)-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),D1,C1)
Where A2 = Start date, B2 = End date, C1:C8 = Holiday range, C1 = Day Start time and D1 = Day end Time.

Any help would be greatly appreciated.

Tammy

[Bob Phillips]

Could it be because you have C1 as part of the holiday range and the Day start time?

[hansont]

Sorry, this is a type o. The Holiday range is N2:N10.

I actually removed the Holiday range to see if this was causing the problem. Still didn't calculate correctly.

[Bob Phillips]

What answer are you expecting. I presume that the start and end dates don't count, but the hours do?

[hansont]

I have copied data directly from the spreadsheet. I took out the holiday range until I can at least get the weekly total working.

Formula
=((NETWORKDAYS(B2,C2)-1)*(N$2-M$2)+IF(NETWORKDAYS(C2,C2),MEDIAN(MOD(C2,1),N$2,M$2),N$2)-MEDIAN(NETWORKDAYS(B2,B2)*MOD(B2,1),N$2,M$2))*24

M2 = Day start 7:00 N2 = Day end 5:00

B2 C2 D2(result) formatted as d:h:mm
5/1/08 14:495/1/08 16:181:11:51

Expected result 0:1:29

This example doesn't even have after hours or weekends to calculate. It has been a long day, so forgive me if I am just overlooking something.

[Bob Phillips]

So now you are saying that B2 has the time as well as the date, whereas in post #1 you said the start time was in C1, end time in D1.

Now you say M2 is ths Day Start, N2 is the End Start, whereas in post #3 you said N2:N10 was the holiday range.

How about one final, accurate, not self-contradicting, easily understandable statement of what is where?

[hansont]

Formula
=((NETWORKDAYS(B2,C2)-1)*(N$2-M$2)+IF(NETWORKDAYS(C2,C2),MEDIAN(MOD(C2,1),N$2,M$2),N$2)-MEDIAN(NETWORKDAYS(B2,B2)*MOD(B2,1),N$2,M$2))*24

M2 = Day start 7:00 N2 = Day end 5:00

B2 C2 D2(result) formatted as d:h:mm
5/1/08 14:495/1/08 16:181:11:51

Expected result 0:1:29

[Bob Phillips]

I can get that result easily enough, but what happens fr more than 1 day? If the end date is 2nd May, would the result be 10:59, 25:59, or something else again?

[hansont]

Project started at 5-1-08 2:49
Day Ended at 5-1-08 5:00
Time for day 1 = 2 Hours 11 minutes

Day started 5-2-08 7:00
Projected ended at 5-2-08 4:16
Time for day 2 = 9 Hours 18 minutes

Total Project time = 11 hours 29 minutes or
1 10 hr work day 1 hour and 29 minutes.