Mondays and Fridays with month table.

t0ny84

Board Regular
Joined
Jul 6, 2020
Messages
134
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
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.

1594034779473.png


Thanks in Advance
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Phuoc

Active Member
Joined
Apr 29, 2016
Messages
314
Try this in cell D4 and copy down:

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

t0ny84

Board Regular
Joined
Jul 6, 2020
Messages
134
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
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
Joined
Apr 29, 2016
Messages
314
= ROWS($D$4:D4) -> the result is 1

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

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

...
 

Watch MrExcel Video

Forum statistics

Threads
1,127,991
Messages
5,628,007
Members
416,286
Latest member
ko15

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top