convert function (from date to year)

[srananmeitie]

I am using CONVERT to change a date (ie. 14-march 1996) to the year (1996). I am using: CONVERT (cellwithdate, "day", "yr").
For the example date above, it returns 1900 as the result.

Can anyone please tell me what I am doing wrong?

thanks!

 

[Zack Barresse]

Why not just use the YEAR function?

=YEAR(cellwithdate)

This does not need the Analysis Toolpak, as CONVERT does.

 

[srananmeitie]

I tried using the Year function and it returns an incorrect year.
For example, A1 = 10/25/1996 (formatted to display as Oct-25-1996)
Year(A1) will result in 1905.

Is there something wrong with how my date is stored? I tried formatting the date as "general", so that it shows a numeric value, but it still returns the wrong year.

 

[Zack Barresse]

It would seem you are not entering it as a true date. The YEAR function shall work when you convert the date to an actual number (Excel recognized date {serial} ). If not, you can use this formula ..

=--RIGHT(SUBSTITUTE(C7,"-","|",2),LEN(C7)-FIND("|",SUBSTITUTE(C7,"-","|",2),1))


HTH

 

[Brian from Maui]

I tried using the Year function and it returns an incorrect year.
For example, A1 = 10/25/1996 (formatted to display as Oct-25-1996)
Year(A1) will result in 1905.

Is there something wrong with how my date is stored? I tried formatting the date as "general", so that it shows a numeric value, but it still returns the wrong year.

When you activate A1, what displays in the formula bar?

 

[srananmeitie]

A1 displays "10/25/1996" in the formula bar.
Originally, the value of A1 comes from a VLOOKUP function, I copied the outcome of that function (paste special: value only).

 

[daniels012]

It doesn't have quotes around it, does it?

 

[srananmeitie]

No it doesnt

 

[Zack Barresse]

Have you tried my formula above? Just change out / for - .

...


=--RIGHT(SUBSTITUTE(C7,"/","|",2),LEN(C7)-FIND("|",SUBSTITUTE(C7,"/","|",2),1))


Note: C7 was just the cell I tested the formula in, you would need to replace every reference to it with the cell of yours in question.