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 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.
5 Replies
With permission from everyone, I have added an example.
WEEKNUM(serial_number,[return_type])
=WEEKNUM(C5,21)
WEEKNUM function
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.
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)
