none
Excel formula issue - Problème de formule Excel

    Question

  • Hello,

    I am currently using Excel 2010. ( In French) But will do my best to swap the terms to english.

    The colomn J is set to give me the YEAR of the date set in K by a formula.

    J13 =SI(K13>1950;ANNEE(K13);" ") Cell value = 2016

    K13 =SI(I13+1825>1904;I13+1825;" ") Cell value = 2016-02-22

    I13 =2011-02-23

    J14 =SI(K14>1950;ANNEE(K14);" ") Cell value = #VALUE

    K14 =SI(I14+1825>1904;I14+1825;" ") Cell value = " "

    I14 =  " "

    The formula in colomn K works perfectly. When the cells in the column I are empty, the formula in column K gives me " " or BLANK

    I used the same type of formula for column J for the YEAR in order to use the conditional formating I needed. And now the BLANK cells (" ") in column K give me #Value in column J. If I put "2013" instead of " " or anything else for that matter the result is the same. Its as if the FALSE part of the IF formula is not working in that column!

    Basically what I need is for column J to give me the YEAR when there is a date and give me a blank cell when there is no value " " in K. in the case of my formula >1950 = TRUE ( Which will give me the YEAR)  FALSE = " " or BLANK 

    If you could help me resolve this issue I would be extremely greatful!

    Regards,

    Bonjour,

    J'utilise Excel 2010. Vous noterez que dans la colone J j'ai l'ANNEE de la date de la colone K.

    La formule de ma colone K fonctionne tres bien. Quand les cellules de la colone I sont vides, la formule de la colone K done " " donc rien. (plutot que de faire BLANK)

    J'ai effectue le meme type de formule avec la colone J pour l'annee afin de pouvoir mettre la mise en forme conditionel que j'utilise pour la colone J et maintenant les cellules vides du K donne #valeur dans la colone J. Si j'inscrit "2013" à la place de " " sa ne fonctionne toujours pas. Donc j'en deduis que le FAUX de la formule SI n'est plus fonctionel pour la colone J... Pourquoi le faux serait fonctionel dans K mais pas dans J?

    Bref j'aurais besoin que la colone J me donne l'annee de la date retrouve dans la colone K. Mais si la colone K est vide ou donc >1950, la J devrais donc etre vide aussi. Vrai = AAAA Faux = " "

    Si vous etes en mesure de m'aider avec ce petit probleme sa serait tres apprecie!

    Merci encore pour l'aide!



    Tuesday, December 10, 2013 6:52 PM

Answers

  • Try

    =SI(K14=" ";" ";SI(K14>1950;ANNEE(K14);" "))

    or

    =SIERREUR(SI(K14>1950;ANNEE(K14);" ");" ")


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, December 10, 2013 9:53 PM
  • Hi,

    well the first one makes my Years disapear so half of the formula is working. When it needs to be BLANK it actually is, which is awesome but it just doesnt do the ANNEE like it should.

    I ended up editing yours a slight bit to

     =SI(K14=" ";" ";SI K14>1950;ANNEE(K14)))

    I basically just took the extra " " at the end which made the formula no longer work!

    Thanks alot Hans!


    Wednesday, December 11, 2013 5:05 PM

All replies

  • Try

    =SI(K14=" ";" ";SI(K14>1950;ANNEE(K14);" "))

    or

    =SIERREUR(SI(K14>1950;ANNEE(K14);" ");" ")


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, December 10, 2013 9:53 PM
  • Hi,

    well the first one makes my Years disapear so half of the formula is working. When it needs to be BLANK it actually is, which is awesome but it just doesnt do the ANNEE like it should.

    I ended up editing yours a slight bit to

     =SI(K14=" ";" ";SI K14>1950;ANNEE(K14)))

    I basically just took the extra " " at the end which made the formula no longer work!

    Thanks alot Hans!


    Wednesday, December 11, 2013 5:05 PM