Forum Discussion
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 ...
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 ...?
Hello, for me personally it doesn't matter which calender week system I use, als long as it works .
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)See the attached version. It may not do what you want, so let me know if you want something different.
