This is the second of a multi-part series on using Dates & Times in Excel.
Want to get the most out of this lesson? Download the sample data so that you can follow along.
Now that we have covered the basics of Excel Date & Time functions it is time to dive a little deeper into more practical day to day applications. While it is very useful to be able to accurately count the time between dates - the working world does operate on a slightly different schedule. In this case we have “working days”, “weekends”, and of course “holidays”.
Depending on our frame of reference it is important to consider these sorts of dates when we jump into using Excel to calculate things like delivery dates, or time off, etc. Luckily for us Excel has this covered. Lets jump into a few scenarios.
Figuring out workdays between 2 dates
Lets say we have ordered some material and the vendor has quoted us a delivery date. We have been asked to report number of business days for delivery. If we want to do this we would need to take into considering weekends & holidays that will occur during the shipment time frame. In order to do this we will incorporate the =NETWORKDAYS function. The syntax for this formula is:
=NETWORKDAYS(start_date, end_date, [holidays])
Where:
start_date: The starting date for calculating the working days.
end_date: The ending date for calculating the working days.
[holidays] (optional): A range or array of dates representing additional non-working days or holidays. It is not required unless you want to exclude specific dates from the calculation.
Here is a list recent orders with delivery times in business days:
What about holidays?
In the United States there are multiple holidays that happen during June and July. They are:
Juneteenth which occurs on June 19th.
Independence Day which occurs on July 4th.
In order to accommodate these dates we will create a separate data table in our Excel sheet in which we can list the holidays.
Note: In the event that we are calculating personal workdays, this list could also include both “Holiday” AND “Personal Time Off” dates.
Here is the updated syntax:
Now you can compare the two results. You will see that including the holidays has changed the number of business days between the date ordered and the arrival date.
NETWORKDAYS.INTL
=NETWORKDAYS excludes Saturday, Sunday, and any Holidays or other dates specify. This can be incredibly useful. But what if your business operates with a different set of work days? Or you live in a region where weekends are Friday & Saturday OR Sunday & Monday?
=NETWORKDAYS.INTL is the solution to this problem. The syntax is very similar to =NETWORKDAYS with one exception: it includes an optional argument that allows you to specify which days of the week to exclude in calculations. Here is the syntax:
NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
start_date: The starting date for calculating the working days.
end_date: The ending date for calculating the working days.
[weekend] (optional): A number or string representing the weekend days. By default, it is set to 1, which denotes Saturday and Sunday as weekends. You can customize the weekends by providing a string containing numbers or characters. For example, 11 represents Sunday and Monday as weekends.
[holidays] (optional): A range or array of dates representing additional non-working days or holidays. It is not required unless you want to exclude specific dates from the calculation.
For the [weekend] argument, here is a table of the options you can choose from:
Using our example we can switch up to only count Sundays as non-workdays. Here are the results:
Note: The two holidays were included in this example also.
Thanks for reading the second lesson in this multi part series. I hope you are able to start using these Excel skills immediately.
In the meantime, make sure you practice using these two date functions! This is easy to do - just download the sample data set included with this lesson and get to work. Apply what you have learned right away and it will stay with you.
I hope you enjoyed this lesson. There are a few other ways I can help you master Excel:
Do you have any burning questions about Excel that you want answered? Send your question to hello@learnexcel.live. I will address it in a future newsletter.
Do you or your organization need a little spreadsheet help? Book a 1 on 1 with me here.
Want to learn more Excel Shortcuts? I have created something special (and free) for you: The Best Damn Excel Shortcut Sheet
Til next time ✌️
-Ajay
The Excel Ninja 🥷
PS - If you like these lessons please share with a friend or two...
Don't forget the other way to specify weekends, with a string of seven digits starting with Monday, where 0 is a working day and 1 is a weekend.
NETWORKDAYS.INTL(start_date, end_date, "0000011")
means nonworking days are Saturday and Sunday, while
NETWORKDAYS.INTL(start_date, end_date, "0010001")
means nonworking days are Wednesday and Sunday.