Hi....a word to the wise....please read this article and respond accordingly...you will be amazed how many tried and tested answers you get....it helps us help you.

https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

from  https://stackoverflow.com/questions/18920393/sql-server-get-next-relative-day-of-week-next-monday-tuesday-wed

DECLARE @NextDayID INT = 4 -- 0=Mon, 1=Tue, 2 = Wed, ..., 5=Sat, 6=Sun
SELECT CONVERT(varchar(8),DATEADD(DAY, (DATEDIFF(DAY, @NextDayID, GETDATE()) / 7) *...

Dont really need to go far this summer !......all this within 5 mins
🙂🙂

like this ??  ...still need expected results

SET DATEFORMAT DMY;
CREATE TABLE #TestTable
(IDHAAndD  VARCHAR(50),
IDClient  VARCHAR(50),
IDStay   VARCHAR(50),
LocationName VARCHAR(50),
...

me thinks it would be useful if you could please post your expected results.....will save any misunderstanding on your explanation.
Also....are you really storing dates as varchar?
Also....what defines the...

you havent provided your expected results in the spreadsheet.......please clarify by reposting with expected results column