Excel Formula / Function for dates

%3CLINGO-SUB%20id%3D%22lingo-sub-1785403%22%20slang%3D%22de-DE%22%3EExcel%20Formula%20%2F%20Function%20for%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1785403%22%20slang%3D%22de-DE%22%3E%3CP%3EHello%20everyone%2C%3C%2FP%3E%3CP%3EI%20hope%20someone%20here%20can%20help%20me.%3C%2FP%3E%3CP%3EI%20am%20looking%20for%20an%20Excel%20function%20that%20will%20enter%20the%20respective%20dates%20for%20the%20corresponding%20year%20and%20calendar%20week.%3C%2FP%3E%3CP%3EFor%20example%2C%20if%20I%20enter%202021%20and%20Monday%20and%20Friday%20(yellow)%2C%20the%20associated%20data%20for%20Mondays%20and%20Fridays%20(blue)%20should%20then%20be%20entered%20automatically%20for%20all%2052%20calendar%20weeks.%20So%20that%20I%20don't%20have%20to%20re-enter%20it%20every%20year%20by%20myself.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-10-15%20210711.jpg%22%20style%3D%22width%3A%20440px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F227019i09F4379C29C41604%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202020-10-15%20210711.jpg%22%20alt%3D%22Screenshot%202020-10-15%20210711.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1785403%22%20slang%3D%22de-DE%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1785857%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20%2F%20Function%20for%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1785857%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F834150%22%20target%3D%22_blank%22%3E%40Kijam650%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhen%20does%20week%201%20start%3F%20Using%20ISO%20week%20number%20(week%201%20is%20the%20week%20containing%20the%204th%20of%20January%2C%20and%20Monday%20is%20the%20first%20day%20of%20the%20week)%20or%20using%20the%20US%20system%20(week%201%20starts%20on%20the%201st%20of%20January%2C%20and%20Sunday%20is%20the%20first%20day%20of%20the%20week)%2C%20or%20...%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1807389%22%20slang%3D%22de-DE%22%3ERe%3A%20Excel%20Formula%20%2F%20Function%20for%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1807389%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22QmZWSe%22%3E%3CDIV%20class%3D%22DHcWmd%22%3EHello%2C%20for%20me%20personally%20it%20doesn't%20matter%20which%20calender%20week%20system%20I%20use%2C%20as%20long%20as%20it%20works%20.%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1807556%22%20slang%3D%22de-DE%22%3ESubject%3A%20Excel%20Formula%20%2F%20Function%20for%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1807556%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F834150%22%20target%3D%22_blank%22%3E%40Kijam650%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20permission%20from%20everyone%2C%20I%20have%20added%20an%20example.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EWEEKNUM(serial_number%2C%5Breturn_type%5D)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DWEEKNUM(C5%2C21)%3C%2FSPAN%3E%3C%2FP%3E%3CH1%20id%3D%22toc-hId-559800886%22%20id%3D%22toc-hId-559800886%22%3E%3CFONT%20size%3D%224%22%3EWEEKNUM%20function%3C%2FFONT%3E%3C%2FH1%3E%3CP%3E%3CFONT%20size%3D%224%22%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fweeknum-function-e5c43a03-b4ab-426c-b411-b18c13c75340%3Fui%3Den-us%26amp%3Brs%3Den-us%26amp%3Bad%3Dus%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fweeknum-function-e5c43a03-b4ab-426c-b411-b18c13c75340%3Fui%3Den-us%26amp%3Brs%3Den-us%26amp%3Bad%3Dus%3C%2FA%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3E*%20Kindly%20Mark%20and%20Vote%20any%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20community%20members%20reading%20here.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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.

 

Screenshot 2020-10-15 210711.jpg

5 Replies

@Kijam650 

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

@Hans Vogelaar 

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

@Kijam650 

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.

 

 

@Kijam650 

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

@Kijam650 

As variant

image.png

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)