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:
=CONCATENATE(YEAR(A2),“-”,RIGHT(CONCATENATE(“0”,TO_TEXT(MONTH(A2))),2),“-”,RIGHT(CONCATENATE(“0”,TO_TEXT(DAY(A2))),2))
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.