| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
return the 2 diget year value
how can i return the yy value of a date please
ie 1996 returns 96 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Return across Row Numeric Values Matching EXACT Month & Year for Criteria | Excel Worksheet Functions | |||
| Excel NPV initial cost in value1 and first year return in value2? | Excel Worksheet Functions | |||
| Need cell formulas to return the day of every Monday in a month based on year entered | Excel Discussion (Misc queries) | |||
| 2 digit year in dates return 19xx not 20xx | Excel Discussion (Misc queries) | |||
| Year-to-date return | Excel Worksheet Functions | |||
Linear Mode
