Formula Help

[Rob_010101]

Hello All,

I need a formula that looks back 11 weeks from a given date and returns the Sunday at the beginning of that week.

- Example, 27/10/2022. 11 weeks prior is 11/08/2022 and the Sunday beginning that week is 07/08/2022. The formula would return 07/08/2022.

I also need one that looks back 41 weeks from a given date and returns the Sunday at the beginning of that week.

Kind Regards
Chris

 

[Fluff]

How about

=WORKDAY.INTL(A2,-12,"1111110")

 

[Rob_010101]

How about

=WORKDAY.INTL(A2,-12,"1111110")

Thank you,

This works for Monday - Saturday but if the date in A2 is a Sunday, it returns the Sunday the week prior.

Example,
Saturday, 1 October 2022 - returns Sunday, 10 July 2022 - ok
Sunday, 2 October 2022 - returns Sunday, 10 July 2022 - need it to return Sunday, 17 July 2022

This is for a maternity leave calculator and the law regarding dates and entitlements is very complex.

Kind Regards
Chris

 

[Fluff]

Ok, how about

=WORKDAY.INTL(A2+1,-12,"1111110")

 

[Dave Patton]

Workday.xlsm
	A	B	C
1		11 weeks	41 weeks
2	Thu 27-Oct-22	Sun 07-Aug-22	Sun 09-Jan-22
3	Sat 01-Oct-22	Sun 10-Jul-22	Sun 12-Dec-21
4	Sun 02-Oct-22	Sun 17-Jul-22	Sun 19-Dec-21
5
3c

Cell Formulas
Range	Formula
B2:B4	B2	=A2-11*7-WEEKDAY(A2-77,17)+1
C2:C4	C2	=A2-41*7-WEEKDAY(A2-287,17)+1

 

[Dave Patton]

Workday.xlsm
	A	B	C
1		11 weeks	41 weeks
2	Thu 27-Oct-22	Sun 07-Aug-22	Sun 09-Jan-22
3	Sat 01-Oct-22	Sun 10-Jul-22	Sun 12-Dec-21
4	Sun 02-Oct-22	Sun 17-Jul-22	Sun 19-Dec-21
5
6	Thu 27-Oct-22	Sun 07-Aug-22	Sun 09-Jan-22
7	Sat 01-Oct-22	Sun 10-Jul-22	Sun 12-Dec-21
8	Sun 02-Oct-22	Sun 17-Jul-22	Sun 19-Dec-21
9
3c

Cell Formulas
Range	Formula
B2:B4	B2	=A2-11*7-WEEKDAY(A2-77,17)+1
C2:C4	C2	=A2-41*7-WEEKDAY(A2-287,17)+1
B6:B8	B6	=WORKDAY.INTL(A6-76,-1,"1111110")
C6:C8	C6	=WORKDAY.INTL(A6-286,-1,"1111110")

 

[Rob_010101]

Workday.xlsm
	A	B	C
1		11 weeks	41 weeks
2	Thu 27-Oct-22	Sun 07-Aug-22	Sun 09-Jan-22
3	Sat 01-Oct-22	Sun 10-Jul-22	Sun 12-Dec-21
4	Sun 02-Oct-22	Sun 17-Jul-22	Sun 19-Dec-21
5
3c

Cell Formulas
Range	Formula
B2:B4	B2	=A2-11*7-WEEKDAY(A2-77,17)+1
C2:C4	C2	=A2-41*7-WEEKDAY(A2-287,17)+1

Thanks very much for your reply.

This one works too!

Kindest Regards

 

[Fluff]

Glad we could help & thanks for the feedback.