Calculate Backdated dates

[shansakhi]

Hello All,
Request your assistance to know how do I calculate below consider 30th Jun as base date.
For example
1 JUL if I do 1JUL-7 then it is less than 30 JUN and there passed the condition
10 JUL if I do 10 JUL-7 then it is grater than 30 JUN and hence I have to take 10JUL-14
.
.
.
6JAN if I do 6JAN-7 then it is grater than 30 JUN and hence I have to take 6JAN-196

30JUN is a dynamic date and will change to different date / also it date column will get extended to future years.

I am looking for a excel formula or either help in Power BI.

Book1
	A	B	C	D	E
1	30-Jun-24
2	Date	Day	Latest Back Date	Day
3	1-Jul-24	Monday	24-Jun-24	Monday
4	10-Jul-24	Wednesday	26-Jun-24	Wednesday
5	20-Jul-24	Saturday	29-Jun-24	Saturday
6	30-Jul-24	Tuesday	25-Jun-24	Tuesday
7	9-Aug-24	Friday	28-Jun-24	Friday
8	19-Aug-24	Monday	24-Jun-24	Monday
9	29-Aug-24	Thursday	27-Jun-24	Thursday
10	8-Sep-24	Sunday	30-Jun-24	Sunday
11	18-Sep-24	Wednesday	26-Jun-24	Wednesday
12	28-Sep-24	Saturday	29-Jun-24	Saturday
13	8-Oct-24	Tuesday	25-Jun-24	Tuesday
14	18-Oct-24	Friday	28-Jun-24	Friday
15	28-Oct-24	Monday	24-Jun-24	Monday
16	7-Nov-24	Thursday	27-Jun-24	Thursday
17	17-Nov-24	Sunday	30-Jun-24	Sunday
18	27-Nov-24	Wednesday	26-Jun-24	Wednesday
19	7-Dec-24	Saturday	29-Jun-24	Saturday
20	17-Dec-24	Tuesday	25-Jun-24	Tuesday
21	27-Dec-24	Friday	28-Jun-24	Friday
22	6-Jan-25	Monday	24-Jun-24	Monday
Sheet1

Regards,
Shan

 

[Joe4]

Use this formula (in cell C3) and copy down:

=LET(d,WEEKDAY(A3)-WEEKDAY($A$1),IF(d=0,$A$1,IF(d<0,$A$1+d,$A$1-7+d)))

Results from your example:

 

[Tetra201]

A slightly shorter one:

=A3-MAX(0,ROUNDUP((A3-$A$1)/7,0)*7)

 

[Joe4]

A slightly shorter one:

=A3-MAX(0,ROUNDUP((A3-$A$1)/7,0)*7)

Wow, that is really much simpler than my formula, and seems to give the exact same results as mine.

 

[shansakhi]

Thank you, Joe4 and Tetra201.
Both the formulas are giving same result.
Thank you so much.

 

[Joe4]

You are welcome.
Glad we were able to help!