NETWORKDAY issue

**mrcasado** · 01-13-2009, 08:19 AM

At present I have a spreadsheet that brings back the start date of an incident, and the end date of incident. Subtracting one from the other gives the time taken on the incident, but if it runs over a weekend that distorts the tiem taken. Is there a way to give the tiem taken for an incident in days, hours and so on?

**oldchippy** · 01-13-2009, 08:50 AM

Hi,

Welcome to the forum, if you want to include week-ends and have your date/time in
A1=01/01/2009 15:00:00
B1=13/01/2009 19:23:00
C1=INT(B1-A1) &" Days " & INT(MOD(B1-A1,INT(B1-A1))*24) & " Hours and " &MINUTE(B1-A1) & " Minutes"

**DonkeyOte** · 01-13-2009, 08:58 AM

or

C1: =B1-A1
C1: Custom Format = d "Days" h "Hours" m "Minutes"

Though I'm not entirely sure we answering your question... could you provide some examples of your values and intended results ?

**mrcasado** · 01-13-2009, 09:41 AM

I'm trying to omit the weekends, so it's only counting the workdays

The example I have from 02/01/2009 09:26 to 05/01/2009 08:12

Which, if you count the weekend is nearly 3 days, but I just need the workdays, so take the weekend out where necessary

Thanks again

**Shijesh Kumar** · 01-13-2009, 09:58 AM

Hi,

Have you tried using NETWORKDAYS function available with Analysis Tool Pak.

For more detail.. Type NETWORKDDAYS in excel help window

**DonkeyOte** · 01-13-2009, 10:38 AM

Networkdays won't obviously give you the duration in hours etc... for that you need to do some additional work with the NETWORKDAYS function:

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

where A1 & B1 hold start & end datetime values respectively and holidays is an optional named range containing public holiday dates that are to be excluded from the calculation also.

note the above would not work were the start/end dates to fall on weekends/public holidays.

you can format the result as detailed previously to give duration in the requisite format.

check out daddylonglegs posts' on all things NETWORKDAY... you're bound to find a solution that works for you amongst that little goldmine of information...

**Shijesh Kumar** · 01-13-2009, 10:40 AM

If you know the no. of days...

You can easily convert it into hour -- days * 24 and minutes days * 24 * 60 and seconds days * 24 * 60 * 60

**DonkeyOte** · 01-13-2009, 10:48 AM

I'm afraid this is not the case:

If as per OP ex.:

A1: Start: Friday 2nd Jan 09:26
B1: End: Monday 5th Jan 08:12

NETWORKDAYS(A1,B1) returns 2 as it is inclusive of both dates - ie Friday & Monday are both working days. So firstly you need to discount 1 day.

If you do that and * 24 you're saying 24 hours have passed which is not correct.

The amount of time that has passed is:

09:26 to Midnight on Friday + 8 hours & 12 minutes on Monday.

The correct result is therefore essentially:

(1-TIME(9,26,0))+TIME(8,12,0) -> ie 22 hrs 46 mins.

Or another way - 1 day less the difference in time between 9:26 and 8:12 -> 1 day - 1 hour and 14 mins.

**mrcasado** · 01-13-2009, 11:23 AM

Originally Posted by DonkeyOte

Networkdays won't obviously give you the duration in hours etc... for that you need to do some additional work with the NETWORKDAYS function:

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

where A1 & B1 hold start & end datetime values respectively and holidays is an optional named range containing public holiday dates that are to be excluded from the calculation also.

note the above would not work were the start/end dates to fall on weekends/public holidays.

you can format the result as detailed previously to give duration in the requisite format.

check out daddylonglegs posts' on all things NETWORKDAY... you're bound to find a solution that works for you amongst that little goldmine of information...

This has done exactly what I wanted, fantastic

NETWORKDAY issue

LinkBack

Thread Tools

Rate This Thread

Display

NETWORKDAY issue

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions