Look Who it Was Random video

Excel - Calculate Work Days Until End of the Month using EOMONTH & WORKDAY Functions - Episode 560

402 views · Published 6 July 2009 · 2:40 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Calculate Work Days Until End of the Month using EOMONTH & WORKDAY Functions.

Welcome back to the MrExcel netcast where we answer your Excel questions and provide helpful tips and tricks. I'm Bill Jelen and today we have a question from Vishnu in India. If you have a question for the netcast, don't hesitate to leave a voicemail or send us an email and we'll feature it in a future podcast.

Coincidentally, Vishnu's question ties in perfectly with our previous episode where we discussed how to find the end of the month. He wants to know how to find the number of work days before the end of the month, not just the last day. He mentioned using the EOMONTH function, which is part of the Analysis Toolpak. If you're using Excel 2003 or earlier, make sure to enable the Analysis Toolpak under Tools ＞ Add-Ins. For those using Excel 2007 or later, the function is automatically available.

To find the number of work days before the end of the month, we'll use a combination of the EOMONTH and WORKDAY functions. First, we'll use the EOMONTH function to find the end of the month for a given date. Then, we'll use the WORKDAY function to calculate the number of work days before that date. One thing to note is that these functions do not automatically format the cell as a date, so we'll need to do that manually by selecting the cells and using the Format Cells option.

The WORKDAY function is a powerful tool that allows us to calculate dates based on work days, rather than just calendar days. In this case, we'll use a negative number to find the work days before the end of the month. For example, if we want to know the date that is one work day before the end of the month, we'll use -1 as the second argument in the function. We can also use this method to find multiple work days before the end of the month by simply changing the number in the second argument.

Thanks to Vishnu for sending in this question and allowing us to showcase this useful Excel function. If you have a question for the podcast, don't hesitate to send it in. Thanks for tuning in and we'll see you next time for another netcast from MrExcel.

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) Introduction
(00:16) Submitting Questions for the Netcast
(00:26) Coincidence with Previous Topic
(00:37) Using EOMONTH Function
(00:56) WORKDAY Function
(01:06) Formatting Cells
(01:23) WORKDAY Function Continued
(01:47) Negative Numbers with WORKDAY Function
(02:08) Combination of EOMONTH and WORKDAY Functions
(02:18) 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:
Analysis Toolpak
Date function
Day of the week
EOMONTH function
Excel 2003
Format Cells
July 30th
Negative numbers
Probation period
Thursday, July 26th
WORKDAY function

This video answers these common search terms:
Analysis Toolpak
Date function
Day of the week
EOMONTH function
Excel 2003
Format Cells
July 30th
Negative numbers
Probation period
Thursday, July 26th
WORKDAY function

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



How can you figure out a date that is a certain number or workdays BEFORE a date? On the last episode of The Lab with Leo Laporte, I showed how to calculate a date 45 workdays after a certain date. Todays question is how to calculate the date 5 days before the end of the month. Episode 560 shows you how to calculate this value.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

More from this channel