Hello and welcome again to #formulafriday. Time for some more formula fun on a Friday. Today we will be looking at using the NETWORKDAYS Function to work out how many days are left in a month from the current or today’s date.
This is a handy dashboard formula, for giving real time time to end of month every time the report is opened, targets maybe set against it, so it is a handy function to know.
First let’s take a look t the NETWORKDAYS Function.
What Does NETWORKDAYS Do?
It returns the number of full working days, excluding weekends and specified holidays between a start date and and end date.
What Is The Synatax For NETWORKDAYS?
The syntax is as follows
=NETWORKDAYS(start_date,end_date,[holidays])
Where
start_date – this is a required field and is the argument that represents the start date
end_date – this is a required field and is the argument that represents the end date
[holidays] – this argument is options (indicated by the [ brackets]) and is used to represent any days that you want treated as non working ( i.e holidays in the period in the analysis – for example a national holiday. This can be represented by a single cell or a range of cells that contains the specified holiday dates.
- Let’s go ahead and build our formula, to find out how many full working days are left in a month’ We start with typing
=NETWORKDAYS(
- We now can combine the TODAY() function as the start_date, so the number of days is dynamic and will update every time the report is opened.
=NETWORKDAYS(TODAY(),
- The end_date is the end of the month(you can specify any date for example the end of a project), in this example we are calculating the number of working days until the end of March 2017. The last day of the month is held in cell F4
=NETWORKDAYS(TODAY(),31/03/2017
- We have only one holiday to specify, that is on the Monday 27/03/2017. This is held in cell D4
- If we have more than one day of holidays to specify then we can select the range of cells that contain the holiday dates. I have extended the holiday dates to include the Monday and Tuesday 27 and 28th March 2017.
- The number of working days has reduced by 1 to 8 when we added in another holiday. As you can see by incorporating the TODAY() function we have made this formula more dynamic as will update to to the current days date when the report is opened by the users.
If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips. If you want to see all of the blog posts in the Macro Mondays Series you can do so by clicking on the link below.
