Excel - Use Previous Workday If Not A Workday - Episode 2543

Microsoft Excel Tutorial: Make sure the due date falls on a workday - use previous workday if it falls on a holiday.

Welcome to another MrExcel netcast! Today, we have a great question from Vicky about using the previous workday if it falls on a weekend or holiday. This is a common issue when trying to calculate daily interest rates for the Fed funds, which are only published on business days. So, we need to go back a day if the date falls on a weekend or holiday.

Before we can tackle this problem, we need to take an extra step and create a named range for the Federal holidays. In this video, I have a worksheet called "holidays" with all the federal holidays listed, including Groundhog's Day (just for fun). At the bottom, I have a note to insert new rows above that row for future holidays. To create a named range, simply select all the holidays and give it a name, such as "holiday range". Make sure there are no spaces in the name and press enter to create it.

Now, we can use the NETWORKDAYS function to determine if a given day is a workday or not. This function includes the first day as a day, unlike the regular end date minus start date formula. So, we start from A2 and go to A2, and for the holiday argument, we specify the range we just created called "holiday range". This will give us a 1 for regular workdays, 0 for weekends, and 0 for holidays.

Next, we use the IF function to check if the result from NETWORKDAYS is equal to 1. If it is, we want to use the date from A2. Otherwise, we need to go back to the previous workday, which is where the WORKDAY function comes in. We start from A2 and use a negative number of days (in this case, -1) to go back to the previous workday. And don't forget to include the "holiday range" as well.

But wait, there's one more step! The WORKDAY and NETWORKDAYS functions are from the Analysis ToolPak, which became part of Excel in 2007. These functions do not automatically convert to dates, so you will see weird serial numbers instead. To fix this, simply select the range and change the format to a short or long date.

And there you have it! The formula is now working and will accurately calculate the previous workday if the date falls on a weekend or holiday. Just remember to create the named range for holidays and format the results as dates. Thank you to Vicky for sending in this great question and thank you for watching. See you next time for another MrExcel netcast!

Table of Contents
(0:00) Introduction
(0:23) Create a named range with holidays
(1:23) Excel formula to detect if date is a workday using NETWORKDAY function
(2:11) WORKDAY function to go back one work day
(3:15) Formatting serial numbers as dates

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

This video answers these common search terms:
Accruing Fed fund interest daily
Analysis tool pack
Business days
Creating a named range
Fed funds rates
Federal holidays
Formula to calculate previous workday
Named range
Network days function
Serial numbers and dates formatting
Weekend or holiday
Workday function

Vicki: I have a list of dates and I'm looking for a formula to calculate the previous workday only if the date in the list is a weekend or holiday.

Why: Accrue Fed Fund Interest Daily (Fed Funds Rate only published on Business Days).
Also: Calculate Rolling Returns for Mutual Funds

This is mildly complex, because you need to be able to create a named range for the holidays, and after building the long formula, Excel will likely give you the date serial number instead of the formatted date.

[simfinso]

The most Required Formula for calculating Rolling Returns in Mutual Fund Industry.Widely Required by Finance professionals all around world... Thanks..

[stayawayjoe]

Love NETWORKDAYS function for custom holidays and custom work week definitions. Why not throw holidays into a table, then define the table column as the named range? Now you can add dates to bottom of the list in any order and they'll be incorporated into table and, therefore, named range. Plus, quick and easy to sort holidays with table auto filter.

[DaveH]

You can try this for previous working day before holidays/weekend =WORKDAY(I2+1, -1, hols)

[dms]

Always do this with a =WORKDAY(WORKDAY(A2, 1, holidays), -1, holidays), you can reverse the -1 and 1 if you want to skip to the next workday too.

[Rapture]

To make it more interesting, how about figuring out which days are holidays in a date range. I needed to do this for POAMs, but it is a little tricky for holidays with floating dates, such as Thanksgiving (required a user defined function).