software

I have a cell with 01/01/2006 in it, and I'm trying to extract the 2006
but when I put =Left(b9,4) it returns 8718.
I'm also trying to pull the 2006 and put spaces in between it like so
2 0 0 6

Does anyone know how to do these two things?--
DKY
------------------------------------------------------------------------
DKY's Profile: www.excelforum.com/member.php...oamp;userid=14515
View this thread: www.excelforum.com/showthread...hreadid=523216
Excel stores dates as numbers, today is 38792, tomorrow is 38793 etc. so
if you have today's date in B9 and use =left(B9,4) it will give you
quot;8792quot;.

to get the year in another cell either use

=TEXT(B9,quot;yyyyquot;)

for a text result or

=YEAR(B9)

for a numeric result or

=B9

and format as quot;yyyyquot;

for the spaces try

=TEXT(YEAR(B9),quot;0 0 0 0quot;)quot;)--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=523216
hi!

B1: =TEXT(YEAR(A1),quot;0 0 0 0quot;)

assuming that the date 1/1/2006 is in A1

-via135

DKY Wrote:
gt; I have a cell with 01/01/2006 in it, and I'm trying to extract the 2006
gt; but when I put =Left(b9,4) it returns 8718.
gt; I'm also trying to pull the 2006 and put spaces in between it like so
gt; 2 0 0 6
gt;
gt; Does anyone know how to do these two things?--
via135
------------------------------------------------------------------------
via135's Profile: www.excelforum.com/member.php...oamp;userid=26725
View this thread: www.excelforum.com/showthread...hreadid=523216Try This

=YEAR(B9)
Format/Cell/Custom #quot; quot;#quot; quot;#quot; quot;#

Beege

quot;DKYquot; gt; wrote in message
news
gt;
gt; I have a cell with 01/01/2006 in it, and I'm trying to extract the 2006
gt; but when I put =Left(b9,4) it returns 8718.
gt; I'm also trying to pull the 2006 and put spaces in between it like so
gt; 2 0 0 6
gt;
gt; Does anyone know how to do these two things?
gt;
gt;
gt; --
gt; DKY
gt; ------------------------------------------------------------------------
gt; DKY's Profile:
gt; www.excelforum.com/member.php...oamp;userid=14515
gt; View this thread: www.excelforum.com/showthread...hreadid=523216
gt;
=YEAR(A1) will extract the year form a date in A1
and this will do the formatting
=INT(YEAR(A1)/1000)amp;quot; quot;amp;INT(MOD(YEAR(A1),1000)/100)amp;quot;
quot;amp;INT(MOD(YEAR(A1),100)/10)amp;quot; quot;amp;INT(MOD(YEAR(A1),10))
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

quot;DKYquot; gt; wrote in message
news
gt;
gt; I have a cell with 01/01/2006 in it, and I'm trying to extract the 2006
gt; but when I put =Left(b9,4) it returns 8718.
gt; I'm also trying to pull the 2006 and put spaces in between it like so
gt; 2 0 0 6
gt;
gt; Does anyone know how to do these two things?
gt;
gt;
gt; --
gt; DKY
gt; ------------------------------------------------------------------------
gt; DKY's Profile:
gt; www.excelforum.com/member.php...oamp;userid=14515
gt; View this thread: www.excelforum.com/showthread...hreadid=523216
gt;
Date is in A1
B1 =TEXT(YEAR(A1),0)
C1 =LEFT(B1,1)amp;quot; quot;amp;MID(B1,2,1)amp;quot; quot;amp;MID(B1,3,1)amp;quot; quot;amp;RIGHT(B1,1)

--
Carlos

quot;DKYquot; gt; wrote in message
news
gt;
gt; I have a cell with 01/01/2006 in it, and I'm trying to extract the 2006
gt; but when I put =Left(b9,4) it returns 8718.
gt; I'm also trying to pull the 2006 and put spaces in between it like so
gt; 2 0 0 6
gt;
gt; Does anyone know how to do these two things?
gt;
gt;
gt; --
gt; DKY
gt; ------------------------------------------------------------------------
gt; DKY's Profile:
www.excelforum.com/member.php...oamp;userid=14515
gt; View this thread: www.excelforum.com/showthread...hreadid=523216
gt;

=TEXT(YEAR(B9),quot;0 0 0 0quot;)
works, thanks!!!--
DKY
------------------------------------------------------------------------
DKY's Profile: www.excelforum.com/member.php...oamp;userid=14515
View this thread: www.excelforum.com/showthread...hreadid=523216=YEAR(A1) will extract the year form a date in A1
and this will do the formatting
=INT(YEAR(A1)/1000)amp;quot; quot;amp;INT(MOD(YEAR(A1),1000)/100)amp;quot;
quot;amp;INT(MOD(YEAR(A1),100)/10)amp;quot; quot;amp;INT(MOD(YEAR(A1),10))
best wishes--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

quot;DKYquot; gt; wrote in message
news
gt;
gt; I have a cell with 01/01/2006 in it, and I'm trying to extract the 2006
gt; but when I put =Left(b9,4) it returns 8718.
gt; I'm also trying to pull the 2006 and put spaces in between it like so
gt; 2 0 0 6
gt;
gt; Does anyone know how to do these two things?
gt;
gt;
gt; --
gt; DKY
gt; ------------------------------------------------------------------------
gt; DKY's Profile:
gt; www.excelforum.com/member.php...oamp;userid=14515
gt; View this thread: www.excelforum.com/showthread...hreadid=523216
gt;