# split or create a funtion

Copper Contributor

# split or create a funtion

hello everyone, i have a problem to put automaticaly range of daily works, and that is slowly to me

So that I want to create a function or have help how to do it.

like in semaine 53  = I want to that equals to 02/09/24-10/10/24 without describing all the words

@sana

13 Replies

# Re: split or create a funtion

Above all date range has 6 days difference but for 53 you different logic. If logic remain same than you can use the below formula.

=LET(dt,RIGHT(M7,8),TEXT(dt+1,"dd/MM/YY")&"-"&TEXT(dt+7,"dd/mm/yy"))

# Re: split or create a funtion

=LET(Sep_1, DATE(YEAR(TODAY())-(MONTH(TODAY())<9), 9, 1), Wk, TEXTAFTER(A2, " "), Dt, Sep_1+7*Wk, Mon, Dt-WEEKDAY(Dt, 3), Sun, Mon+6, TEXT(Mon, "dd/mm/yy")&"-"&TEXT(Sun, "dd/mm/yy"))

=LET(Sep_1; DATE(ANNEE(AUJOURDHUI())-(MOIS(AUJOURDHUI())<9); 9; 1); Wk; TEXTE.APRES(A2; " "); Dt; Sep_1+7*Wk; Mon; Dt-JOURSEM(Dt; 3); Sun; Mon+6; TEXTE(Mon; "jj/mm/aa")&"-"&TEXTE(Sun; "jj/mm/aa"))

# Re: split or create a funtion

I'm not sure if this is what is meant, but here is a simple example in the attached file that should work in all versions of Excel, since there is no information about the version.

My answers are voluntary and without guarantee!

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

# Re: split or create a funtion

This way it doesn't work

# Re: split or create a funtion

It's very strange, it works for me. I'm currently using Excel 2016.

# Re: split or create a funtion

thank you for your help and formulas

# Re: split or create a funtion

Hi Niko, your formula probably depends on your system date settings. It returns #VALUE! on my computer too.

# Re: split or create a funtion

That's right, now I've checked and the German formula =(1&-B3)+A3*7-REST(2&-1&-B3;7)-4 cannot be automatically translated into English by Excel, it doesn't accept.
The formula may be trying to perform arithmetic on concatenated strings, which could be problematic when (not sure, but could be most likely). Here is a revised approach in English, it should actually work, even i dont test it  =VALUE(1 & -B2) + A2 * 7 - MOD(VALUE(2 & -1 & -B2), 7) - 4

Thanks both of you for the tip

# Re: split or create a funtion

@NikolinoDE that is a clever way to do it. Maybe use the ISO standard for the date format then it may be more universal (year-month-day):
=(B2&-1&-1)+A2*7-MOD(B2&-2&-1,7)-4
@HansVogelaar and @SergeiBaklan can you confirm if this version works for you too?

# Re: split or create a funtion

Yes, ISO always works

Another comment, that doesn't answer on initial question. Based on screenshot week numbers are not for calendar year (for financial year ?) . In this year week #52 is the last week in August, week #53 is first week in Sep. I'm not sure how first week starts - with any day in the first month, or with only any work day in the first month, or something else.

# Re: split or create a funtion

i test it, but it doesn t go well, it says me ERROR @Tejas_shah

# Re: split or create a funtion

you have 2 = signs. remove 1 of the "="

# Re: split or create a funtion

As variant

``=TEXTJOIN("-",,TEXT(TEXTSPLIT(K55,"-")+7,"dd/mm/yy"))``