Get start and end date for week (challenging)

[rpaulson]

Hello all,

not as easy as it sounds

we use weeks that start on Monday as the first day of the week. therefore in our WEEKNUM formula we use type 21. example =WEEKNUM (A1, 21).

there are times wen the week number will wrap thru a year change.

I'm looking for a formula or VBA option to get the start and end of the week if I supply the year and the week. (See sample).

A1=2022
A2=13

should fetch March 28, 2022 and April 3 2022

-Ross

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C
1	2022	28/03/2022	03/04/2022
2	13
Main

Cell Formulas
Range	Formula
B1	B1	=WORKDAY.INTL(DATE(A1,1,A2*7),-1,"0111111")
C1	C1	=WORKDAY.INTL(B1,1,"1111110")

 

[rpaulson]

txs Fluff, as always you never disappoint.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[rpaulson]

throws an "Argument not optional" error in in my VBA

Range("Z1") = WorksheetFunction.WorkDay.INTL(WorksheetFunction.Date(2022, 1, 13 * 7), -1, "0111111")

ideas?

 

[Fluff]

Change the . in the function name to an underscore.

 

[rpaulson]

no luck.

Range("Z1") = WorksheetFunction.WorkDay_Intl(WorksheetFunction.Date(2022, 1, 13 * 7), -1, "0111111")

 

[Fluff]

Use the vba dateserial instead

Range("Z1") = WorksheetFunction.WorkDay_Intl(DateSerial(2022, 1, 13 * 7), -1, "0111111")

 

[rpaulson]

works, thanks again,

on a side note: to return the end date could I just not use B1+6 instead of WORKDAY.INTL(B1,1,"1111110")

 

[Fluff]

Yes you could do that.