New 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 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

# Re: Excel Formula / Function for dates

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 ...?

# Re: Excel Formula / Function for dates

Hello, for me personally it doesn't matter which calender week system I use, als long as it works .

# Betreff: Excel Formula / Function for dates

With permission from everyone, I have added an example.

WEEKNUM(serial_number,[return_type])

=WEEKNUM(C5,21)

# WEEKNUM function

https://support.microsoft.com/en-us/office/weeknum-function-e5c43a03-b4ab-426c-b411-b18c13c75340?ui=...

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.

# Re: Excel Formula / Function for dates

See the attached version. It may not do what you want, so let me know if you want something different.

# Re: Excel Formula / Function for dates

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)``````