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

Converting Field Results to Text

Fields are meant to be dynamic, providing a result based on conditions at the time they are updated. You may want to ...

Discover More

Centering Information in Table Cells

One of the most common ways to format information in a table is to apply some sort of alignment to the contents of table ...

Discover More

When you copy workbooks that contain links, you may be at a loss as to how to update those links. There are a couple of ...

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!

Incrementing Months in Dates

Excel can easily store dates. If you want to increment a date by one month, there are a number of ways you can accomplish ...

Discover More

Displaying a Number as Years and Months

How do you display a number of years, such as 3.67 years, as a number of years and months? It's simple to do with a ...

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."

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 8 - 7?

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.