Calculate Number of Working Days to the End of the Current Month

Welcome to Blue Pecan: we offer tailored In house/ onsite Excel training at your business premises. We are based in Sussex, UK and cover the home counties and London including Sussex, Dorset, Hampshire, Kent, Essex, Berkshire & Buckinghamshire. Call 0800 612 4105 to enquire.

We want Excel to tell on any given day, how many working days left until the end of the current month. To achieve this we will use three functions (see the video below for a demonstration).

The TODAY function. This function automatically returns the current date. It’s a function without arguments so has empty function brackets like this =TODAY() The today function automatically updates itself when the workbook is refreshed or opened.

The EOMONTH function (End of Month). This function returns the last date in a specified month. It has two arguments start date and months. We would specify our start date with the TODAY function and months as zero to return the last date of the current month. So to calculate the last day of the current month the formula would be =EOMONTH(TODAY(),0).

To calculate the number of working days (Monday through to Friday) between today and the end of the current month we need to employ the NETWORKDAYS function. This function has two mandatory arguments and one optional. The two mandatory are start date and end date. The optional argument is holidays. So my formula might look like this

=NETWORKDAYS(TODAY(),EOMONTH(TODAY(),0),E1:E10) where E1:E10 contains a list of dates that are non working, for example bank holidays.