HOW TO get date N working days ahead in Excel

I use Excel occasionally and I'm always amazed at how there is always a function that you can adapt to suit a requirement.

To get date 3 working days ahead in Excel there is a helpful WORKDAY function.

It however returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900.

Working days exclude weekends and any dates identified as holidays. WORKDAY can be used to exclude weekends or holidays

Excel stores all dates as integers and all times as decimal fractions. With this system, Excel can add, subtract, or compare dates and times just like any other numbers, and all dates are manipulated by using this system.

So the formula to get a DATE 3 working days ahead in Excel takes a little more work as we have to convert the serial number representing the resulting date -

=DATE(YEAR(WORKDAY(A1,3)),
MONTH(WORKDAY(A1,3)),
DAY(WORKDAY(A1,3)))


It is assumed here that you have a valid date in cell A1

Many of the Excel functions including WORKDAY are available in Google Docs Spreadsheet as well.

Comments

Popular posts from this blog

Maven Crash Course - Learn Power Query, Power Pivot & DAX in 15 Minutes

"Data Prep & Exploratory Data Analysis" course by Maven Analytics

Oracle Cloud Infrastructure 2024 Generative AI Professional Course & Certification Exam (1Z0-1127-24)