| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date formula to return two digit year
I'm trying to perfect a formula that produces a two digit year from another
cell that contains a full date, for example: Y12 contains the following formatted as a date: 07-10-2008 In Y13 my formula is: =TEXT(YEAR(Y12),"##") No matter what I try I get: 2008 as a result rather than the intended: 08 I'm sure it is obvious, but can someone please assist. Thanks much in advance! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date formula to return two digit year
=TEXT(Y12,"yy")
"XP" wrote: I'm trying to perfect a formula that produces a two digit year from another cell that contains a full date, for example: Y12 contains the following formatted as a date: 07-10-2008 In Y13 my formula is: =TEXT(YEAR(Y12),"##") No matter what I try I get: 2008 as a result rather than the intended: 08 I'm sure it is obvious, but can someone please assist. Thanks much in advance! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date formula to return two digit year
=TEXT(YEAR(Y12),"YY")
XP wrote: I'm trying to perfect a formula that produces a two digit year from another cell that contains a full date, for example: Y12 contains the following formatted as a date: 07-10-2008 In Y13 my formula is: =TEXT(YEAR(Y12),"##") No matter what I try I get: 2008 as a result rather than the intended: 08 I'm sure it is obvious, but can someone please assist. Thanks much in advance! -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date formula to return two digit year
Maybe this:
=TEXT(Y13,"yy") HTH, Paul -- "XP" wrote in message ... I'm trying to perfect a formula that produces a two digit year from another cell that contains a full date, for example: Y12 contains the following formatted as a date: 07-10-2008 In Y13 my formula is: =TEXT(YEAR(Y12),"##") No matter what I try I get: 2008 as a result rather than the intended: 08 I'm sure it is obvious, but can someone please assist. Thanks much in advance! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date formula to return two digit year
Would =TEXT(Y12,"yy") work for you?
Gord Dibben MS Excel MVP On Thu, 10 Jul 2008 08:11:03 -0700, XP wrote: I'm trying to perfect a formula that produces a two digit year from another cell that contains a full date, for example: Y12 contains the following formatted as a date: 07-10-2008 In Y13 my formula is: =TEXT(YEAR(Y12),"##") No matter what I try I get: 2008 as a result rather than the intended: 08 I'm sure it is obvious, but can someone please assist. Thanks much in advance! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date formula to return two digit year
Ignore this response.
I didn't notice the year() portion. Dave Peterson wrote: =TEXT(YEAR(Y12),"YY") XP wrote: I'm trying to perfect a formula that produces a two digit year from another cell that contains a full date, for example: Y12 contains the following formatted as a date: 07-10-2008 In Y13 my formula is: =TEXT(YEAR(Y12),"##") No matter what I try I get: 2008 as a result rather than the intended: 08 I'm sure it is obvious, but can someone please assist. Thanks much in advance! -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date formula to return two digit year
Yeah, thanks.
It was a case of my adding too much! When I remove the "Year" portion, it works. Thanks again. "Gord Dibben" wrote: Would =TEXT(Y12,"yy") work for you? Gord Dibben MS Excel MVP On Thu, 10 Jul 2008 08:11:03 -0700, XP wrote: I'm trying to perfect a formula that produces a two digit year from another cell that contains a full date, for example: Y12 contains the following formatted as a date: 07-10-2008 In Y13 my formula is: =TEXT(YEAR(Y12),"##") No matter what I try I get: 2008 as a result rather than the intended: 08 I'm sure it is obvious, but can someone please assist. Thanks much in advance! |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date formula to return two digit year
You have your answer (several times now), but I just wanted to mention that
when you use a # or 0 symbol in the integer part of a string pattern, that does *not* restrict the number of digits in the integer part of the number being processed, it only differentiates whether leading zeroes will be printed or not. This is different from the decimal part of a floating point number where the number of # or 0 symbols in the string pattern does truncate (actually, round) the number of decimal places printed out. So, whether you used "#", "##", "###", etc. in the integer part of the pattern string, you would always have gotten 2008 printed out. As the other responses showed, using the date part meta-characters in the pattern string allow you to restrict the part of the date printed out. Rick "XP" wrote in message ... I'm trying to perfect a formula that produces a two digit year from another cell that contains a full date, for example: Y12 contains the following formatted as a date: 07-10-2008 In Y13 my formula is: =TEXT(YEAR(Y12),"##") No matter what I try I get: 2008 as a result rather than the intended: 08 I'm sure it is obvious, but can someone please assist. Thanks much in advance! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Return Month & Year of Date Value | Excel Discussion (Misc queries) | |||
| Addendum to 2 digit year to 4 digit year | Excel Discussion (Misc queries) | |||
| 2 digit year in dates return 19xx not 20xx | Excel Discussion (Misc queries) | |||
| Year-to-date return | Excel Worksheet Functions | |||
| Date formula: return Quarter and Fiscal Year of a date | Excel Discussion (Misc queries) | |||
Linear Mode
