When working with dates, you'll sometimes find that you need to calculate the number of working (or business days) in a month, from a specific date.
Contents:
Quick Formulas
To calculate the number of working days in a month:
= NETWORKDAYS(EOMONTH(date,-1)+1,EOMONTH(date,0))To calculate the number of working days in a month excluding holidays:
= NETWORKDAYS(EOMONTH(date,-1)+1,EOMONTH(date,0), holiday_dates)date - The reference date for which the number of working days in the month will be calculated
holiday_dates - List/range of dates that should not count as a working day
Explanation
How to Calculate Working Days in a Month from a Date
This formula revolves around the use of the NETWORKDAYS function.
The NETWORKDAYS function will return the number of workdays, or business days, between two dates.
However, since we only want one date as an input, we need to use a slightly more complicated formula to calculate the start and end dates for each month.
Here is the full formula using NETWORK DAYS. We can break it down into a couple different pieces. The red portion represents the "start_date" argument and the blue portion represents the "end_date" argument.
Let's look at the start_date formula first.
=EOMONTH(date,-1)+1
This formula will take a date, let's say 1/14/2024, and will return the very first day of the month in which the date is found.
The EOMONTH function takes in a date and returns the last or end date in that month. So, for 1/14/2024, the last day is 1/31/2024.
By adding a -1 in the second argument, it takes the last date of the previous month, giving And by adding a +1 to that date, we end up with the beginning date of the current month.
So that gives us the start_date of 1/1/2024. Next we need to figure out the end_date.
For that we use this formula:
= EOMONTH(date,0)As explained in the previous step, this formula will always return the end date of the current month that the date is in.
Put it all together and this is our full formula that will take a date, and return the number of business days in that month.
= NETWORKDAYS(EOMONTH(date,-1)+1,EOMONTH(date,0))
How to Exclude Holidays from Workdays in a Month Calculations
The previous formula does not take any holiday into account when calculating the number of business days, but we can make one change in the formula to account for this.
In the U.S. for example, there are two federal holidays in January. New Years Day, and also MLK Day. If we use the previous formula, we calculate the month as having 23 working days, but in actuality, there are only 21.
We need to change our formula a bit.
= NETWORKDAYS(EOMONTH(date,-1)+1,EOMONTH(date,0), holiday_dates)
The NETWORKDAYS function has an optional [holidays] argument, where, when given a list of dates, will exclude those dates from the calculation.
Let's say your company gets these holidays off, and we want to make sure these holidays aren't included in the working day calculations.
In the place of the holiday_dates argument, we can select the entire date column.
If the dates are held in the range F4:F11, then that whole range would be your holiday_dates input.
Make sure to use "$" signs where appropriate to lock your date selection down.
F4:11 would become $F$4:$F$11 to allow the formula to be copied.
Here is how the finished tables would look. We have a column of dates and number of business days in that month on the left, that is referencing our holiday table on the right.
If we look into the formula, you can better see how the formula is operating.
