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)