Get NEXT Sunday date based on the given date (in [yyyy][mm][dd]) format-CodePudding

In cell C3 the date format is [yyyy][mm][dd], what i would like to do in a single cell is get the date from that cell (which i just take with LEFT, MID and RIGHT functions) and add until sunday ([dd].[mm].[yyyy] format) in the same cell.

On the example i gave, the date is 07.06.2022, so the part i'm missing with function is "- 12.06.2022".

The formula in C2 cell is this: =RIGHT(C3;2)&"."&MID(C3;5;2)&"."&LEFT(C3;4)&" - 12.06.2022"

What i also tried to do is get the real DATE (with function), like this: =DATE(LEFT(C3;4);MID(C3;5;2);RIGHT(C3;2))

But with that function, this would require me to select the cell as "DATE" format and then i'm not sure how to have a whole string (07.06.2022 - 12.06.2022) in that cell, because as soon as i add &" - " to the formula, the cell will not display the date anymore, only numbers (that cell is in "Date" format:

Is there any way to do what i'm trying to do? Bonus would also be, to get Monday to Sunday date (06.06.2022 - 12.06.2022) from the selected date, but i would be happy with getting Sunday " - 12.06.2022" with a function only.

CodePudding user response：

With Excel 365 can try-

=LET(x,DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)),FILTER(SEQUENCE(7,,x,1),WEEKDAY(SEQUENCE(7,,x,1),1)=1))

CodePudding user response：

After playing around a bit with the help of @Harun24hr's formula to get Sunday, this is the final result which i'm happy with:

=LET(Date;DATE(LEFT(A1;4);MID(A1;5;2);RIGHT(A1;2));TEXT(Date-WEEKDAY(Date;2) 1;"dd.mm.yyyy")&" - "&TEXT(FILTER(SEQUENCE(7;;Date;1);WEEKDAY(SEQUENCE(7;;Date;1);1)=1);"dd.mm.yyyy"))

This would get "Monday - Sunday" date.

Taking "20220611" as example, the above function would return: 06.06.2022 - 12.06.2022