"next week"

[synergy16]

i have a build schedule that has a date category of "next 7 days" so i use =today()+7, but what i really want it to be is "next week" if i use the +7 its always going to "roll" 7 days but what i really want is for it to use =today() as a reference point and then tell me something has to be built on or before the following saturday. so for example, today is the 27th, i want the formula to say, it has to be built on or before Aug 6. and it would say that date all week until the it became the next sunday. so for example, that category would read "has to be built before Aug 6" (for this week) but when it becomes july 30, i want it to read "has to be built before Aug 13" for that entire week.

hopefully that makes sense.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[synergy16]

365

 

[Fluff]

Thanks for that, how about

Fluff.xlsm
	D	E
1
2	27/07/2021	06/08/2021
3	30/07/2021	13/08/2021
Data

Cell Formulas
Range	Formula
E2:E3	E2	=WORKDAY.INTL(D2,2,"1111011")

 

[synergy16]

Thanks for that, how about

Fluff.xlsm
	D	E
1
2	27/07/2021	06/08/2021
3	30/07/2021	13/08/2021
Data

Cell Formulas
Range	Formula
E2:E3	E2	=WORKDAY.INTL(D2,2,"1111011")

that is working perfectly as far as functionality but its switching on friday instead of saturday. im not sure of how the last string in the formula works for me to alter it. looks like binary, which i can read, but im not sure how it affects the formula

 

[Fluff]

Not sure what you mean by "it's switching on Friday" the formula will return 6th Aug if you give it 29th Jul & then 13th Aug for 30th Jul

 

[synergy16]

Not sure what you mean by "it's switching on Friday" the formula will return 6th Aug if you give it 29th Jul & then 13th Aug for 30th Jul

im saying that i want it to return Aug 6 up to and including July 30 then return 13 Aug starting Aug 1

 

[Fluff]

But you said

when it becomes july 30, i want it to read "has to be built before Aug 13" for that entire week.

So which is it?

 

[synergy16]

But you said

So which is it?

this one:
im saying that i want it to return Aug 6 up to and including July 30 then return 13 Aug starting Aug 1

i apologize for the conflicting statements

 

[Fluff]

In that case try

=WORKDAY.INTL(D2-1,2,"1111011")