Mondays and Fridays with month table.

[t0ny84]

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.

Thanks in Advance

 

[Phuoc]

Try this in cell D4 and copy down:

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

 

[t0ny84]

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]

= ROWS($D$4:D4) -> the result is 1

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

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

...