Pad the data from other cells (used via formula) with leading zeroes

[irunohiln]

I couldn’t figure out how to pad the data from other cells (used via formula) with leading zeroes.

any help?

• the boxed cells in right & their used formulae along with the desired outcome are shared here.
• first cell here (right above “00”) is “A5”.
• column A is plain data,
• B6 is formula: =$B$2+A6*7 where B2 is ="2023-8-21" - WEEKDAY(B1, 3)
• C6 is =WEEKNUM(B6)
• E6 is =CONCAT(C6, " (", A6, ")")
• F6 is =CONCAT(MONTH(B6), ".", DAY(B6), "-", DAY(B6+5))
• Obtained data in E6, F6 is 34 (0) , 8.21-26
• Desired is 34 (00) , 08.21-26

---

Version: 7.4.5.1 (x64) / LibreOffice Community
Build ID: 9c0871452b3918c1019dde9bfac75448afc4b57f
CPU threads: 8; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: threaded

[robleyd]

An actual spreadsheet would be of more help so we can see exactly what is in column A and how it is formatted.

For completeness, please add your OS information and LO version from Help | About - use the adjacent icon to copy the version information.

[JohnSUN]

Welcome!

Maybe =CONCAT(C6, " (", TEXT(A6, "00"), ")")?

[irunohiln]

@robleyd

An actual spreadsheet would be of more help so we can see exactly what is in column A and how it is formatted.

similar to how there is “SQL View” for Query Design which shows the source of the relations, is there a source view for spreadsheet formulae?? as there are lots of probs with binary formats while sharing.

[irunohiln]

Unrelated to LO, but related to the topic: Thunderbird 115 (email client) calls this 4 week view as “Multiweek” view in its calendar - nice :

[karolus]

hm:
F6: =TEXT(B6;"MM.DD-") & TEXT(B6+5;"DD")
E6: =TEXT(B6;"WW ") & TEXT(A6;"(00)")
»WW« doesnt pad weeknums below 10
E6: =TEXT(WEEKNUM(B6);"00 ") & TEXT(A6;"(00)")

[irunohiln]

thanks a lot, yes, smth elegant like this is what i wanted.

• i tried with =Text() before, but couldn’t understand it,
• partially because of lack of on-the-fly documentation & clunkiness of the LO formula wizard;
• and partially due to lack of my tenacious efforts.

thanks for including the insufficiency of WW format specifier too , use of & is also nice.

here’s a gif showing before & after (without annotations), the uniform padding makes quick eye-scanning so so much easier

[mariosv]

Function wizard

[robleyd]

This is not a solution to your question - please delete it and add as a comment. This is the Guide…

ODF documents are compressed XML files and generally share well. If you have other experience with ODF files, please elaborate.

[irunohiln]

the problem is it’s not plain source file - plain source files can be instantly glanced on a webpage itself and the problem/solution discussed right on the spot.

blobs require downloading file, selecting a directory, then inspecting that in a gui - even if its super trivial; sharing screenshots, annotating for verbose directions, etc etc etc.

see the Extract Calc Engine as a standalone programming language - #2 by irunohiln for now
i will migrate that over to bugzilla? (ref: When will LibreOffice add a certain feature? - #3 by soon_1900)