Snasui Excel Forum

Hi dear

I need your help how to return first Monday of month base on year and week number.

I give an output in color cells

Thanks

...

็็Hi Guy,

Look likes the first week of the year 2021 doesn't have Monday.

If the first week of your point means the first-second-Third-... "MONDAY" each week of "YEAR".

C2 would be: -

=AGGREGATE(15,6,ROW(INDIRECT(--("1/1/"&A2)&":"&--("31/12/"&A2)))/(WEEKDAY(ROW(INDIRECT(--("1/1/"&A2)&":"&--("31/12/"&A2))))=2),B2)


On the other hand, if your point is related to the WEEKNUM as an Excel calendar by allow the FIRST MONDAY to stay in the FIRST WEEK of the YEAR.

C2 would be: -
=IFERROR(LOOKUP(2,1/(WEEKDAY(ROW(INDIRECT(--("1/1/"&A2)&":"&--("31/12/"&A2)))/(WEEKNUM(ROW(INDIRECT(--("1/1/"&A2)&":"&--("31/12/"&A2))))=B2))=2),ROW(INDIRECT(--("1/1/"&A2)&":"&--("31/12/"&A2)))),AGGREGATE(15,6,ROW(INDIRECT(--("1/1/"&A2)&":"&--("31/12/"&A2)))/(WEEKDAY(ROW(INDIRECT(--("1/1/"&A2)&":"&--("31/12/"&A2))))=2),B2))

Please give more clarification If I've missed out on something that would be useful to you.

Norkaz

thank you