In this week’s video I show you how to calculate the first and last working day of a month. This can be particularly useful for project planning, payroll calculations, and various administrative tasks.

Here’s a list of invoices. As you can see, each invoice has a date. The Head of Accounts needs to know which accounting period each invoice relates to. The accounting periods cover the first working day of a month to the last working day of a month. Working days are defined as Monday to Friday.

We also have to factor in working days that are non-working days (known as holidays). In this example the first working day of January 2024 is Monday 1st January however the company shuts down over the Christmas/New Year period and didn’t re-open until 8th January. So in this instance 8th January 2024 is the first working day of January 2024.

The formulas that I used in the video:

=EOMONTH([@[Order Date]],-1)+1

=EOMONTH([@[Order Date]],0)

=WORKDAY(EOMONTH([@[Order Date]],-1),1,$K$2:$K$10)

=WORKDAY(EOMONTH([@[Order Date]],0)+1,-1,$K$2:$K$10)

You can download a copy of the file I used in the video by clicking through to YouTube. The link for the file is in the YouTube description.