networkdays

[yak10]

I had a question yesterday that was quite confusing and received a request from a few of you to upload the document - which I did not have a chance to do. I've thought about it this a little more. Before I upload the doc, I think I can resolve this question by finding a similar function to the networkdays functions. Here's what should resolve my issue:

networkday function solves for the number of business days between a range of two dates.

Is there a similar function that returns a date that would be x number of days from the start date?

In essence:
networkdays=start day - end day

I need:
End date=start date + networkdays (10)

I want to solve for that end date if I know I need a date that is 10 bus days from today.

Thanks for the help in advance......

[Palmetto]

Is there a similar function that returns a date that would be x number of days from the start date?
I need:
End date=start date + networkdays (10)

I want to solve for that end date if I know I need a date that is 10 bus days from today.

If A1 holds the date
If B1 holds the result of the networkdays formula, then

Formula for End date: = A1 + B1

[DonkeyOte]

I think you mean:

=WORKDAY(A1,10)

which will add 10 working days to date in A1, as per NETWORKDAYS there is optional 3rd parameter for public holidays and pre XL2007 also requires activation of the Analysis ToolPak Add-In (via Tools -> Add-Ins)

[yak10]

The workday functions works, however (here's the tough part), I need to include Saturdays as workdays........Thanks again for the help

[DonkeyOte]

Yes that makes things significantly more complex... to be honest there's only one or two people (IMO) who are able to do these types of calcs with any level of precision and elegance - I will drop them a note to take a look in on your thread.

[yak10]

I can do it with a combination of several spreadsheets in connection with the match, vlookup, and if functions but it takes many many lines of formulas to return one figure - which is not very efficiente. It would take at least an entire day of work to input all the formulas

[DonkeyOte]

It's a pretty complex calc in truth... this is an adaptation of something I saw elsewhere (itself an adaptation of one of Bob Phillips' formulae) so I won't confess to it being mine nor Bob's (who may disown it!) - nor would I say it's the be-all-and-end-all... it's a Volatile Array and as such will not be particularly efficient...

Please Login or Register  to view this content.

Where A1 is start date and A2 no. of days to be added and Holidays the named range containing the list of public holidays falling Mon-Sat.

If VBA is viable you might want to consider implementing a UDF which would be relatively straightforward I think.

[yak10]

I need to do some research on these functions before attempting anything. I have tried to understand the ISNA function before (on a limited basis) but I'm really lost when it comes to that one. The other functions are a little vague to me also. I will attempt a solution. Thanks for your help......

[daddylonglegs]

Do you need to exclude holidays?

If not then this will exclude just Sundays

=A1-WEEKDAY(A1,3)+INT(7/6*(B1+MIN(5,WEEKDAY(A1,3))))

where A1 is start date and B1 is a (positive) number of days to add

[DonkeyOte]

dll, thanks... and....

[yak10]

I DO need to exclude holidays also......Thanks.......

[daddylonglegs]

OK, then you need something similar to the formula posted by DonkeyOte. That formula is pretty comprehensive. It works even if you want to go backwards, i.e. if A2 is a negative number. If that scenario never arises for you then you could simplify a little, effectively removing all the SIGN and ABS functions, e.g.

=SMALL(IF(WEEKDAY(A1+ROW(INDIRECT("1:"&A2*10)))<>1,IF(ISNA(MATCH(A1+ROW(INDIRECT("1:"&A2*10)), holidays,0)),ROW(INDIRECT("1:"&A2*10)))),A2)+A1

confirmed with CTRL+SHIFT+ENTER

[yak10]

OK, I finally have a chance to work with this formula.......I'm trying to understand how this is working before I implement. I'm researching the INDIRECT function. What exactly is INDIRECT("1:"&a2*10) doing? What function is the 1, :, and & taking on? Why are we multiplying be 10? Thanks.

[daddylonglegs]

Probably best to explain first how the formula works in general.

The idea is to generate an array of future dates that satisfy our criteria, i.e. they aren't Sundays and they aren't holidays. Once you have that array then we can use SMALL function to extract the nth date, e.g.

