t0ny84
Board Regular
- Joined
- Jul 6, 2020
- Messages
- 205
- 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