Modify NETWORKINGDAYS

**JoshKyle** · 11-13-2008, 02:46 PM

I need help in trying to find a formula, here is the problem:

Cell A1 ="Start Date"
Cell B1 = "LeadTime" (Days required to complete task)
Range "Hols" = Holidays
Cell C1 = “EndDate” (A1+B1)

( However if "EndDate" falls on a Friday, then add 1 day to "EndDate". And If a Holiday falls in between "StartDate" and "EndDate" then add the holidays.)
For eg. Start Date = 13th Nov, LeadTime = 6 Days, Holidays = 17, 18 & 19th Nov, so EndDate will be 23rd Nov.

I have looked through the forum, and nothing quite meets what I need or seems to just go over my head.

I would like to thanking you all in advance for looking.

**e4excel** · 11-13-2008, 03:33 PM

Originally Posted by JoshKyle

I need help in trying to find a formula, here is the problem:

Cell A1 ="Start Date"
Cell B1 = "LeadTime" (Days required to complete task)
Range "Hols" = Holidays
Cell C1 = “EndDate” (A1+B1)

( However if "EndDate" falls on a Friday, then add 1 day to "EndDate". And If a Holiday falls in between "StartDate" and "EndDate" then add the holidays.)
For eg. Start Date = 13th Nov, LeadTime = 6 Days, Holidays = 17, 18 & 19th Nov, so EndDate will be 23rd Nov.

AS per your requirement, you will be providing the Lead Date and therefore you need to get the End Date..?

So the Lead time will only determine the work completed in business days.. and the End Date would be 6 days hence excluding the Week Offs like Sat/Sun and Holidays...

Letme work on this ..very interesting..

**e4excel** · 11-13-2008, 04:07 PM

Dear Josh,
If you are mentioning NETWORKDAYS function then it will exclude Sat/Sun as well as the Holidays that you specify then ideally the End Date will be 25 th Nov..

In your example there would 2 pairs of Sat/Sun and 3 holidays..

Thu13-Nov-08Fri14-Nov-08Sat15-Nov-08Sun16-Nov-08Mon17-Nov-08Tue18-Nov-08Wed19-Nov-08Thu20-Nov-08Fri21-Nov-08Sat22-Nov-08Sun23-Nov-08Mon24-Nov-08Tue25-Nov-08Wed26-Nov-08Thu27-Nov-08

So please clarify the same....

I have attached a file which can give you the End date, but this would be excluding the 2 pairs of Sat/Sun and Holidays and it is 25-Nov-08..

Im not an expert , hope this helps and wish someone can give yo a single formula answer..

**JoshKyle** · 11-13-2008, 04:18 PM

Sat and Sun is a working day, only exclude Fridays and Holidays.

**martindwilson** · 11-13-2008, 04:44 PM

do you want to exclude all fridays i.e a 3 day week end? if so i think you'd just add all fridays to holidays range.

**Ron Coderre** · 11-13-2008, 05:04 PM

With
A1: (StartDate...eg 13-NOV-2008)
B1: (Lead days....eg 6)
and
I1:I3 contains this list of holidays:
November 17, 2008
November 18, 2008
November 19, 2008

Not pretty...but, maybe this:
This formula counts the non-Friday, non-holiday dates and
adds one if the resulting date is a Friday

Please Login or Register  to view this content.

In the above example,
C1 returns: 22-NOV-2008

Please Login or Register  to view this content.

EDITED TO INCLUDE THIS POSSIBILITY:
This formula seems to return the same value as the above formula

Please Login or Register  to view this content.

Does that help?

**JoshKyle** · 11-13-2008, 05:30 PM

Originally Posted by martindwilson

do you want to exclude all fridays i.e a 3 day week end? if so i think you'd just add all fridays to holidays range.

The worksheets is to be used in Egypt where they only have Friday as their 1 day off a week. I've tried adding fridays to the Holiday range, using the NETWORKINGDAYS function it still classes Sat & Sunday as a weekend.

**daddylonglegs** · 11-13-2008, 05:33 PM

If you just want to exclude Fridays and holidays then, counting A1 as the first day you can use this formula

=SMALL(IF(WEEKDAY(ROW(INDIRECT("1:"&B1*10))+A1-1)<>6,IF(ISNA(MATCH(ROW(INDIRECT("1:"&B1*10))+A1-1,H1:H10,0)),ROW(INDIRECT("1:"&B1*10)))),B1)+A1-1

which needs to be confirmed with CTRL+SHIFT+ENTER

H1:H10 contains holiday dates

**martindwilson** · 11-13-2008, 06:12 PM

Egypt where they only have Friday as their 1 day off a week

cripes i'm not going to work in egypt then..ever

**JoshKyle** · 11-13-2008, 06:45 PM

Originally Posted by daddylonglegs

If you just want to exclude Fridays and holidays then, counting A1 as the first day you can use this formula

=SMALL(IF(WEEKDAY(ROW(INDIRECT("1:"&B1*10))+A1-1)<>6,IF(ISNA(MATCH(ROW(INDIRECT("1:"&B1*10))+A1-1,H1:H10,0)),ROW(INDIRECT("1:"&B1*10)))),B1)+A1-1

which needs to be confirmed with CTRL+SHIFT+ENTER

H1:H10 contains holiday dates

So far so good!, I've tested your formula on a small section of dates, and it's returning the correct dates...I will be testing it on the full calendar in the next few hours, and will let you know the results.

Many thanks to you all for your valuable time.

**JoshKyle** · 11-14-2008, 07:28 AM

[SOLVED] Thank you all for your contributions.

**[email protected]** · 12-17-2011, 07:36 AM

need a code for date picker that count only working days when apply to leave.even when selected days holidays or weekends are they.we should count only working days and person monthiy having only 2 leaves

**arthurbr** · 12-17-2011, 08:27 AM

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.