Oct 15 2020 12:09 PM
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.
Oct 15 2020 01:20 PM
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 ...?
Oct 22 2020 02:13 AM
Oct 22 2020 03:13 AM
With permission from everyone, I have added an example.
WEEKNUM(serial_number,[return_type])
=WEEKNUM(C5,21)
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.
Oct 22 2020 03:32 AM
See the attached version. It may not do what you want, so let me know if you want something different.
Oct 22 2020 05:21 AM
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)