View original (French)

Function =Line with Dates

Solved
Kirilov Posted messages 108 Status Member -  
 CocoPL -
Hello,
For some reason I don't understand, Excel transforms a string when I use the formula =RIGHT(A1,4)!
Let me explain: I have a column of dates in the formats DD/MM/YYYY or DD/YYYY from which I want to extract the year. To keep it simple, I'm using =RIGHT to extract the last 4 characters, but for example, the date 01/04/1972 becomes 6420!?
Is there a way to prevent the program from trying to interpret the characters and simply display the last 4 characters?

Thank you!

Configuration: Windows XP / Chrome 21.0.1180.89

3 answers

Answer 1 / 3
landrin Posted messages 11 Registration date   Status Member Last intervention   4
 
Hello,

This is a format issue.
Select your column and replace / with a .
Instead of having 01/04, you will have 01.04

I hope this will be satisfactory for you.

Best regards

--
https://excelfacile.jimdofree.com/
3
CocoPL
 
Old answer, but still useful (if you don't care about managing the date format).
Thank you very much.
0
Answer 2 / 3
eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
 
Hello,

In Excel, a date is a number (displayed in date format).
In this case, 01/04/1972 = 26390.

Instead, use =YEAR(A1)

Eric

--
You will never reply to an unsolicited private message...
Well, that's done.
0
Kirilov Posted messages 108 Status Member 1
 
Hello Eriiic,
I hadn't thought of that... It works very well of course, but the problem is that I don't have uniform formats in the original column, and so if I use, for example, =YEAR(A1) on a cell containing just 1972, the formula returns "1905"...
0
Answer 3 / 3
Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
 
Hello

maybe a trick:
=IF(COUNTA(A1)=4,A1,YEAR(A1))
best regards

aren't we the only ones to understand what we are explaining?
0
Kirilov Posted messages 108 Status Member 1
 
Hello,
That works! Thank you very much.
0