Next Sunday Formula

[sasuser_sk]

I have formula for next Sunday and previous Monday of the week.

SUNDAY:   intnx('week.1',datepart(ACTVN_DAT),1)

Monday:   intnx('WEEK.2',datepart(ACTVN_DAT),0)

But I realized that it is giving me next Sunday if my ACTVN_DAT falls on Sunday itself (which tells formula is good). Example is below:

How can I amend my formula to keep my Next_Sunday as it is if it falls on Sunday. This is what I want in my data:

March 21st & Jan 3rd was Sunday. So I want Next_Sunday as 03/21/2021 & 01/03/2021 respectively. However. Jan 21st is Thursday so my formula works right for this date.

Thanks for your help.

[FreelanceReinh · Accepted solution]

Hi @sasuser_sk,

Just subtract one day before applying INTNX:

Next_Sunday=intnx('week',datepart(ACTVN_DAT)-1,1);

(The ".1" after "week" is redundant.)

[sasuser_sk]

Thank you @FreelanceReinh . This worked just fine.