Forum Discussion
Kijam650
Oct 15, 2020Copper Contributor
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 ...
Kijam650
Oct 22, 2020Copper Contributor
Hello, for me personally it doesn't matter which calender week system I use, als long as it works .
SergeiBaklan
Oct 22, 2020Diamond Contributor
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)