=SMALL(Date_array,A2)

where A2 is the number of days you want to add

The ROW(INDIRECT part is a common way to generate an array of consecutive integers starting at 1, e.g. this formula

=ROW(INDIRECT("1:10"))

generates this array {1;2;3;4;5;6;7;8;9;10}

In this case we want to generate an array of dates that will definitely include the date we wish to return, so we base the "intvector" on the value in A2, e.g. if A2= 3 then

=ROW(INDIRECT("1:"&A2*10))

is the equivalent of

ROW(INDIRECT("1:30"))

i.e. it generates this array

{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30}

In the first part of the formula this array is added to the date A1 so that generates the array of dates, starting with A1+1 and going through to A1+30

The formula then successively tests these dates, first to see whether they are Sundays (WEEKDAY = 1) and then whether they match any dates in the holiday range. The final output is the list of dates which are neither Sundays nor holidays, and as I explained above, SMALL is then used to pick the correct date.

The *10 used is a little bit arbitrary, you could probably use 5 or 8. It would only matter when A2 is a low number. e.g. if A2 is 1 and we only generate 5 dates using *5 then there could be a problem if none of those 5 dates are working days....of course that can only happen here if your holiday list has 4 successive holidays (with the 5th a Sunday), in which case the formula will fail. Using *10 should ensure that the formula works in all but the most extreme circumstances......

[yak10]

OK - I follow you this far.........

WEEKDAY(A1+ROW(INDIRECT("1:"&A2*10)))<>1

This is returning an array of values that are excluding Sundays.....So if a1 is 8/25/2009 and a2 is 3, then 8/30, 9/06, etc are being excluded from 30 days after 8/25/2009 (or 1 + 8/25/2009).

Now......MATCH(A1+ROW(INDIRECT("1:"&A2*10)), holidays,0

What is "holidays"? I understand that it is testing for holidays in the same array of dates, which you are trying to assign the number 0. If you do a simple formula for 9/07/2009 (match(a1,holidays,0) (where a1 is equal to 9/07/2009) then it shows #NAME?. This is labor day - which its not recognizing.

I think I just about have this......Thanks again

[daddylonglegs]

You have to have a named range holidays where you list your holiday dates....or you can just replace holidays with a specific range - which must be a single column or row - e.g. if you list your holidays in H1:H10 then use that range in place of holidays, i.e.

ISNA(MATCH(A1+ROW(INDIRECT("1:"&A2*10)),H1:H10,0))

[yak10]

I get a #NUM!............

[daddylonglegs]

That could be either because

1) you still have holidays in the formula and you haven't defined a range of that name (you can use Insert > Name > Define)

....or....

2) you haven't confirmed the formula with CTRL+SHIFT+ENTER. If done correctly you get curly braces like { and } around the formula in the formula bar

[yak10]

OK, I haven't had a chance to come back to this problem until today. I clicked the yellow exclamation point and selected show calculation steps - control shift enter doesn't do anything......The formula I inputted is =SMALL(IF(WEEKDAY(B33+ROW(INDIRECT("1:"&C33*10)))<>1,IF(ISNA(MATCH(B33+ROW(INDIRECT("1:"&C33*10)),(H54:H63),0)),ROW(INDIRECT("1:"&C33*10)))),C33)+B33

It lists the following small(if(true,if(ISNA(match(40057,$h$54:$h$60,0)),row(indirect("1:"&c33*10)))),c33)+b33. In this formula match(40057,h54:h60,0)) is underlined.

