return the 2 diget year value

[EngelseBoer]

how can i return the yy value of a date please

ie 1996 returns 96

 

[EngelseBoer]

more correctly

1997-11-23
returns 97

 

[Gary''s Student]

If the cell has a genuine date, then just format it as yy
If the cell has a value like 1996, then in another cell:
=--RIGHT(A1,2) and format as 00

 

[Gary''s Student]

For
1997-11-23 (as a text string) use:
=--MID(A1,3,2) and format as 00

 

[T. Valko]

Assuming the date is a true Excel date:

A1 = 1997-11-23

=MOD(YEAR(A1),100)

Custom format as 00

That returns the year as a numeric value but note that any leading zeros are
for *display purposes only*. If the date in A1 was 2008-9-12 the result will
*display* as 08 but as far as Excel is concerned the result is 8.

If you don't need a numeric value as the result this returns a *text string*
:

=RIGHT(YEAR(A1),2)

 

[Dave Peterson]

Another one:
=text(a1,"yy")
(to return text)
or
=--text(a1,"yy")
(to return a number)

 

[Rick Rothstein]

This will work whether your 1997-11-23 date is a real Excel date or a text
string (it works for either because of the format you used... year, month,
day ordering with dashes between them)...

=TEXT(E1,"yy")

Note that this returns your 2-digit year as a text string (in order to
preserve leading zeroes); if you really want this as a number (for use in
calculation as an example), then use this instead...

=--TEXT(E1,"yy")

but be aware that for 2008 it will return 8 as the response.

 

[EngelseBoer]

thanx everyone
the =text is fine as i have to "paste values" later anyway to clear all
formulae then delimit for further use