Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Something like this?
Or Simply you can do as below..
=Only({<ColumnA = {'$(=Max(ColumnA))'}>}ColumnB)
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.
Try this
=FirstSortedValue(ColumnB,-ColumnA)
Set the date to your desired output with:
=date(columnA, 'MMM')&date(ColumnA, chr(39)&'YY')
Something like this?
Or Simply you can do as below..
=Only({<ColumnA = {'$(=Max(ColumnA))'}>}ColumnB)
Cool !!!!!!!!!! Many thanks Manish, it works now !!!!!!!!!!