NETWORKDAYS Function

BCS2

Board Regular
Joined
Dec 8, 2003
Messages
67
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.

BCS2

Board Regular
Joined
Dec 8, 2003
Messages
67
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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Seems that you have some answers in your original thread.
 

BCS2

Board Regular
Joined
Dec 8, 2003
Messages
67
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

:pray:
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
OK,

Try this one

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

and format as before.
 

rrdonutz

Well-known Member
Joined
Jan 15, 2003
Messages
564
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
Joined
Dec 8, 2003
Messages
67
Thanks Guys!! :cool:

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

megnin

Active Member
Joined
Feb 27, 2002
Messages
340
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. :confused:
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top