I then hit evaluate and it underlines isna(#n/a).

I'm lost at this point............

[daddylonglegs]

..... control shift enter doesn't do anything......

The formula won't work without it. If you have that formula in a cell then select that cell, press F2 key then hold down CTRL and SHIFT keys and press ENTER.

If done correctly then curly braces like { and } will appear around the formula in the formula bar and the formula should work as intended....

[yak10]

OK......that did the trick........Thanks for all the help, it is much appreciated - it will help tremendously. Can I ask you one last question though? Why does it not work without using f2 control shift enter? What function does it serve? Thanks again.

[daddylonglegs]

Functions like WEEKDAY are normally only used with single dates, e.g.

=WEEKDAY(A1)

but if you apply WEEKDAY to a range or array as you do in that formula, e.g.

=WEEKDAY(A1:A10)

then you need to use CTRL+SHIFT+ENTER [F2 issn't essential to this process, you can just click in the formula bar].

CTRL+SHIFT+ENTER effectively tells excel that it has to loop through the range of cells, finding the weekday of each, see here for more

[DonkeyOte]

In addition (if you are interested) Colin Legg has also put a detailed tutorial up on Array Formulae - link in my sig.

[yak10]

I've had a chance to read over one of the links - excellent information - Thank you.

There is one last thing bothering about the formula that was provided. I realize its an accurate formula and does work for my needs. However, I would like to "improve" my understanding. As I look at this formula I see two "if" functions. The first has a logical test that creates an array that is excluding Sundays. The "value if true" is the beginning of the second "if" function that is returning an array of dates that is excluding holidays. The second "if" function contains its "value if true" with the following: ROW(INDIRECT("1:"&C33*10)))). After the fourth ending parathesis and then a comma, it jumps over to finish the "small" function. So in other words, my question is - How can this formula work without finishing the "if" function? There is no "value if false"

[daddylonglegs]

You can try out a sample IF in B1

=IF(A1="x",1)

There's no "value if false" specified, which means that if A1 isn't "x" the formula just returns FALSE

In the formula above, therefore, the combined IF functions return an array that consists of numbers and FALSE values. SMALL function ignores FALSE.

You can see the array contents like this

Make sure B33 is a small value
Select cell with the long formula
Put the cursor in the formula bar and select this part of the formula only

IF(WEEKDAY(B33+ROW(INDIRECT("1:"&C33*10)))<>1,IF(ISNA(MATCH(B33+ROW(INDIRECT("1:"&C33*10)),(H54:H63),0)),ROW(INDIRECT("1:"&C33*10))))

so that it's highlighted (make sure you include all relevant parentheses)

and press F9 so that you see the values generated

[You can also use Tools > Formula auditing > Evaluate Formula and continually hit "Evaluate" to go through the process that the formula follows....]

Some people prefer to put in a "" as the FALSE value (SMALL will ignore text too)

[yak10]

I'm really starting to get the hang of this now. I now have a new development. Assuming I use the same formula and I want to find the greater of two dates, this is what I have so far but Excel does not like it.

=small((small(if(weekday(a1+row(indirect("1:"&b1*10)))<>1, if(isna(match(a1+row(indirect("1:"&b1*10)),(holidays,0)),ROW(INDIRECT("1:"&b1*10)))),b1)+a1)small(if(weekday(c1+row(indirect("1:"&d1*10)))<>1, if(isna(match(c1+row(indirect("1:"&d1*10)),(holidays,0)),ROW(INDIRECT("1:"&d1*10)))),d1)+c1),2)

I have also tried to use the large function, with no luck. I was considering using more if functions, but haven't gotten that far yet. Am I making this too complicated? I could separate this into two cells and then use the small or large function, which I have done and have gotten it to work. But, the more concise the better if its possible. Thanks again.

P.S. it puts a blue smile where a colon should be.........

[daddylonglegs]

I think I'd go with 2 separate cells but if you want it all in one then you'd need to use MAX, i.e.

=MAX(SMALL(IF(WEEKDAY(C1+ROW(INDIRECT("1:"&D1*10)))<>1, IF(ISNA(MATCH(C1+ROW(INDIRECT("1:"&D1*10)),holidays,0)),ROW(INDIRECT("1:"&D1*10)))),D1)+C1,SMALL(IF(WEEKDAY(A1+ROW(INDIRECT("1:"&B1*10)))<>1, IF(ISNA(MATCH(A1+ROW(INDIRECT("1:"&B1*10)),holidays,0)),ROW(INDIRECT("1:"&B1*10)))),B1)+A1)

confirmed with CTRL+SHIFT+ENTER