Formula to find the last month

[danialier]

Hello,
I have an excel with two columns as follows:

ColumnA          ColumnB

201401               Jan'14

201402               Feb'14

201403               Mar'14

201404               Apr'14

Then in QV I have a Text Box that I want to display the last month, so in this case I want the Text Box to show "Apr'14"

I know how to display "201404", this is just entering the formula: =max(ColumnA)

However, my question is: how I can make sure that the Text box shows "Apr'14" instead of "201404" ?

Thanks,

dani

[Jason_Michaelides]

Date(MakeDate(Left(Max(ColumnA),4),Right(Max(ColumnA),2)),'MMM YY')

If you absolutely have to have the apostrophe in there then try:

Replace(Date(MakeDate(Left(Max(ColumnA),4),Right(Max(ColumnA),2)),'MMM YY'),' ',Chr(39))

Hope this helps,

Jason

EDIT: Added Max() in.

[er_mohit]

Try this

=FirstSortedValue(ColumnB,-ColumnA)

[Michiel_QV_Fan]

Set the date to your desired output with:

=date(columnA, 'MMM')&date(ColumnA, chr(39)&'YY')

[MK_QSL · Accepted solution]

Something like this?

Or Simply you can do as below..

=Only({<ColumnA = {'$(=Max(ColumnA))'}>}ColumnB)

[danialier]

Cool !!!!!!!!!! Many thanks Manish, it works now !!!!!!!!!!