YearMonth format with '

[#1]

Hi

I need to show a date format where ' is part of the format, and I need to show only the first letter of the Month.

So 2015 January should be 15'J, and both Marts and May is M.  

[fvelascog72]

Hi,

Try:

Date(Date,'YY')&Chr(39)&left(upper(Date(Date,'MMM')),1)

[anat]

how you will differentiate for January ,June and July records,for all these three months output like 15'J ,15'J,15'J.

[robert_mika]

Depends on your data format:

=MID(DATE(Date#('01/01/2015','DD/MM/YYYY'),'DD/MMM/YYYY'),4,1)&Chr(39)&RIGHT(DATE(Date#('01/01/2015','DD/MM/YYYY'),'DD/MMM/YYYY'),2)

[Ralf-Narfeldt]

It seems a bit strange to not be able to differ between March and May.

Date(MyDate, 'YY''M') would give 15'1. You need to put an extra guote to escape.

[#6]

Try this

     =Right('2015',2)&chr(39)&left(Date('2015-01-01','MMM'),1)

[martin_dideriks · Accepted solution]

If you do this, then use a dual to make sure that you will be able to:

1/ Sort the data correctly

2/ Differentiate between January, June and July and March and May

Dual(

  Date(Date,'YY') & chr(39) &Upper(Left(Date(Date,'MMM'),1))

  ,MonthStart(Date)

  )

//Martin

[#8]

Yes indeed that is a problem. It will be used in a chart as dimension so you can see it from the positon relative to the others.

[hic]

If you use the Dual function, you can get them in the right order, so that it is obvious which month the letter refers to. Use

     Dual(Left(Date(Date,'YY''MMM'),4), MonthStart(Date)) as Month

Note that two consecutive single quotes is interpreted as one single quote.

HIC

[#10]

Thx for the "two consecutive" trick. I do get the same result as the version first suggested.