Home>Blogs>Excel Tips and Tricks>Amazing Excel Hack to Count Working Days in a Month
Number of Working days in a Month
Excel Tips and Tricks

Amazing Excel Hack to Count Working Days in a Month

Struggling to keep tabs on working days each month? Tired of the tedious manual counting? Well, here’s some exciting news! We’ve got a super simple Excel trick that will make your life a whole lot easier. Let’s dive right in and transform the way you handle dates in Excel!

Getting Started

Before we jump into the magical formula, let’s lay the groundwork. Picture your Excel sheet. In cell B2, you’ve punched in the year. Then, from A5 to A16, you’ve got the months – January to December, all neatly lined up. Now, the challenge is to figure out the working days for each month, listed in the range B5 to B16.

Networkdays formula
Networkdays formula

The Formula:

Get ready for the formula that’s going to change everything: =NETWORKDAYS(DATEVALUE(“1-“&A5&”-“&$B$2),EOMONTH(DATEVALUE(“1-“&A5&”-“&$B$2),0)). Let’s break it down, step by step:

  • DATEVALUE Function: First off, DATEVALUE(“1-“&A5&”-“&$B$2) transforms a text-based date into an actual date Excel can work with. It’s like turning “1-January-2022” into a date format Excel understands.
  • EOMONTH Function: Next up, EOMONTH(DATEVALUE(“1-“&A5&”-“&$B$2),0) finds the last day of the month. It takes the date we just converted and tells us the month’s end date.
  • NETWORKDAYS Function: Finally, the star of the show, NETWORKDAYS, calculates the working days between two given dates. In our formula, it’s working out the days from the start to the end of the month.

Implementing the Trick

Simply pop the formula into B5, then drag it down all the way to B16. Just like that, the working days for each month are automatically filled in. Easy, right?

Why You’ll Love This

This Excel shortcut not only saves you heaps of time but also boosts your accuracy in tracking days. Say goodbye to the headache of manual counting!

You’ve Got This!

Kudos to you for mastering this useful Excel formula! Now, you can effortlessly calculate working days for any month in the blink of an eye.

Excel has tons of these cool little hacks, and with tips like this, you’re well on your way to becoming an Excel superstar.

Stay tuned for more awesome Excel tips and tricks! Keep rocking that spreadsheet magic!

Visit our YouTube channel to learn step-by-step video tutorials


Watch the step-by-step video tutorial:

Click here to download the practice file

Meet PK, the founder of PK-AnExcelExpert.com! With over 15 years of experience in Data Visualization, Excel Automation, and dashboard creation. PK is a Microsoft Certified Professional who has a passion for all things in Excel. PK loves to explore new and innovative ways to use Excel and is always eager to share his knowledge with others. With an eye for detail and a commitment to excellence, PK has become a go-to expert in the world of Excel. Whether you're looking to create stunning visualizations or streamline your workflow with automation, PK has the skills and expertise to help you succeed. Join the many satisfied clients who have benefited from PK's services and see how he can take your Excel skills to the next level!