Tips & Tricks 81 – First Working Day of the Month if a Date is Given

If A1 contains a date, then formula for First Working Day of the month would be

=WORKDAY(EOMONTH(A1,-1),1)

The above formula assumes that your weekends are Saturday and Sunday.

But, if your weekends are different (e.g. in gulf countries), you can use following formula –

=WORKDAY.INTL(EOMONTH(A1,-1),1,"0000110")

Where 0000110 is a 7 character string, 1 represents a weekend and 0 is a working day. First digit is Monday and last digit is Sunday. The above example is for Gulf countries where Friday and Saturday are weekends.

You also have option to give a range which has holidays. In that case, your formula would become

=WORKDAY(EOMONTH(A1,-1),1,D1:D10)

=WORKDAY.INTL(EOMONTH(A1,-1),1,"0000110",D1:D10)

Where range D1:D10 contains the list of holidays.