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

Printing a Chart

It is inevitable that if you spend time creating a chart you will want to print that chart on your printer. Here's how you ...

Discover More

Moving Comment Background Pictures to Cells

When formatting comments, you can use a graphic as a background for the comment box. If you later want to move this graphic ...

Discover More

Excel allows you to easily convert values from decimal to other numbering systems, such as hexadecimal. This tip explains how ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

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

Unique Military Date Format

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

Discover More

Calculating Months of Tenure

Need to know the number of months between two dates? It's easy to figure out if you use the DATEDIF function.

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}] in your comment text. Youâ€™ll be prompted to upload your image when you submit the comment. 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 7 + 8?

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.