Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Monthly Close-Out Dates.

# Monthly Close-Out Dates

Written by Allen Wyatt (last updated December 19, 2020)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365

Different companies handle their workload 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. 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 (10279) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel 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

Understanding Nonprinting Characters

Even characters that print nothing still take space in your document. Characters such as tabs, spaces, breaks, and the ...

Discover More

If you have conditional formatting applied in a worksheet, the formulas in those formats may not be as secure as you ...

Discover More

Limiting What is Shown in the Open Dialog Box

When you display the Open dialog box, Word has to determine what files to display. This tip looks at a baffling ...

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 (ribbon)

Determining Contract Weeks

Everyone seems to determine the difference between dates differently. Nicole has a need to calculate contact weeks (the ...

Discover More

Calculating Differences in Months using Non-Standard Date Values

Dates can be entered into a worksheet in any number of unique or novel ways. Working with those dates can be a challenge, ...

Discover More

Converting European Dates to US Dates

Those in Europe use a date format that is different than those in the US; this is not news. But what if you need to ...

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 4 + 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 ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.