cell A4 is date
cell B2 is =WEEKNUM($A4,11)
I am using this formula
=WORKDAY.INTL($A4,B$2,"0111111")
which works fine when the start date in cell A4 as 1 Jan 2021,
if this date is changed to other dates for example 1 Feb 2021 the result is not as desired (need Monday)
Kindly see the attached file.
Thank you.
Maybe:
=IF(WORKDAY.INTL($A4,N$2,"0111111")>=EOMONTH(DATE(YEAR(A4),COUNTIF($A$4:A4,A4),1),0),"",WORKDAY.INTL($A4,N$2,"0111111"))
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
FWIW, I'd also de-merge the green cells, use this in B3, copied down:
=IF(A3="",DATE(YEAR($A4),COUNTIF($A$4:A4,A4),1),"")
and then (ONLY if you REALLY want to) remerge them (better NOT to, as merged cells are truly horrible).
Remove row 2
B3:
=A4
A4:
"1/1/2020"
A5:
=EDATE(A4,-12)
Copy to A9
A11:
=EDATE(A4,1)
A12:
=EDATE(A5,1)
Copy to A16
D4:
Copy to G,J,M,P
Please Login or Register to view this content.
Copy B3:P9 to B10:P16
Quang PT
Please try at D4
=LET(a,WORKDAY.INTL(DATE(YEAR($A4),MONTH($B$3),0),COLUMNS($B4:D4)/3,"0111111"),IF(MONTH(a)=MONTH($B$3),a,""))
Thank you thank you sooooo much ...Glenn, bebo021999 and Bo_Ry. I can't thank you enough all of you, made
my long weekend productive.
Happy Easter guys. God bless you all.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks