Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Monthly Close-Out Dates.

Monthly Close-Out Dates

Written by Allen Wyatt (last updated July 28, 2020)
This tip applies to Excel 97, 2000, 2002, and 2003


Different companies handle their work load differently. Some companies are tied to the regular calendar, with a "work month" beginning on the first and concluding on the last workday of the month. Other companies adjust the monthly starting and ending dates to meet particular needs for accounting or other purposes.

You may have a need to figure out your particular close-out date for a month. By using a couple of functions from Excel's Analysis ToolPak, you can make quick work of this task.

The primary function to use is the WORKDAY function. This function allows you to determine a particular workday before or after a starting date. For instance, if you needed to know the workday three days before today, you could use the function in this manner:

=WORKDAY(TODAY(),-3)

The first argument for WORKDAY is the TODAY function, which provides today's date. The second argument indicates how many days before or after today you want. Remember that WORKDAY only returns actual workdays, Monday through Friday. (Well, it returns the dates for those workdays. It's not like the WEEKDAY function, which returns 0 through 7 for the day of the week.) In this particular instance, if today is a Tuesday, then three workdays before Tuesday is Thursday, and WORKDAY returns the date for that Thursday.

The next function you need to use is EOMONTH, which returns the date for the end of the month a given number of months before or after a particular date. To find the end of the current month, you would use the function in the following manner:

=EOMONTH(TODAY(),0)

The first argument for EOMONTH is the TODAY function which, again, provides today's date. The second argument indicates how many months before or after that date you want the end of month for. Since the argument is 0, this usage of EOMONTH returns the last day of the current month.

If you combine the WORKDAY and EOMONTH, you can determine the third workday before the end of the current month, in this manner:

=WORKDAY(EOMONTH(TODAY(),0),-3)

If you wanted to figure out the third workday before the end of a different month, just replace the TODAY function with a date in the month you want. For instance, if cell C2 contains a date, and you want to know the third workday before that date's end of month, you would use the following:

=WORKDAY(EOMONTH(C2,0),-3)

It should be noted that this formula actually returns the third workday before the last day in the month, not the third workday before the last workday in the month. This comes into play, of course, when dealing with months that end on Saturday or Sunday. If a month ends on a Saturday or Sunday, the function returns three workdays before that date, which would be a Wednesday. However, if you wanted the date three days before the last workday (which is Friday), you actually want Tuesday, not Wednesday.

In this case, the formula gets much more complex because now you need to check to see if the actual end of the month is a Saturday or Sunday. Again assuming that you are basing everything on a date in C2, you could use the following formula:

=IF(OR(WEEKDAY(EOMONTH(C2,0))=1,WEEKDAY(EOMONTH(C2,0))=7),
WORKDAY(WORKDAY(EOMONTH(C2,0),-1),-3), WORKDAY(EOMONTH(C2,0),-3))

If the end of the month in C2 is a Saturday (7) or Sunday (1), then the formula calculates the workday one day before (the Friday) and then figures the workday three days before that. Otherwise, the regular formula that calculates the third prior workday is used.

If you want the formula to be even more accommodating, remember that WORKDAY can compensate for a set of holidays, as well. The easiest way to allow for holidays is to put the dates of a set of holidays into a named range (such as "Holidays"), and then add a third parameter to the WORKDAY function, as shown here:

=WORKDAY(EOMONTH(C2,0),-3,Holidays)

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3287) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Monthly Close-Out Dates.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Determining If a File Exists

Before you have your macro open and read a file from disk, you'll want to check to make sure it is really there. Here's ...

Discover More

Changing Axis Tick Marks

Create a chart in Excel, and you may find that the tick marks shown on the axes in the chart aren't to your liking. It is ...

Discover More

Adding a Macro to the Quick Access Toolbar

One of the easiest ways to quickly access a macro is to assign it to the Quick Access toolbar. Here's how you can make ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (menu)

Unique Military Date Format

Some industries (such as the military) have special formatting that they use to represent dates. Here is one such format ...

Discover More

Days Left in the Year

Sometimes it is handy to know how many days are left in the current year. This tip provides a quick formula that ...

Discover More

Calculating an Age On a Given Date

Start putting dates in a worksheet (especially birthdates), and sooner or later you will need to calculate an age based ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 2 + 2?

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.