Need number of days in current month without weekends

chrise1111

New Member
Joined
Mar 4, 2022
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Hello, I need to have the formula to auto-generate the number of days in the current month each month, but without (excluding) weekends. Thanks in advance for your help!

Details:
  • I need it to auto calculate this for the current month each month. Then when the next month starts, auto-calculate it for that month and so on.
    • For example - the current month right now is March 2022. It has 31 days total and 8 weekend days or 23 days not including the weekends. Then for April, it would auto-calculate 21 days, and so forth on into the future.
  • I'm not an Excel expert by any means if you can just reply with the formula. I know I have formulas below but I didn't write or figure them out and only have a small understanding of NETWORKDAYS, EOMONTH, etc.
  • It doesn't even need to have holidays, just excluding the weekends each month and resetting when a new month starts like the formulas below. And not the days left in the month, (I already have those formulas), just days in the month without weekends.
PS:
  • I already have a formula that auto-calculates the number of days total in the current month and resets each month: =DAY(EOMONTH(TODAY()+1,0)) Just need to calculate this day count without weekend days.
    • And the one for days left in current month: =EOMONTH(TODAY(),0)-TODAY()
    • And the one for days left in a month with no weekends: =NETWORKDAYS(today(),EOMONTH(TODAY(),0))
      And days elapsed in a current month =DAY(TODAY())
Thanks again!
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
7,245
Office Version
  1. 2016
Platform
  1. Windows
Hi,

Is this what you mean:

Book3.xlsx
A
523
Sheet1035
Cell Formulas
RangeFormula
A5A5=NETWORKDAYS(EOMONTH(TODAY(),-1)+1,EOMONTH(TODAY(),0))
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,498
Welcome to the MrExcel forum!

Try:

Excel Formula:
=NETWORKDAYS(TODAY()-DAY(TODAY())+1,EOMONTH(TODAY(),0))
 

chrise1111

New Member
Joined
Mar 4, 2022
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
That's it! Thanks, jtakw and Eric W for the fast response and the warm welcome. You guys are awesome a saved me a bunch of time. Thanks again!
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
7,245
Office Version
  1. 2016
Platform
  1. Windows
You're welcome, thanks for the feedback.
 

chrise1111

New Member
Joined
Mar 4, 2022
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Hi guys, how about a formula that calculates the elapsed days in the current month, excluding weekends? And that resets each new month. For example for the current month and current date it is 3.7.22, so 7 total days in the month minus the 2 weekends days that would calculate to 5 days. Thanks in advance!
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,498
It should just be:

Excel Formula:
=NETWORKDAYS(TODAY()-DAY(TODAY())+1,TODAY())
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
7,245
Office Version
  1. 2016
Platform
  1. Windows
Also:

Excel Formula:
=NETWORKDAYS(EOMONTH(TODAY(),-1)+1,TODAY())
 

Forum statistics

Threads
1,176,640
Messages
5,904,198
Members
435,076
Latest member
71672618

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top