zabouti |||

Google Sheet Number Formatting

Google Sheets has fairly good formatting for displaying numbers and dates.

But I often use Google Sheets to create data for exporting to TSV files (for importing into markdown files).

Apparently I do it the wrong way because I often try to turn calculate dates that can be formatted. In the past, I tried to do lots of things, as illustrated in this table:

date-n year month day y-m-d month (calc) day (calc) y-m-d (pays no attention to leading zeros) date-n (formatted)
45136 2023 7 29 2023-7-29 07 29 2023-7-29 2023-07-29
45137 2023 7 30 2023-7-30 07 30 2023-7-30 2023-07-30
45138 2023 7 31 2023-7-31 07 31 2023-7-31 2023-07-31
45139 2023 8 1 2023-8-1 08 01 2023-8-1 2023-08-01

Here are the calculations used to make each column:

column value
date-n 45136
year =YEAR(A2)
month =MONTH(A2)
day =DAY(A2)
y-m-d =CONCATENATE(B2,“-”,C2,“-”,D2)
month (calc) =MONTH(A2)
day (calc) =DAY(A2)
y-m-d (pays no attention to leading zeros) =CONCATENATE(B2,“-”,F2,“-”,G2)
date-n (formatted) 2023-07-29

The thing that annoys the Hell out of me is that column y-m-d (pays no attention to leading zeros) uses the values in the month (calc) and day (calc) columns but loses the leading zeros. This drives me crazy.

However, much to my surprise, I recently tried making the date-n (formatted) column, which I formatted using the following method, works!

I’ve also figured out how to do it with some truly weird calculations.

This table:

is formatted with this crazy calculation:

and it works, too!

Here’s the calculation:


To add a leading zero to a number, I concatenate the string 0” (not a number) to the number (itself converted to text) and then chop off everything but the two righmost digits. Ie works.

Up next Summer Brake A friend of mine (not a native speaker of English) (not that native speakers or even spelling correctors know any better) sent out this message: « MoveOn wants to amend the Constitution? Ben & Jerry used their platform to encourage everyone to mark their money with a rubber stamp: This is a terrible idea! Can you just imagine what a
Latest posts Vocabulary lesson Iris and I had an interesting discussion with Google Bard this morning. Discussion on the Pomona College class of 1964 listserv I’m illiterate Elon Musk I love photographing the artists at Wildacres Eating a Mouse It Really is a Fascist Flag (at least to some Americans) I love the New York Times Metropolitan Diary Watching The Ten Commandments in Gafsa, Tunisia, in the sixtes I wanna be like these guys when I’m their age! The insignificance of photography Enough photos from our trip to Japan MoveOn wants to amend the Constitution? Google Sheet Number Formatting Summer Brake Romantic technologies and ideologies PITY THE NATION Basho’s poem, Even in Kyoto War in Ukraine - an explanation Timothy Snyder’s History of Ukraine Fake Email Senders More on language learning Effect and Cause Soros is my Co-Pilot Note à une chère amie Arnold Schwarzenegger Why Purim upsets me I was a Programmer Who Swore Constantly I perceive new things as my nerves slow down