excel - Check day is the last working day of the month

Question

I need to write the formula to check the last working day of the month in excel.

Data look like this:

| Date | | 2018/10/22 | | 2018/10/31 |

The output should be:

| Date | Check | | 2018/10/22 | o | | 2018/10/31 | x |

Working day: from Mon to Friday, No holidays.

Answer 1

The 'last working day of the month' is a bit ambiguous given different holiday structures and the fact that not everyone works Monday to Friday. However, if a list of local holidays is provided in Z2:Z13, the WORKDAY.INTL function should be able to return the last workday of any month with a variety of work and holiday schedules.

=WORKDAY.INTL(EOMONTH(A2, 0)+1, -1, "0000011", Z$2:Z$13)

For the purpose of demonstration, I've added the weekday to the date format with a custom number format of ddd, yyyy/mm/dd_).

In the following sample image, note that the fictional holiday of Wed, 2018/01/31 pushed the 'last workday of the month' to Tue, 2018/01/30 and March's last day is Fri, 2018/03/30 since Saturday is considered a non-workday.