extract year from the date in a cell

D

DKY

I have a cell with 01/01/2006 in it, and I'm trying to extract the 200
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
 
D

daddylonglegs

Excel stores dates as numbers, today is 38792, tomorrow is 38793 etc. s
if you have today's date in B9 and use =left(B9,4) it will give yo
"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")"
 
B

Bernard Liengme

=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
 
C

CarlosAntenna

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

Bernard Liengme

=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
 
Top