Forum Discussion

Kijam650's avatar
Kijam650
Copper Contributor
Oct 15, 2020

Excel Formula / Function for dates

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 ...
excel
Formulas and Functions
Kijam650's avatar
Kijam650
Copper Contributor
Oct 22, 2020

HansVogelaar 

Hello, for me personally it doesn't matter which calender week system I use, als long as it works .
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 22, 2020

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)

Resources