A quick formula to calculate the number of weekend or non-working days remaining in the current month.
Contents:
Formula
Calculate weekend days left in current month:
= DAYS(EOMONTH(TODAY(),0),TODAY()) - NETWORKDAYS(TODAY()+1,EOMONTH(TODAY()+1,0))To calculate weekdays remaining from a specific date, you can replace the TODAY() with a date or cell reference like so (replacing date with your actual date):
=DAYS(EOMONTH(date,0),date) - NETWORKDAYS(date+1,EOMONTH(date+1,0))Explanation
How to Return the Number of Non-Working Days Left in Current Month
This formula calculates the number of weekend days between today's date and the end of the current month.
Formula Breakdown
This formula can be broken down into two main segments.
The blue part calculates the number of days remaining in the current month, and the orange part calculates the number of weekdays remaining. By subtracting the number of weekdays from the number of remaining days, we're left with weekend days remaining.
DAYS(EOMONTH(TODAY(),0),TODAY()):
This part of the formula uses the DAYS function, which calculates the number of days between two dates.
The first argument is end_date. To calculate that, we're using EOMONTH(TODAY(),0) to return the end date of the current month. For the start_date argument, we're just using the TODAY() function.
NETWORKDAYS(TODAY()+1,EOMONTH(TODAY()+1,0))
This second part of the formula is covered in more detail in this tutorial (link here).
But, in general, this formula calculates the number of whole workdays between today's date (including today) and the end of the current month, excluding weekends (Saturday and Sunday).
Then we subtract the two. So if the date is August 21st, there are 10 days left in the current month, and 8 of those are weekdays, so there are 2 weekend days remaining.
= 10 days - 8 weekdays = 2 weekend days
How to Return the Number of Weekends Left in Current Month
We can use a similar formula to calculate the number of weekends remaining in a month instead of weekend days. The only change needed is to divide our previous function by 2.
So, our formula would be changed to:
= (DAYS(EOMONTH(A2,0),A2) - NETWORKDAYS(A2+1,EOMONTH(A2+1,0)))/2This formula will take each date in our "Date" column and calculates the number of remaining weekends in that specific month.