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?
>Solution :
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"))
