Hello Excellers and welcome back to another #Excel tip in my #FormulaFriday 2020 series. Need to get a reminder in Excel?. Today, let’s look at using the NETWORKDAYS function to get a reminder in Excel if your task deadline is approaching. This approach would be very useful for projects and reporting schedules.
Do you need to know or reminded that your deadline day is approaching fast or you have a little bit of wriggle room left and are ahead of schedule?. Well, it is one date that is really easy to track in Excel.
There are a couple of ways to do this, so let’s look at them both. This, in fact, was a recent question from a subscriber via email.
First, I will take a sample data set to work with. I have a number of tasks numbered and their due date. The task is to calculate the number of working days until the deadline date.
Just for clarity, we will assume the current date is the 30th of March 2019 when this Excel Tip is being written. So read in this context. The function we are going to use is NETWORKDAYS. When you begin to type the function there are two versions that are available.
1. The first NETWORKDAYS function counts the number of whole working days (standard business days) between two dates whilst excluding an assumed weekend of Saturday and Sunday.
2. The second NETWWORKDAYS.INTL counts the number of whole working days (standard business days) between two dates and also excludes Saturday and Sunday by default. The extra functionality this version provides is the ability to specify which days of the week you want to be seen as a weekend. For example your weekend maybe Sunday and Monday.
NETWORKDAYS SYNTAX
=NETWORKDAYS.INTL (start_date, end_date, [weekend], [holidays])
Where
start_date – The is your start date.
end_date – The end your date.weekend – [optional].(Setting for which days of the week should be considered weekends).
holidays – [optional] A list of non-work days as dates.
NETWORKDAYS.INTL calculates workdays between two dates as in the first example. Workdays exclude weekends (Saturday and Sunday by default) and can optionally exclude holidays. This function is more flexible and robust than the NETWORKDAYS function because it allows you to control which days of the week are considered weekends. The weekend argument is set using a list of codes which I have summarised below in a table.
NETWORKDAYS.INTL can also optionally take into account holidays. For the holidays’ argument, supply a range that contains holiday dates. These dates also treated as non-working days and will not be included in the result.
So, let’s return to our example and calculate the following scenarios.
Weekdays are Tuesday To Saturday, Public holidays are 1 April until 2 April 2019. With this new information, I can more accurately calculate the deadline for my tasks.
I can now calculate the deadline for my tasks, with a different weekend schedule and some built-in holiday dates. Looks like one of my tasks is due!. This is a really easy way to set a reminder in Excel.
Other Excel Blog Posts In This Series.
Formula Friday – Is That Odd? Testing A Number With ISODD Function.(Opens in a new browser tab)
Flag Multiple Matches In Your VLOOKUP Formula(Opens in a new browser tab)
If you want more 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.
