return the 2 diget year value
 

how can i return the yy value of a date please

ie 1996 returns 96

return the 2 diget year value
 

more correctly

1997-11-23
returns 97

"EngelseBoer" wrote:

how can i return the yy value of a date please

ie 1996 returns 96

return the 2 diget year value
 

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 - gsnu200804


"EngelseBoer" wrote:

how can i return the yy value of a date please

ie 1996 returns 96

return the 2 diget year value
 

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


"EngelseBoer" wrote:

more correctly

1997-11-23
returns 97

"EngelseBoer" wrote:

how can i return the yy value of a date please

ie 1996 returns 96

return the 2 diget year value
 

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)

--
Biff
Microsoft Excel MVP


"EngelseBoer" wrote in message
...
how can i return the yy value of a date please

ie 1996 returns 96

return the 2 diget year value
 

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




EngelseBoer wrote:

how can i return the yy value of a date please

ie 1996 returns 96

--

Dave Peterson

return the 2 diget year value
 

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.

--
Rick (MVP - Excel)


"EngelseBoer" wrote in message
...
more correctly

1997-11-23
returns 97

"EngelseBoer" wrote:

how can i return the yy value of a date please

ie 1996 returns 96

return the 2 diget year value
 

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



"Dave Peterson" wrote:

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




EngelseBoer wrote:

how can i return the yy value of a date please

ie 1996 returns 96

--

Dave Peterson