# NETWORKDAYS Function

#### BCS2

##### Board Regular
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
ABCD
11/9/0411:00AM1/12/0410:49AM2.00
2
31/6/049:001/6/0411:001.00
Sheet1

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### fairwinds

##### MrExcel MVP
Hi,

Why not

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

#### BCS2

##### Board Regular
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.

#### BCS2

##### Board Regular
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
ABCD
11/9/0411:00AM1/12/0410:00AM2.9671:00
2
31/6/049:00AM1/6/0411:00AM1.0826: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

##### MrExcel MVP
OK,

Try this one

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

and format as before.

#### rrdonutz

##### Well-known Member
Perhaps this (not extensively tested):
Book1.xls
ABCD
11/9/0411:00AM1/12/0410:49AM23:49
21/9/0411:00AM1/13/0410:49AM47:49
31/6/049:00AM1/6/0411:00AM2:00
41/6/049:00AM1/7/0411:00AM26: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

##### Board Regular
Thanks Guys!!

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

#### megnin

##### Active Member
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.

Replies
4
Views
258
Replies
28
Views
807
Replies
2
Views
1K
Replies
0
Views
147
Replies
3
Views
391

1,186,172
Messages
5,956,359
Members
438,248
Latest member
Poysenberries

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back