tips-and-tricks

Calculate the number of working days between two dates

October 12, 2012 Liz Todd

Whether it is meeting monthly, quarterly or annual targets, organising rosters, or counting down until Christmas, it is often useful to know the number of working days between two dates. The good news that you don’t need to sit with a calendar and count each day! Instead, you can use the Excel function NETWORKDAYS.

NETWORKDAYS calculates the number of days excluding weekend days between a start date and end date. As an option, you can exclude any holidays in the period. The example we use in this article excludes the New Zealand public holidays in the number of working days.

In an Excel workbook, enter the dates of public holidays in a list. If you’re not sure of these, a Google search will help you find them. It’s a good idea to name this list of dates to make it easier to reference the holidays in the NETWORKDAYS function arguments. Here’s how: select the list of holiday dates, click the Name box at the top left of the Formula bar, type “Holidays” and tap Enter.

In a worksheet cell, type the start date for the period. In another cell, type the end date for the period. In a third cell, type “=NETWORKDAYS(”, click the start date cell, type “,”, click the end date cell, type “,”, type “Holidays)” and then tap Enter. And there you have the total number of working days excluding public holidays between the two dates!

Note: do not type the “” shown in these instructions.


Tags



Older Post Newer Post



Leave a comment

Please note, comments must be approved before they are published