Hello, Excellers welcome back to another #Excel tip in my #FormulaFriday 2020 blog post series. Today let’s look at how to set a deadline in the future. I have a list of invoices in Excel that have a deadline to be paid. Once this date has expired, I have 10 working days to pay the outstanding balance. With a quick easy formula, I can get the deadline date in workdays easily populated on my Invoice spreadsheet log. So, here is a sample of my Excel data set.
I need to automatically add 10 working days to the deadline date. So, what is the solution?. It is the WORKDAY Function.
The Workday Function Solution.
The Excel WORKDAY function will allow us to add 10 working days to the deadline or Invoice date in our data seen above. It returns a number that represents a date that is the indicated number of working days before or after a date (the starting date).
The Syntax of WORKDAY Function is
= WORKDAY(start_date, days, [holidays])
Where
start_date -this is the start date to be used. It is a required argument,
days – this represents the number of non-weekend and non-holiday days before or after start_date. A positive value for days yields a future date; a negative value yields a past date. This again is a required argument in the formula.
holidays – this is an optional argument. It is a list of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contain the dates or an array constant of the serial numbers that represent the dates.
Writing The Formula.
Ok, let’s go ahead and write our formula. The first version of our formula is without the optional holidays’ argument in the formula.
=WORKDAY(B3,10)
The second version of our formula has some specified holidays which are simply dates that are to exclude from the working calendar. I have set two holidays, that of the 4th and 5th of June 2019. These are stored in Column F on my worksheet.
=WORKDAY(B3,10,$F$3:$F$4)
Subsequently, our deadline date to exclude the two specified ‘holiday’ or non-working dates.
If you want more Excel and VBA 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 Formula Friday Series Click The Link Below
How To Excel At Excel – Formula Friday Blog Posts.
So, Don’t forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.