@ > Home > Contents > x-th and x-last working day Excel 97+

Mon-Fri are working days. We do not consider holidays here.

A2: An Excel-Date (23/09/2007), which shows month  (09/2007) or year (2007).

B1: x (x-th or x-last working day of the month)

N°1: First working day of the month (being an exception to N° 2):
=MAX(3-MOD(A2-DAY(A2)+1,7),1)+A2-DAY(A2)

N°2: x-th working day since month's beginning (probably still to abbreviate - many shifts included):
=A2-DAY(A2)+MAX(3-
MOD(A2-DAY(A2)+1,7),1)+TRUNC((\$B\$1-1+MAX(
MOD(A2-DAY(A2)+1,7)-2,))/5)*7+MOD(\$B\$1-1+MAX(
MOD(A2-DAY(A2)+1,7)-2,),5)-MAX(
MOD(A2-DAY(A2)+1,7)-2,)

Note: No array formulas, so it can be used in Pocket Excel and other spreadsheets. Despite being longer than arrays and accessing A1 for 10 times, N°2 calculates faster, because it calculates without loops depending on B1. Roughly said it uses the ratio of 140% days compared to working days.

Holidays require array formulas at least, see 5004.htm.

N°3: x-last working day before month's end (following later)