Excel - Previous Work Day?: Episode 1421
1,959 views · Published 6 September 2011 · 2:55 · Indexed 14 November 2026
Channel: MrExcel.com · 2011 · Education
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
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920