CODEWITHSUNDEEP
exceldate
HL8
HL8

Reputation: 1419

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

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,

Upvotes: 2

Views: 12576

Answers (1)

Glenn Stevens
Glenn Stevens

Reputation: 2008

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.

Upvotes: 3

Related Questions