formula for six days week explanation..:)

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

Can any one please explain the formula in detail as i could not understand SMALL & INDIRECT function. Mr. Daddylongleg gave me few days back. this formula works fine and main purpose of the formula is to calculate a future date something like WORKDAY function but works better...

The formula is calculating a future date based on a 6 day work week (Sundays non-working day) with public holidays to be accounted for also.

Dates in Excel are Integers - ie 4th March 2010 is 40241, the 5th would be 40242 etc (1900 date system assumed).

The formula utilises this fact in conjunction with the fact that Row numbers can be used to represent dates, eg ROW(40241) would be 40241 which would be equiv. to 4th March 2010

The formula uses INDIRECT to create a range of ROWS which can then be evaluated as Dates - it does this using INDIRECT:

INDIRECT("1:"&A3*10)

where A3 is the number of WORKDAYS to be added to the start date... the * 10 is to ensure that enough days are processed such that the required date will be found (ie public holidays etc...)

Assume then A3 is 10 - the INDIRECT range becomes:

INDIRECT("1:100")

ROW is then applied to the above to generate an Array of integers 1-100:

ROW(INDIRECT("1:100")) -> {1;2;3;...;100}

To these numbers we add the Start Date, as set in A2, let's assume that is 4th March 2010 - or 40241 in Integer terms:

A2+{1;2;3;...;100} -> {40242;40243;40244;...;40341}

So we know have an Array of 100 integers - each of which represent a date.

Against this array a WEEKDAY test is conducted to determine whether the date is a a Sunday or not

WEEKDAY({40242;40243;40244;...;40341})<>1

Where the result of this test is TRUE we know the date value must be Mon-Sat... at which point we know that the date value is "potentially" legitimate working day for our purposes.

I say "potentially" because we also need to validate as to whether or not the date falls on a public holiday - if it does we need to discount it as we would a Sunday and so we move on to the next test:

IF(ISNA(MATCH(A2+ROW(INDIRECT("1:"&A3*10)),H$1:H$10,0)),...

So this does the same thing as before re: A2+ROW(INDIRECT(...)) only now the resulting values are each checked to against the range H1:H10 ... only if the date values are not listed are they deemed valid (ie ISNA returns TRUE)

If at this point the date values being processed are both

a) Mon-Sat
b) not Public Holidays

then we add the "days from start date" to the final array of values - this is done by virtue of:

ROW(INDIRECT("1:"&A3*10))

wherever either of a) OR b) have not held True (ie Sun or public holiday) the final array is populated with a Boolean FALSE.

If as before (for sake of continuity and example) we assume:

A2 is 4th March 2010
A3 is 10
H1 contains our only public holiday and that is 8th March 2010

then our final array of values to be used with the SMALL will look something like:

{1;2;FALSE;FALSE;5;6;7;8;9;FALSE;11;12;13;...;100}

You will note 3,4 are not listed - this is because day 3 from A2 is 7th March 2010 and that's a Sunday, 4 is 8th March 2010 and though a Monday it is a Public Holiday - both are therefore discounted given they are not legitimate dates for our result.

The SMALL is applied to the above array and the appropriate value is retrieved based on the WORKDAY requirement - ie earlier we stated that A3 would be 10 - the 10th Smallest numerical value in the above array would be 13.

We take the above result (13) and add that to our start date - eg 40241 + 13 -> 40254 - which we can format as a Date to read 17-Mar-2010.


If the above does not make sense immediately do not fret - it's not trivial by any stretch of the imagination.