Excel Formula / Function for dates

[Kijam650]

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.

[#2]

@Kijam650 

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 ...?

[#3]

@Hans Vogelaar 

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

[#4]

@Kijam650 

With permission from everyone, I have added an example.

WEEKNUM(serial_number,[return_type])

=WEEKNUM(C5,21)

WEEKNUM function

https://support.microsoft.com/en-us/office/weeknum-function-e5c43a03-b4ab-426c-b411-b18c13c75340?ui=...

I would be happy to know if I could help.

Nikolino

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.

[#5]

@Kijam650 

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

[#6]

@Kijam650 

As variant

to play with latest functions

=LET(
   daysOfTheWeek,
    {"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"},
   firstDayOnTheWeek, MATCH(B2,daysOfTheWeek,0),
   lastDayOnTheWeek, MATCH(C2,daysOfTheWeek,0),
   daysInTheYear, 365,
   firstMondayInTheYear,DATE(A2,1,8)-WEEKDAY(DATE(A2,1,6)),
   allDaysInTheYear, SEQUENCE(365,1,firstMondayInTheYear),
   Result, TEXT(FILTER(
                 SEQUENCE(daysInTheYear,1,firstMondayInTheYear),
                 (WEEKDAY(allDaysInTheYear,2)=firstDayOnTheWeek)+
                 (WEEKDAY(allDaysInTheYear,2)=lastDayOnTheWeek)
              ),"dd mmm yyyy"),
 Result)