2

I have an excel that has a column filled with date objects in this form: 20/12/2016.

I would like to convert them to string values of this form: 20161220, how could I achieve that? enter image description here

enter image description here

jenny
  • 933
  • 2
  • 11
  • 26

2 Answers2

1

Based on this answer, simply:

=TEXT(A1,"YYYYDDMM")

Should do the trick.

Verified on Excel 2010.

However, the letters to use for the day, month, and year are locale-dependent; for instance German is:

=TEXT(A1;"JJJJMMTT")

And Greek is:

=TEXT(A1;"εεεεμμηη")
Community
  • 1
  • 1
Ken Y-N
  • 14,644
  • 21
  • 71
  • 114
  • Thank you for your suggestion. I'm using Excel 2013, the above doesn't work. I tried `=TEXT(A1;"YYYYDDMM")` but it returns YYYYDDMM – jenny Dec 13 '16 at 08:10
  • @jenny are sure these dates are Dates ? (not strings looking like dates) – iDevlop Dec 13 '16 at 08:14
  • @KenY-N Strings as dates will only work if Excel can interpret them as dates. If the MM and DD are in the wrong order for a day higher than 12, Excel won't recognize it as a date. – teylyn Dec 13 '16 at 08:16
  • 2
    @jenny: The format patterns in the `Text` function are locale dependent. With `German` locale it must be `=TEXT(A1;"JJJJMMTT")`. – Axel Richter Dec 13 '16 at 08:18
  • 1
    @Axel I'm using greek locale, so you are right in my case it should be like this: `=TEXT(A1;"εεεεμμηη")` – jenny Dec 13 '16 at 08:24
  • Updated the answer based on your comments, and made it a Community Wiki post. – Ken Y-N Dec 13 '16 at 08:28
  • @Ken Y-N: Should be in English `=TEXT(A1,"YYYYMMDD")` since needed is 20161220 which is 4 digits year 2 digits month 2 digits day. – Axel Richter Dec 13 '16 at 08:30
  • I tried both, only the semicolon works for me. With the comma, I get an error from excel saying there was a problem with formula – jenny Dec 13 '16 at 08:40
  • 1
    @jenny: Whether comma or semicolon is used as delimiter in formulas is also locale dependent. Some locales use semicolon because the comma is decimal separator already. – Axel Richter Dec 13 '16 at 08:42
1

Try this:

=YEAR(A1)&MONTH(A1)&DAY(A1)

This is evaluated by Excel as a text string.

Vitor Barreto
  • 177
  • 2
  • 13
  • Thank you for your answer, but it doesn't work in every case. For a date like this: 20/12/2016 it outputs this: 20161220, but for a date like this: 01/09/2016 it outputs that: 201691 – jenny Dec 13 '16 at 08:34
  • I get the same results – jenny Dec 13 '16 at 08:42
  • 1
    @Vitor Barreto: `=TEXT(YEAR(A1),"0000")&TEXT(MONTH(A1),"00")&TEXT(DAY(A1),"00")` would be the solution which is most locale independent. – Axel Richter Dec 13 '16 at 08:58