extract year from the date in a cell
 

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: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=523216

extract year from the date in a cell
 

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
"8792".

to get the year in another cell either use

=TEXT(B9,"yyyy")

for a text result or

=YEAR(B9)

for a numeric result or

=B9

and format as "yyyy"

for the spaces try

=TEXT(YEAR(B9),"0 0 0 0")")


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=523216

extract year from the date in a cell
 

hi!

B1: =TEXT(YEAR(A1),"0 0 0 0")

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

-via135

DKY Wrote:
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?


--
via135
------------------------------------------------------------------------
via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://www.excelforum.com/showthread...hreadid=523216

extract year from the date in a cell
 

Try This

=YEAR(B9)
Format/Cell/Custom #" "#" "#" "#

Beege

"DKY" wrote in message
...

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:
http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=523216

extract year from the date in a cell
 

=YEAR(A1) will extract the year form a date in A1
and this will do the formatting
=INT(YEAR(A1)/1000)&" "&INT(MOD(YEAR(A1),1000)/100)&"
"&INT(MOD(YEAR(A1),100)/10)&" "&INT(MOD(YEAR(A1),10))
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"DKY" wrote in message
...

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:
http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=523216

extract year from the date in a cell
 

Date is in A1
B1 =TEXT(YEAR(A1),0)
C1 =LEFT(B1,1)&" "&MID(B1,2,1)&" "&MID(B1,3,1)&" "&RIGHT(B1,1)

--
Carlos

"DKY" wrote in message
...

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:
http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=523216

extract year from the date in a cell
 

=TEXT(YEAR(B9),"0 0 0 0")
works, thanks!!!


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=523216

extract year from the date in a cell
 

=YEAR(A1) will extract the year form a date in A1
and this will do the formatting
=INT(YEAR(A1)/1000)&" "&INT(MOD(YEAR(A1),1000)/100)&"
"&INT(MOD(YEAR(A1),100)/10)&" "&INT(MOD(YEAR(A1),10))
best wishes


--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"DKY" wrote in message
...

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:
http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=523216