t0ny84
Board Regular
 Joined
 Jul 6, 2020
 Messages
 146
 Office Version

 365
 2016
 2013
 Platform

 Windows
 Mobile
 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.
Thanks in Advance
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