WORKDAY + Beginning of Month!

**Keibri** · 11-23-2016, 02:52 PM

Hi all,

I would like to ask whether someone can assist me with the attached file.

Basically, I have two dates inserted in a workbook:
-one in cell A3 e.g. 01/01/2017
- another in cell B3. e.g. 31/05/2017

Would it be possible to put a formula in, say, cell E3 which basically would calculate all the firstworking days for each month which falls between the period 01/01/2017 to 31/05/2017 (i.e the dates found in cell A3 and B3)?

In the file attached, I have included manually what I would like to obtain!

Would really appreciate a lot your kind help!

(P.S: I had posted something similar which,however, required the endofmonth rather than the beggining and was wondering whether it could also be done for the beggining of the month)

Thanks a lot

Keibri

**Bernie Deitrick** · 11-23-2016, 03:49 PM

In E3, array-enter (enter using Ctrl-Shift-Enter) this formula, which assumes that you have a list of your holidays in a range named "Holidays"

=MIN(IF(WORKDAY(DATE(YEAR($A$3),MONTH($A$3)+(COLUMN(A1)-1),1)+{-1,0,1,2,3,4,5},1,Holidays)=DATE(YEAR($A$3),MONTH($A$3)+(COLUMN(A1)-1),1)+{0,1,2,3,4,5,6},DATE(YEAR($A$3),MONTH($A$3)+(COLUMN(A1)-1),1)+{0,1,2,3,4,5,6}))

and copy to the right for 4 more columns.

It gets a bit more complicated if you need it to check the date range - but as long as the second date is the end of a month, we can simplify to this (also array-entered using Ctrl-Shift-Enter)

=IF(DATEDIF($A$3,$B$3,"m")>=COLUMN(A1)-1,MIN(IF(WORKDAY(DATE(YEAR($A$3),MONTH($A$3)+(COLUMN(A1)-1),1)+{-1,0,1,2,3,4,5},1,Holidays)=DATE(YEAR($A$3),MONTH($A$3)+(COLUMN(A1)-1),1)+{0,1,2,3,4,5,6},DATE(YEAR($A$3),MONTH($A$3)+(COLUMN(A1)-1),1)+{0,1,2,3,4,5,6})),"")

Then copy to the right until it returns blanks.

**Jonmo1** · 11-23-2016, 04:23 PM

Try like this

In E3 put
=WORKDAY($A3-1,1)

In F3 and filled right as far as required
=IFERROR(1/(1/IF(MONTH(E3)=MONTH($B3),"",WORKDAY(EOMONTH(E3,0),1))),"")

**Keibri** · 11-23-2016, 04:34 PM

Wow! Cheers guys tried both solutions and they worked great!That's some awesome coding! thanks a lot! appreciate it!

**Jonmo1** · 11-23-2016, 04:51 PM

You're welcome.

**Bernie Deitrick** · 11-23-2016, 05:09 PM

Boy, did I over-complicate that....

WORKDAY + Beginning of Month!

LinkBack

Thread Tools

Rate This Thread

Display

WORKDAY + Beginning of Month!

Re: WORKDAY + Beginning of Month!

Re: WORKDAY + Beginning of Month!

Re: WORKDAY + Beginning of Month!

Re: WORKDAY + Beginning of Month!

Re: WORKDAY + Beginning of Month!

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] EOMONTH + WORKDAY for more than 1 month!

[SOLVED] Calculation of days per month for dates beginning in one month and ending in another month

VB Help - Month Beginning Month End Variable

[SOLVED] Determine the 2nd WorkDay of a Month

Find First Workday of Month Value Excl Holiday

calculate workday before x month

Excel2000 VBA: First workday of month

Bookmarks

Bookmarks

Posting Permissions