NETWORKDAYS Function

[BCS2]

I am trying to determine the elapsed time between a start and dtop time. I am doing so now by subtracting one from the other. Which works fine until I get to a weekend.

I am assuming this function counts up until Friday night @ midnight and roll into Monday. The problem I have run into is that the results are displayed as whole days. I would like to display as [H]:mm.

An example of a test I am using is below.

Thanks All.

Estimate Tracking Example.xls
	A	B	C	D
1	1/9/0411:00AM	1/12/0410:49AM	2.00
2
3	1/6/049:00	1/6/0411:00	1.00
Sheet1

 

[fairwinds]

Hi,

Why not

=B1-A1 and format as [H]:mm

 

[jpettit]

this is a double post BCS2
http://www.mrexcel.com/board2/viewtopic.php?p=355628

 

[BCS2]

It will work if I simply subtract the two dates as long as the dates are weekdays. The trouble comes in when I have a start date on Friday afternoon and a finish date on Monday morning. I don't want to add in the weekend time in my turn-time calculation.

 

[fairwinds]

Seems that you have some answers in your original thread.

 

[BCS2]

None of those worked. You're right, I should have posted a reply in that thread.

It won't happen again.

The problem with the formula that was suggested

=(NETWORKDAYS(A1,B1))+((B1-A1)-(NETWORKDAYS(A1,B1))-INT((B1-A1)-(NETWORKDAYS(A1,B1))))

is that when the start date and end date are the same, it returns a 1. Is this a limitation in the NETWORKDAYS function? Example below. Column C is formatted as number. Column D is formatted as [H]:mm.

Estimate Tracking Example.xls
	A	B	C	D
1	1/9/0411:00AM	1/12/0410:00AM	2.96	71:00
2
3	1/6/049:00AM	1/6/0411:00AM	1.08	26:00
Sheet1

I could use a different function when this occurs, but the data is being input on a weekly basis as tasks are completed and I never know if the start / end date will be the same or not in a given row of data.

Is there any way to alter this formula to yield the result I need?

Thanks

ray:

 

[fairwinds]

OK,

Try this one

=NETWORKDAYS(INT(A1),INT(B1))+MOD(B1,1)-MOD(A1,1)-1

and format as before.

 

[rrdonutz]

Perhaps this (not extensively tested):

Book1.xls
	A	B	C	D
1	1/9/0411:00AM	1/12/0410:49AM	23:49
2	1/9/0411:00AM	1/13/0410:49AM	47:49
3	1/6/049:00AM	1/6/0411:00AM	2:00
4	1/6/049:00AM	1/7/0411:00AM	26:00
5
Sheet3

The formula in C1 is:

=B1-A1-INT(B1)+INT(A1)-1+NETWORKDAYS(A1,B1)

It works with the limited data I posted. I do know that it won't work if either of your start/stop times is on a weekend.

Tom

 

[BCS2]

Thanks Guys!!

I am working on fairwinds formula at the moment. Working so far.

 

[megnin]

Using Fairwinds formula with a start date on 5/24/05 and and end date on 7/01/05 it gives me and elapsed time of 4 hours and 33 minutes.