return the 2 diget year value

G

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
 
T

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)
 
D

Dave Peterson

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

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.
 
E

EngelseBoer

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