I have an Excel spreadsheet in which the date of certain events is stored in three columns: year, month, and day.
| Year | Month | Day |
|---|---|---|
| 1734 | 04 | 08 |
| 1750 | 11 | 10 |
I set the format of the cell to 'custom' so as to show the leading '0' which I want to maintain. I now want to concatenate the information in these cells so that it shows the date as follows YYYY-MM-DD. I'm using this formulate to achieve that:
=CONCAT(B2, "-", C2, "-", D2)
Annoyingly, however, it doesn't maintain the leading zero (the result is: 1734-4-8 and 1750-11-10, respectively). Does anyone know how to concatenate the data from these cells while maintaining the leading zero?
CodePudding user response：
Cell formatting only changes appearance, but doesn't alter value. Even if it looks like 04 it still is 4 as value.
Use TEXT:
=CONCAT(B2,"-",TEXT(C2,"00"),"-",TEXT(D2,"00"))
or
=TEXTJOIN("-",,B2,TEXT(C2,"00"),TEXT(D2,"00"))
