18 Replies Latest reply: Nov 28, 2013 4:37 AM by Sokkorn Cheav

# 2 digit year field how to sort from hi to lo ?

Hi All

I am using below code to get 2 digit for year :-

Right(year(date),2)           as [year_],

I am not able to sort the year in sequences from hi to lo

Any work around ?

PAul

• ###### Re: 2 digit year field how to sort from hi to lo ?

Simply because year is now traited as Text o String format. Format your year using Date() function to retain it as Year. See line of script below:

Date(date,'YY') as [year_]

It is very important to keep year in it's original format. Hope it helps.

Thanks,

Bill

• ###### Re: Re: 2 digit year field how to sort from hi to lo ?

Hi Bill

Hi Sir

I use

Date(date,'YY') as [year_]

i get duplicated year. any work around ?

• ###### Re: 2 digit year field how to sort from hi to lo ?

hi paul may i know why you use like that

• ###### Re: Re: 2 digit year field how to sort from hi to lo ?

Hi Nir

I am lazy to write code for year field :-

=

IF(year='2014','14',

IF(year='2013','13',

IF(year= '1997' ,'97',

IF(year= '1998' ,'98',

IF(year= '1999' ,'99',

IF(year= '2000' ,'00',

IF(year= '2001' ,'01',

IF(year= '2002' ,'02',

IF(year= '2003' ,'03',

IF(year= '2004' ,'04',

IF(year= '2005' ,'05',

IF(year= '2006' ,'06',

IF(year= '2007' ,'07',

IF(year= '2008' ,'08',

IF(year= '2009' ,'09',

IF(year= '2011' ,'11',

IF(year= '2012' ,'12',

IF(year= '2010','10'))))))))))))))))))

• ###### Re: 2 digit year field how to sort from hi to lo ?

why dont you use right(year(date),2) as Year

• ###### Re: 2 digit year field how to sort from hi to lo ?

Paul,

• ###### Re: 2 digit year field how to sort from hi to lo ?

Hi Paul,

Right(year(date),2)           as [year_], -- Right() will give you the result in string

you use this

Date(date, 'YY') as [year_] this will give you the year in date (numeric)

Hope this helps you.

Regards,

Jagan.

• ###### Re: Re: 2 digit year field how to sort from hi to lo ?

Hi Jagan

if i use :-

Date(date, 'YY') as [year_] this will give you the year in date (numeric)

My year field unable to sort like below :-

98 99 10 11

• ###### Re: 2 digit year field how to sort from hi to lo ?

Try:

=Num(Mod(Year(date),100),'00')

You can try it in he script as well.

PFA

• ###### Re: Re: 2 digit year field how to sort from hi to lo ?

Hi Tres

Notice that it not able to sort . i need it :-

96  99 00 11 12

• ###### Re: 2 digit year field how to sort from hi to lo ?

In the sort tab->Expression

=Year

• ###### Re: Re: 2 digit year field how to sort from hi to lo ?

Hi tres

I add sort expression as you said. but still not sort as my wish.

• ###### Re: 2 digit year field how to sort from hi to lo ?

I see it fine !

• ###### Re: 2 digit year field how to sort from hi to lo ?

Paul

You can create a dual for this, and sort it numerically, like this.

Dual(Right(Year(date), 2), YearStart(date))

HTH

Jonathan

• ###### Re: 2 digit year field how to sort from hi to lo ?

Hi All

Many thank. Now i found more then 3 people provide the right solution. I must said Jona get it right first.

• ###### Re: 2 digit year field how to sort from hi to lo ?

Once you sort it by 'Year' field , it works like expected .

My Advice would be rather than writing a big "IF" expression in the front end (In the list Year list box), it would be great if would get this done from the Backend Script to have a better performance .

Regards,

Karthick V

• ###### Re: 2 digit year field how to sort from hi to lo ?

Mr. Paul,

I know this post closed already. Just want to add new idea with below script for simple function and better performance

Date(YearStart([DateField]),'YY')     AS [Years]

Above function will sort order automatically.

Regards,

Sokkorn