Convert dd/mm/yyy to mmm-yy

[kostas10]

Hi,

I got a simple one but for some reason confusing.

I have dates in the following format 27/03/2018 and i want it in Mar-19.

The reason for it is i need to average if them, so they need to be the exact same number.

Thank you!

[Sam Capricci]

first off, 27/03/2018 wouldn't be Mar-19 since that is a different year. And that is only a formatting issue to change it from dd/mm/yyyy to mmm-yy.
but if you are wanting it to be simply translated as march 2018 you can use =TEXT(MONTH(A1)&"-"&YEAR(A1),"mmm-yy")

EDIT, caution as the output will be text.

[Sam Capricci]

Now, as I added in the edit, the output of that will be text which might make it difficult to use for averaging.
This will output a date and will make all dates within a month as the first of the month. I use this for doing calculations from as it does return a date and you can format as you want...
=DATEVALUE(MONTH(A1)&"/1/"&YEAR(A1))

EDIT: sorry, for your format in London (mine is US standard) yours might be like this =DATEVALUE("1/"&MONTH(A1)&"-"&YEAR(A1)) though I'm not completely sure but you can adjust as needed.

EDIT2: maybe simply =DATEVALUE(MONTH(A1)&"-"&YEAR(A1))