Excel Date Formula to find date for the 2nd business day of the next month

Question

I have a spreadsheet with the the month I'm reporting on, but I want work out the due date, which is the 2nd business day of the following month.

Report Month               Due Date
January-13                 04-Feb-13

Thank you,

Solution

If you are using Excel 2007 or later or if you are using have Excel 2003 or earlier and have the Analysis toolpack add-in enabled, you can use EOMONTH to find the first day of the month (EOMONTH returns the last day of the month, so add 1 day to it for the first day of the next month) and WORKDAY to find the second work day after the date returned by EOMONTH.

If you do not want to factor holidays into this, the formula would look like:

=WORKDAY((EOMONTH("January-2013",0)),2)

If you did want to factor holidays into this, you need to add a third agrument to the WORKDAY function call with a list of dates of the holidays you want included. The easiest way to handle build the list is to use a range containing the dates of the holidays.

TechRepublic has a good post on the WORKDAY function.

One thing I would caution you about is, if possible feed the month in using the format Month-YYYY instead of Month-YY as excel will usually interpret a date entered as Month-YY as being Month-DD-Current year. Microsoft goes as far to recommend using the date formula to build the dates being passed into these functions instead of using text values for them.

Comments

[Preet Sangha]

Before anyone votes to close this please remember that this is a programming question as Excel formulae are programming the Excel API and we all know that you can also use VB program it.

[Tim Williams]

=WORKDAY(EOMONTH(B2,0)+1,2)

[Glenn Stevens]

The formula @TimWilliams posted in his comment will be one day later than the 2nd business day of the month but it made me realize I could simplify my example code.

[barry houdini]

+1 Note that Analysis ToolPak os usine Excel 2003 or earlier. In Excel 2007 or later both EOMONTH and WORKDAY are built-in functions

[barry houdini]

Sorry, my comment became garbled - should say "Analysis ToolPak is only required if you are using Excel 2003 or earlier......"

[Glenn Stevens]

Thanks @barry I have always used Excel with the Analysis Toolpack installed and enabled so I didn't even realize that EOMONTH and WORKDAY have become part of the base product. I updated my answer to include reflect this.