Hello Excellers and welcome back to another #Exceltip in my #FormulaFriday 2019 series. Today let’s look at how to get a reminder in Excel if your task deadline is approaching. This approach would be very useful for projects and reporting schedules. We will be using the NETWORKDAYS Function.
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, 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.INTL (start_date, end_date, [weekend], [holidays])
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. Work days 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. They are 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!.
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.
Likewise, if you want to see all of the blog posts in the Macro Mondays Series Click The Link Below