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

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

image


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

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.

Welcome!

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

2 Likes

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.

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

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)")

3 Likes

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 :slight_smile: , 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


soffice.bin_T8MWpRWztn

Function wizard

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.

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)