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.