@ > 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)

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

N2: 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, N2 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.

N3: x-last working day before month's end (following later)