[Solved] WEEKNUM + text

[amDEwolff]

Hello,

Spreadsheet (version 4.1.7) contains 52 tabs, one per week.

Function WEEKNUM needs to return year (4 digits), <week> as a written word, week number (2 digits).
I entered =YEAR(C2)&"week" &WEEKNUM(C2; 2) which gives me without spaces: 2024week1

However output should read: 2024 week 1

1) How can I alter the formula so as to add single spaces in between?
2) How to add a leading zero to week number 1 up to week number 9?
3) The formula does work, but does it look crisp and elegant as well (not sure)?

Thanks.

[MrProgrammer]

Function WEEKNUM needs to return year (4 digits), <week> as a written word, week number (2 digits).

WEEKNUM only returns the week number. WEEKNUM cannot provide the year, nor can it add text. To put 2024 week 01 in a cell, you must use the YEAR function and the concatenation operator, as you have done.

I entered =YEAR(C2)&"week" &WEEKNUM(C2; 2) which gives me without spaces: 2024week1.
1) How can I alter the formula so as to add single spaces in between?
2) How to add a leading zero to week number 1 up to week number 9?

=YEAR(C2)&" week "&TEXT(WEEKNUM(C2;2);"00")

3) The formula does work, but does it look crisp and elegant as well (not sure)?

Any crisp and elegant formulas would be of little value if you don't understand how they work. The formula above is what I would use. Parameter 00 tells the TEXT function to format the week number as two digits.

To learn more about the WEEKNUM function see sections E and F in [Tutorial] Calc date formulas. Perhaps you want WEEKNUM_ADD instead of WEEKNUM or you want type 1 instead of type 2. Those sections explain the differences.

If this solved your problem please go to your first post use the Edit ☐ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[amDEwolff]

I had been searching for answers in excel help forums. I would not have figured out placing the bit TEXT the way you did.
Thanks a lot.

[karolus]

Hallo

Code: Select all

=TEXT(C2; "yyyy \w\e\e\k ww")

[MrProgrammer]

=TEXT(C2; "yyyy \w\e\e\k ww")

Number format code WW gives the value of WEEKNUM(date;1) and uses 1 digit for the first nine weeks. Poster amDEwolff asked for WEEKNUM(date;2) and 2-digit numbers for those weeks.

=YEAR("2024-01-07")&" week "&TEXT(WEEKNUM("2024-01-07";2);"00") → 2024 week 01
=TEXT("2024-01-07";"YYYY \w\e\e\k WW")                          → 2024 week 2

[karolus]

=TEXT(C2; "yyyy \w\e\e\k ww")

Number format code WW gives the value of WEEKNUM(date;1) and uses 1 digit for the first nine weeks. Poster amDEwolff asked for WEEKNUM(date;2) and 2-digit numbers for those weeks.

=YEAR("2024-01-07")&" week "&TEXT(WEEKNUM("2024-01-07";2);"00") → 2024 week 01
=TEXT("2024-01-07";"YYYY \w\e\e\k WW")                          → 2024 week 2

I cannot see any difference between (except 1st returns Text and the 2nd returns numeric values:

Code: Select all

=TEXT(A2; "ww" )
=WEEKNUM(A2;2)

with me:

Code: Select all

Version: 7.4.7.2 / LibreOffice Community
Build ID: 40(Build:2)
CPU threads: 4; OS: Linux 6.6; UI render: default; VCL: gtk3
Locale: de-DE (de_DE.UTF-8); UI: de-DE
Debian package version: 4:7.4.7-1+deb12u1
Calc: threaded

weeknum.png (73.93 KiB) Viewed 323 times

[MrProgrammer]

I cannot see any difference between (except 1st returns Text and the 2nd returns numeric values)

That's interesting. They are the same for you. But they are different for me. For both of us =WEEKNUM("2024-01-07";2) is numeric 1. For you, =TEXT("2024-01-07";"WW") is text 1. But for me, =TEXT("2024-01-07";"WW") is text 2 because the result of format code WW depends on the locale! Mine is English (Canada).

How to add a leading zero to week number 1 up to week number 9?

Code WW does not pad single-digit weeks with a leading zero, as other codes like MM and DD do, so WW doesn't seem like a good solution for amDEwolff.