Excel - Previous Work Day?: Episode 1421

1,959 views · Published 6 September 2011 · 2:55 · Indexed 14 November 2026

Channel: MrExcel.com · 2011 · Education

Watch on YouTube

Microsoft Excel Tutorial: How to Calculate the Previous Work Day in Excel (Including Holidays) | MrExcel Podcast.

Welcome back to the MrExcel podcast. In this episode, we will be discussing the topic of "Previous Work Day". As we just got back from a long holiday weekend, it brings up the question of how to calculate the previous work day. Yesterday was Labor Day in the United States, so there was no podcast, and no work. But fear not, because I have a solution for you.

First, I will show you a simple calculation using the "work day" function. I will input today's date using the shortcut [ctrl ;] and change the format to a long date. Let's say today is Tuesday, September 6. Now, to find the previous work day, I will use the formula "=WORKDAY(start_date, days, [holidays])". In this case, the start date is today, and the number of days is -1. However, we also need to consider holidays, so I have included an optional third argument with a list of federal holidays for 2011 and 2012. This will give us the correct previous work day, taking into account any holidays.

But what if you have a different work week, such as working Monday through Saturday? In the past, this would have been difficult to calculate, but now there is a solution. The "work day international" function allows you to specify the weekend days, so you can input any work week and still get the correct previous work day. Simply use the formula "=WORKDAY.INTL(start_date, days, [weekend], [holidays])" and choose from the list of options for the weekend days.

One helpful tip is that you can select the list of holidays and press [F9] to embed the date codes in the formula. This way, you won't have to worry about updating the list every year. The range of this list is five years, so you can continue using it for a while. And if you ever need to change the holidays, just remember to update the formula.

Thank you for tuning in to this episode of the MrExcel podcast. I hope this tip on calculating the previous work day was helpful for you. Don't forget to subscribe to our channel for more Excel tips and tricks. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Previous Work Day
(00:18) Calculation for Previous Work Day
(00:28) Using [ctrl ;] for Today's Date
(00:40) Formula for Previous Work Day
(00:50) Optional Third Argument for Holidays
(01:19) Embedding Holiday Dates in Formula
(01:55) Using Work Day International for Non-Traditional Work Weeks
(02:45) Clicking Like really helps the algorithm

#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:
Custom work week calculation
Embedding date codes
Episode 1421
Federal holidays
Labor Day
Learn Excel
Previous work day calculation
Today's date calculation
Work day function
Work day international function


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153099/


September 5, 2011 was the 'Labor Day' Holiday in the States; Labor Day is celebrated in honor of those workers who have contributed economically and socially...but it can make spreadsheet calculations a little difficult [as can most holidays, weekends, vacation time].  Today, in Episode #1421, Bill shows us how to calculate the previous work day - particularly when that previous day is a holiday. Learn how to work with the =WORKDAY Function, Holidays, Weekends, a few Excel Shortcuts and More when calculating time with Excel.

...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!

More from this channel