Excel Formula / Function for dates

New Contributor

Hello everyone,

I hope someone here can help me.

I am looking for an Excel function that will enter the respective dates for the corresponding year and calendar week.

For example, if I enter 2021 and Monday and Friday (yellow), the associated data for Mondays and Fridays (blue) should then be entered automatically for all 52 calendar weeks. So that I don't have to re-enter it every year by myself.


Screenshot 2020-10-15 210711.jpg

5 Replies


When does week 1 start? Using ISO week number (week 1 is the week containing the 4th of January, and Monday is the first day of the week) or using the US system (week 1 starts on the 1st of January, and Sunday is the first day of the week), or ...?

@Hans Vogelaar 

Hello, for me personally it doesn't matter which calender week system I use, als long as it works .


With permission from everyone, I have added an example.




WEEKNUM function




I would be happy to know if I could help.



I know I don't know anything (Socrates)


* Kindly Mark and Vote any reply if it helps please, as it will be beneficial to more Community members reading here.




See the attached version. It may not do what you want, so let me know if you want something different.


As variant


to play with latest functions

   firstDayOnTheWeek, MATCH(B2,daysOfTheWeek,0),
   lastDayOnTheWeek, MATCH(C2,daysOfTheWeek,0),
   daysInTheYear, 365,
   allDaysInTheYear, SEQUENCE(365,1,firstMondayInTheYear),
   Result, TEXT(FILTER(
              ),"dd mmm yyyy"),