# Mondays and Fridays with month table.

#### t0ny84

##### Board Regular
Hi All,

I am hoping that someone with more Excel skills might be able to point me in the right direction or tell me this isn't possible, I am currently designing a table which is to show the following

Cell B1 - Month Drop Down List (Using months in Column A).
Cell B4 Week Number (Editable) - Cell C4 Employee Name (Editable) - Cell D3 Monday Date updates based on Month from B1 and the date of the Monday from the week (Based on NUMBER in Cell B4)
Cell B5 Week Number (Editable) - Cell C5 Employee Name (Editable) - Cell C4 Monday (Based on NUMBER in Cell B5) - same as above but for Friday.

I have used the following to get the Monday and Friday (I have these going down as Monday then Friday on repeat changing the highlighted red 8 in below example to get the Monday to Friday but cannot get it to let me have it so I can have it update when the WEEK number is changed.

Monday Formula - =IF(WEEKDAY(EOMONTH(\$A\$1,-1)+1)=2,EOMONTH(\$A\$1,-1)+1,EOMONTH(\$A\$1,-1)+(8 + MOD(1,7))-WEEKDAY(EOMONTH(\$A\$1,-1)))
Friday Formula - =IF(WEEKDAY(EOMONTH(\$A\$1,-1)+1)=2,EOMONTH(\$A\$1,-1)+1,EOMONTH(\$A\$1,-1)+(8+MOD(5,7))-WEEKDAY(EOMONTH(\$A\$1,-1)))

I have tried the below formula which doesn't work and I feel that there should be a smaller/easier formula which might do what is required without having each cell in the C column having the code for each week (4 total).

=IF(B4=1,IF(WEEKDAY(EOMONTH(\$B\$1,-1)+1)=2,EOMONTH(\$B\$1,-1)+1,EOMONTH(\$B\$1,-1)+(8+MOD(1,7))-WEEKDAY(EOMONTH(\$B\$1,-1))))*OR(IFB4=2,,IF(WEEKDAY(EOMONTH(\$B\$1,-1)+1)=2,EOMONTH(\$B\$1,-1)+1,EOMONTH(\$B\$1,-1)+(8+MOD(1,7))-WEEKDAY(EOMONTH(\$B\$1,-1))))

Below is screenshot of the table aiming to have, change the Week Number in B Column and the date in the Day column updates to the Monday (or Friday) which falls within that week.

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### Phuoc

##### Active Member
Try this in cell D4 and copy down:

=WORKDAY.INTL(EOMONTH(\$B\$1,-1),ROWS(\$D\$4:D4),"0111011")

#### t0ny84

##### Board Regular
Try this in cell D4 and copy down:

=WORKDAY.INTL(EOMONTH(\$B\$1,-1),ROWS(\$D\$4:D4),"0111011")

Hi Phuoc,

Thanks so much for a way to simplify\shorten how to obtain the required days, I was wondering though what exactly is the ROWS component doing in this formula?
My understanding is that =WORKDAY.INTL is getting the workdays after the last day of the previous month (EOMONTH gets last day of month in cell B1 then deducts a month - e.g. FEB-2020 would give a date of 31-JAN-2020).

Anthony

#### Phuoc

##### Active Member
= ROWS(\$D\$4:D4) -> the result is 1

= ROWS(\$D\$4:D5) -> the result is 2

= ROWS(\$D\$4:D6) -> the result is 3

...

Replies
5
Views
83
Replies
3
Views
537
Replies
2
Views
67
Replies
5
Views
402
Replies
7
Views
405

1,127,334
Messages
5,624,082
Members
416,010
Latest member
NJT

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back