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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

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

ADVERTISEMENT

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

ADVERTISEMENT

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:
 

Watch MrExcel Video

Forum statistics

Threads
1,114,093
Messages
5,545,916
Members
410,711
Latest member
Josh324
Top