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

  1.
    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
    1. CocoPL
       
      Old answer, but still useful (if you don't care about managing the date format).
      Thank you very much.
      0
  2. 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
    1. 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
  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
    1. Kirilov Posted messages 108 Status Member 1
       
      Hello,
      That works! Thank you very much.
      0