I have the column:
and I want to format it so I can obtain the year and the month separated by a dot like this:
.
The steps were the following:
- I converted the value into a text format: =TEXT($A2; "000000")
- I used left and right functions to separate the year, respectively the month
- I concatenated them all and I put a dot between them
My question is (because I spent some time): isn't it a simpler method to do all this stuff?
CodePudding user response：
You can try:
=TEXT(A2,"0000\.00")
However, this would output text isntead of true dates. Not something you'd really want in a database. I'd also suggest to use:
=DATE(LEFT(A2,4),RIGHT(A2,2),1)
And use custom number formatting 'yyyy.mm` on your column.
CodePudding user response：
Not simpler but it is useful for Excel to know that your date is really a date. A date can be used as a date in formulas, sorting, grouping, filters, etc.
Convert the number into a date using the DATE function.
=DATE(LEFT(A2,4),RIGHT(A2,2),1)
Set a Custom format by right clicking on a date cell, select Format Cell and set a custom format type of:
yyyy.mm
Copy the Custom format to other cells using the Format Painter.
Excel will recognise the cell as a date. You will see the format that you want.
