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

by Allen Wyatt
(last updated October 24, 2015)

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

Applying Bullets from the Keyboard

Most people use the toolbars to apply bullets to paragraphs. If you want to apply them using the keyboard, then you can do so ...

Discover More

Too Many Edits Prevent Pasting

Sometimes Word can be rather cryptic in the error messages it provides. One such cryptic message warns about "too many ...

Discover More

Transposing Two Words

A common editing task is to transpose two adjacent words, so that their order is changed. While the task is common, there is ...

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)

Finding the Dates for Minimums and Maximums

If you use Excel to maintain a collection of data, you may need to find information in one column based on information in an ...

Discover More

Converting Coded Dates into Real Dates

Sometimes the format in which you receive data is not the same format that would be optimal for Excel. For instance, you ...

Discover More

Calculating Week-Ending Dates

When working with dates, you may need to figure out all the dates on which weeks end in a given year. There are several ...

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}] 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 6 - 0?

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.