When working with dates, you'll sometimes find that you need to calculate the number of working (or business days) that have passed in the current month.
Contents:
1.1. Calculate Working Days Passed in Current Month
Quick Formulas
To calculate the number of working days passed in the current month from today:
= NETWORKDAYS((EOMONTH(TODAY(),-1)+1),TODAY()-1)To calculate the number of working days passed in the current month from today excluding holidays:
= NETWORKDAYS((EOMONTH(TODAY(),-1)+1),TODAY()-1, holiday_dates)holiday_dates - List/range of dates that should not count as a working day
Explanation
How to Calculate Working Days Passed in Current Month
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. Here date would represent todays date, returned by =TODAY().
=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:
= TODAY()-1Since we want to count all fully-passed days in the month, we can just take todays date, and subtract one, leaving us with yesterday's date.
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(TODAY(),-1)+1),TODAY()-1)How to Exclude Holidays from Workdays Passed in Current Month
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(TODAY(),-1)+1),TODAY()-1, 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.
Now, since 9/2 is a holiday, if we use the same formula, we get 18 business days passed, instead of the original 19.
