If no working day: Next working day

@ > Home > Contents > If not a workday: Next (or previous) workday Excel 4+

Task:

Step 1: A given date has to be on a workday (f.e. for office work time). Weekends and holidays have to be left out, choosing the next workday instead.

Step 2: If the date after Step 1 results in a next months date, get the last previous workday instead (international Target Day Method for mortgage plans).

Requirements:

- Name a one-column range containing sorted holidays "ftg".
- A1: the date on which Step 1 or 2 have to be performed.
- The use of the Analysis-Add-In (WORKDAY()) is not permitted.

Preliminaries:

A weekend has 2 days. There are not more than 3 subsequent non-weekend holidays. In Europe, added to 2 Christmas holidays often comes Dec 24th (at banks and some other companies). So we deal with 5 days maximum which are not work days. 5 therefore is the maximum number which has to be added to reach the first workday forwards (step 1); of course this is also valid backwards (step 2).

Step 1:

Holidays (before *) and weekends (after *) must be validated together for each following day until both are FALSE. The reason is: Both can be in any order whatever. The following nested condition meets this requirement:

=IF((A1+0>VLOOKUP(A1+0,ftg,1))*(MOD(A1+0,7)>1),0,
IF((A1+1>VLOOKUP(A1+1,ftg,1))*(MOD(A1+1,7)>1),1,
IF((A1+2>VLOOKUP(A1+2,ftg,1))*(MOD(A1+2,7)>1),2,
IF((A1+3>VLOOKUP(A1+3,ftg,1))*(MOD(A1+3,7)>1),3,
IF((A1+4>VLOOKUP(A1+4,ftg,1))*(MOD(A1+4,7)>1),4,5)))))+A1

Looks like a badly needed array formula here! But: The above is fast (about 70% days are workdays, meeting the first THEN) and even suits Pocket-Excel, so I have a reason to mention it.

You get your array formula here:

{=MATCH(1,(A1+ROW($1:$6)-1>INDEX(ftg,MATCH(A1
+ROW($1:$6)-1,ftg)))*(MOD(A1+ROW($1:$6)-1,7)>1),0)+A1-1}

Watch. There is a slight problem with it. Entered and {}-ed (Ctrl-Shift-Enter) in one single cell, it does not work! If you do so in two neighboring cells, {}-ing as a range array, it works. We (or you?) are still trying to find out why.

Step 2:

Otherwise in analogy to step 1, it now has to be checked whether the formula remains in the same month. If not, the formula checks also backwards for workdays and chooses the previous last one. As a frequent nuisance in spreadsheet formulas it has to perform duplicate calculation (highlighted in the formula) in the IF clause:

=A1+IF(MONTH(
IF((A1+0>VLOOKUP(A1+0,ftg,1))*(MOD(A1+0,7)>1),0,
IF((A1+1>VLOOKUP(A1+1,ftg,1))*(MOD(A1+1,7)>1),1,
IF((A1+2>VLOOKUP(A1+2,ftg,1))*(MOD(A1+2,7)>1),2,
IF((A1+3>VLOOKUP(A1+3,ftg,1))*(MOD(A1+3,7)>1),3,
5-(A1+4>VLOOKUP(A1+4,ftg,1))*(MOD(A1+4,7)>1)))))
+A1)=MONTH(A1),
IF((A1+0>VLOOKUP(A1+0,ftg,1))*(MOD(A1+0,7)>1),0,
IF((A1+1>VLOOKUP(A1+1,ftg,1))*(MOD(A1+1,7)>1),1,
IF((A1+2>VLOOKUP(A1+2,ftg,1))*(MOD(A1+2,7)>1),2,
IF((A1+3>VLOOKUP(A1+3,ftg,1))*(MOD(A1+3,7)>1),3,
5-(A1+4>VLOOKUP(A1+4,ftg,1))*(MOD(A1+4,7)>1)))))
,
-IF((A1-1>VLOOKUP(A1-1,ftg,1))*(MOD(A1-1,7)>1),1,
IF((A1-2>VLOOKUP(A1-2,ftg,1))*(MOD(A1-2,7)>1),2,
IF((A1-3>VLOOKUP(A1-3,ftg,1))*(MOD(A1-3,7)>1),3,
IF((A1-4>VLOOKUP(A1-4,ftg,1))*(MOD(A1-4,7)>1),4,
5)))))

You see a small detail having changed? The deepest IF clause has changed to a boolean subtraction "5-(A1...". Why? The nesting had reached the before-XL-2007 maximum of 8 nested functions (6x IF, 1x MONTH, 1x VLOOKUP/ MOD). But we need one of them for SETV(). Admittedly, reducing nesting complexity could also be reached by naming parts of the formula, f.e., the 3 inner most IF's. But by doing as above, we can keep it in one formula.

The array formula below again has to be typed (Ctrl-Shift-Enter) by marking two cells together:

{=A1+IF(MONTH(
MATCH(1,(A1+ROW($1:$6)-1>INDEX(ftg,
MATCH(A1+ROW($1:$6)-1,ftg)))*(MOD(A1+ROW($1:$6)-1,7)>1),0)
+A1-1)=MONTH(A1),
MATCH(1,(A1+ROW($1:$6)-1>INDEX(ftg,
MATCH(A1+ ROW($1:$6)-1,ftg)))*(MOD(A1+ROW($1:$6)-1,7)>1),0)-1,
-MATCH(1,(A1-ROW($1:$5)>INDEX(ftg,
MATCH(A1-ROW($1:$5),ftg)))*(MOD(A1-ROW($1:$5),7)>1),0))}

It uses only 6 function nesting levels, giving SETV() space to be used instantly:

{=A1+IF(MONTH(setv(
MATCH(1,(dat>INDEX(ftg,MATCH(dat,ftg)))*(MOD(dat,7)>1),0))
+A1-1)=MONTH(A1),getv()-1,-
MATCH(1,(das>INDEX(ftg,MATCH(das,ftg)))*(MOD(das,7)>1),0))}

"dat" and "das" name the running variable expressions, shortening them - which is not a must, only a convenience.

By using (1) the array, (2) SETV() and (3) named formula parts, the step-2 formula is more than 4 times shorter. And probably faster.

Remarks:

http://excelformeln.de/formeln.html?welcher=13 (replacing WORKDAY() of the Analysis-Add-In)
http://excelformeln.de/formeln.html?welcher=428 (target date for financial purposes)

(both in German) gave the ideas for this page.

Here we have an example for different Excel philosophies: VBA, macros or WORKDAY() would be more efficient, but are restricting in other aspects.