GESTION DES DATES

Sur cette page, je vais vous développer un bon nombre de formule pour que vous puissiez gérer au mieux toutes les hypothèses de manipulation des dates.

Allons-y !!!

* Afficher le numéro du JOUR d'une date (de 1 à 31) :
   En A1 : 06/10/2015
   Formule en B1 :   =JOUR(A1)
   Résultat : 6
    Attention la cellule du résultat doit être au format Standard 


* Afficher le NOM du JOUR de la semaine en fonction de son numéro (de 1 à 7) :
   En A1 : 1
   Formule en B1 :   =TEXTE(MOD(A1;7);"jjjj")
   Résultat : dimanche
    Attention la cellule du résultat doit être au format Standard 

   Donc par déduction :
   dimanche = 1
   lundi = 2
   mardi = 3
   mercredi = 4
   jeudi = 5
   vendredi = 6
   samedi = 7
     

* Afficher le numéro du MOIS (de 1 à 12) correspondant à une date donnée :
   En A1 : 04/12/2015
   Formule en B1 :   =TEXTE(A1;"mm")*1
    Résultat : 12 
    Attention la cellule du résultat doit être au format Standard



* Afficher le numéro du TRIMESTRE (de 1 à 4) correspondant à une date donnée :
   En A1 : 04/06/2015
   Formule en B1 :   =ENT((MOIS(A1)+2)/3)
   Résultat : 2
    Attention la cellule du résultat doit être au format Standard 


* Afficher le numéro du TRIMESTRE en cours :
   Formule en A1 :   =PLAFOND(MOIS(AUJOURDHUI())/3;1)
   Résultat : 2
    Attention la cellule du résultat doit être au format Standard


* Retrouver le NUMERO du JOUR de l'année correspondant à une date donnée :
   En A1 : 04/06/2015
   Formule en B1 :   =A1-DATE(ANNEE(A1);1;0)
   Résultat : 155
    Attention la cellule du résultat doit être au format Standard


* Afficher le DERNIER jour du mois courant :
   Formule en A1 :   =DATE(ANNEE(AUJOURDHUI());MOIS(AUJOURDHUI())+1;0)
   Résultat : 30/06/2015 => si nous sommes au mois de juin 2015
   
    ou :
    En A1 : 04/06/2015
    Formule en B1 :   =DATE(ANNEE(A1);MOIS(A1)+1;0)
    Résultat : mardi 30 juin 2015  => avec format cellule personnalisé => Type jjjj jj mmmm aaaa

    ou suivant année et mois dans cellules différentes  :
    En A1 : 2015
    En B1 : 6
    Formule en C1 :   =DATE(A1;B1+1;0)
    Résultat : mardi 30 juin 2015  => avec format cellule personnalisé => Type jjjj jj mmmm aaaa


* Afficher le PREMIER jour du mois courant
   Formule en A1 :   =FIN.MOIS(AUJOURDHUI();-1)+1
   Résultat : 01/06/2015  => si nous sommes au mois de juin 2015

    ou :
    En A1 : 04/06/2015
    Formule en B1 :   =DATE(ANNEE(A1);MOIS(A1);1)
    Résultat : lundi 01 juin 2015  => avec format cellule personnalisé => Type jjjj jj mmmm aaaa

    ou suivant année et mois dans cellules différentes  :
    En A1 : 2015
    En B1 : 6
    Formule en C1 :   =DATE(A1;B1;1)
    Résultat : lundi 01 juin 2015  => avec format cellule personnalisé => Type jjjj jj mmmm aaaa


* Afficher le premier LUNDI d'une année donnée :
   En A1 : 2015
   Formule en B1 :   =DATE(A1;1;1)+SI(2<JOURSEM(DATE(A1;1;1));7-JOURSEM(DATE(A1;1;1))+2;2-JOURSEM(DATE(A1;1;1)))
   Résultat : 05/01/2015


* Afficher le PREMIER  LUNDI du mois du date donnée :
   En A1 : 15/06/2015
   Formule en B1 :   =TEXTE(A1;"m/aaa")-MOD(TEXTE(A1;"m/aaa")-4;7)+5
   Résultat : 01/06/2015
   Donc en modifiant cette formule vous pouvez trouver :
   * 1er Mardi       => = TEXTE(A1;"m/aaa")-MOD(TEXTE(A1;"m/aaa")-5;7)+5
   * 1er Mercredi  => = TEXTE(A1;"m/aaa")-MOD(TEXTE(A1;"m/aaa")-6;7)+5
   * 1er Jeudi        => = TEXTE(A1;"m/aaa")-MOD(TEXTE(A1;"m/aaa")-7;7)+5
   * 1er Vendredi   => = TEXTE(A1;"m/aaa")-MOD(TEXTE(A1;"m/aaa")-1;7)+5
   * 1er Samedi     => = TEXTE(A1;"m/aaa")-MOD(TEXTE(A1;"m/aaa")-2;7)+5
   * 1er Dimanche => = TEXTE(A1;"m/aaa")-MOD(TEXTE(A1;"m/aaa")-3;7)+5

   * 2ème Lundi       => = TEXTE(A1;"m/aaa")-MOD(TEXTE(A1;"m/aaa")-4;7)+12
   * 2ème Mardi       => = TEXTE(A1;"m/aaa")-MOD(TEXTE(A1;"m/aaa")-5;7)+12
   * 2ème Mercredi  => = TEXTE(A1;"m/aaa")-MOD(TEXTE(A1;"m/aaa")-6;7)+12
   * 2ème Jeudi        => = TEXTE(A1;"m/aaa")-MOD(TEXTE(A1;"m/aaa")-7;7)+12
   * 2ème Vendredi   => = TEXTE(A1;"m/aaa")-MOD(TEXTE(A1;"m/aaa")-1;7)+12
   * 2ème Samedi     => = TEXTE(A1;"m/aaa")-MOD(TEXTE(A1;"m/aaa")-2;7)+12
   * 2ème Dimanche => = TEXTE(A1;"m/aaa")-MOD(TEXTE(A1;"m/aaa")-3;7)+12

   * 3ème Lundi       => = TEXTE(A1;"m/aaa")-MOD(TEXTE(A1;"m/aaa")-4;7)+19
   * 3ème Mardi       => = TEXTE(A1;"m/aaa")-MOD(TEXTE(A1;"m/aaa")-5;7)+19
   * 3ème Mercredi  => = TEXTE(A1;"m/aaa")-MOD(TEXTE(A1;"m/aaa")-6;7)+19
   * 3ème Jeudi        => = TEXTE(A1;"m/aaa")-MOD(TEXTE(A1;"m/aaa")-7;7)+19
   * 3ème Vendredi   => = TEXTE(A1;"m/aaa")-MOD(TEXTE(A1;"m/aaa")-1;7)+19
   * 3ème Samedi     => = TEXTE(A1;"m/aaa")-MOD(TEXTE(A1;"m/aaa")-2;7)+19
   * 3ème Dimanche => = TEXTE(A1;"m/aaa")-MOD(TEXTE(A1;"m/aaa")-3;7)+19

   * 4ème Lundi       => = TEXTE(A1;"m/aaa")-MOD(TEXTE(A1;"m/aaa")-4;7)+26
   * 4ème Mardi       => = TEXTE(A1;"m/aaa")-MOD(TEXTE(A1;"m/aaa")-5;7)+26
   * 4ème Mercredi  => = TEXTE(A1;"m/aaa")-MOD(TEXTE(A1;"m/aaa")-6;7)+26
   * 4ème Jeudi        => = TEXTE(A1;"m/aaa")-MOD(TEXTE(A1;"m/aaa")-7;7)+26
   * 4ème Vendredi   => = TEXTE(A1;"m/aaa")-MOD(TEXTE(A1;"m/aaa")-1;7)+26
   * 4ème Samedi     => = TEXTE(A1;"m/aaa")-MOD(TEXTE(A1;"m/aaa")-2;7)+26
   * 4ème Dimanche => = TEXTE(A1;"m/aaa")-MOD(TEXTE(A1;"m/aaa")-3;7)+26


* Calculer le NOMBRE de LUNDI de lundi à partir de l'ANNEE et du N° DU MOIS :
    En A1 : 2015
    En B1 : 6
    Formule en C1 :   =SOMME((JOURSEM(DATE(A1;B1;LIGNE(INDIRECT("1:"&JOUR(DATE(A1;B1+1;0))))))=2)*1)
    Attention formule matricielle : Validez la formule par les touches CTRL + MAJ + ENTREE (vous devez avoir des parenthèses en début et fin de formule {..........}  )
   Résultat : 5

   Donc en modifiant cette formule vous pouvez trouver le nombre de :
   * Mardi => =SOMME((JOURSEM(DATE(A1;B1;LIGNE(INDIRECT("1:"&JOUR(DATE(A1;B1+1;0))))))=3)*1)
  * Mercredi => =SOMME((JOURSEM(DATE(A1;B1;LIGNE(INDIRECT("1:"&JOUR(DATE(A1;B1+1;0))))))=4)*1)
  * Jeudi => =SOMME((JOURSEM(DATE(A1;B1;LIGNE(INDIRECT("1:"&JOUR(DATE(A1;B1+1;0))))))=5)*1) 
  * Vendredi => =SOMME((JOURSEM(DATE(A1;B1;LIGNE(INDIRECT("1:"&JOUR(DATE(A1;B1+1;0))))))=6)*1) 
  * Samedi => =SOMME((JOURSEM(DATE(A1;B1;LIGNE(INDIRECT("1:"&JOUR(DATE(A1;B1+1;0))))))=7)*1) 
  * Dimanche => =SOMME((JOURSEM(DATE(A1;B1;LIGNE(INDIRECT("1:"&JOUR(DATE(A1;B1+1;0))))))=1)*1)  


* Transformer une DATE au  format TEXTE :
   En A1 : 15/06/2015
   Formule en B1 :   =TEXTE(A1;"mmmm")
   Résultat : juin

   Formule en B1 :   =TEXTE(A1;"jjjj")
   Résultat : lundi

   Formule en B1 :   =TEXTE(A1;"jjjj aaaa")
   Résultat : lundi 2015

   Formule en B1 :   =TEXTE(A1;"jjjj jj mmmm aaaa")
   Résultat : lundi 15 juin 2015

* Reconstituer une DATE suivant saisie sur trois cellules :
   En A1 : 15
   En B1 : 6
   En C1 : 2015
   Formule en D1 :   =DATE(A1;B1;C1)
   Résultat : 15/06/2015
 
* Reconstituer la semaine précédente par rapport à une date donnée du lundi au dimanche :
   En A1 : 15/06/2015
  
   Formule en A2 :   =7*ENT(A1/7)-5    Résultat (Lundi) : 08/06/2015
   Formule en A3 :   =7*ENT(A1/7)-4    Résultat (Mardi) : 09/06/2015
   Formule en A4 :   =7*ENT(A1/7)-3    Résultat (Mercredi) : 10/06/2015 
   Formule en A5 :   =7*ENT(A1/7)-2    Résultat (Jeudi) : 11/06/2015   
   Formule en A6 :   =7*ENT(A1/7)-1    Résultat (Vendredi) : 12/06/2015 
   Formule en A7 :   =7*ENT(A1/7)       Résultat (Samedi) : 13/06/2015 
   Formule en A8 :   =7*ENT(A1/7)+1    Résultat (Dimanche) : 14/06/2015   


* Retrouver dans une série de dates, la DATE la plus près de la date du JOUR  :
   En A1 : 01/06/2015
   En B1 : 23/05/2015
   En C1 : 29/05/2015
   Formule en D1 :   =GRANDE.VALEUR(SI(A1:C1<=AUJOURDHUI();A1:C1);1)
   Attention formule matricielle : Validez la formule par les touches CTRL + MAJ + ENTREE (vous devez avoir des parenthèses en début et fin de formule {..........}  )
   Résultat : 01/06/2015


* Indication si la date est supérieure ou égale à la date du jour :
   En A1 : 15/06/2015
   Formule en B1 : =SI(A1>=AUJOURDHUI();"OUI";"NON")
   Résultat : NON si vous avez mentionné 15.06.2015 en A1 à la date où vous lisez cette page.



* Afficher le nombre de jours pour un mois donné :
   En A1 : 02/02/2012
   Formule en B1 : =JOUR(DATE(ANNEE(A1);MOIS(A1)+1;0))
   Résultat : 29 car tient aussi compte des années bissextiles


* Afficher une DATE moins TROIS MOIS :
   En A1 : 15/06/2015
   Formule en B1 : =DATE(ANNEE(A1);MOIS(A1)-3;JOUR(A1))
   Résultat : 15/03/2015 pour information le chiffre en bleu dans la formule égale le nombre à soustraire pour 3 mois.


* Afficher une DATE moins 10 JOURS :
   En A1 : 15/06/2015
   Formule en B1 : =DATE(ANNEE(A1);MOIS(A1);JOUR(A1)-10)
   Résultat : 05/06/2015 pour information le chiffre en bleu dans la formule égale le nombre à soustraire pour 10 jours.


* Afficher une DATE moins 2 ANS :
   En A1 : 15/06/2015
   Formule en B1 : =DATE(ANNEE(A1)-2;MOIS(A1);JOUR(A1))
   Résultat : 15/06/2013 pour information le chiffre en bleu dans la formule égale le nombre à soustraire pour 2 ans


* Afficher une DATE moins nombre de  MOIS mentionné dans une cellule :
   En A1 : 15/06/2015
   En B1 : 3
   Formule en C1 : =DATE(ANNEE(A1);MOIS(A1)-C1;JOUR(A1))
   Résultat : 15/03/2015 pour information le chiffre en bleu correspond à la cellule B1 pour moins 3 mois. Même déplacement que les formules évoquées ci-dessus.

Avec gestion année bixestile (29/2)

=SI(MOIS(DATE(ANNEE(A1);MOIS(A1)+B1;JOUR(A1)))<>MOD(MOIS(A1)+B1;12);DATE(ANNEE(A1);

MOIS(A1)+B1+1;1)-1;DATE(ANNEE(A1);MOIS(A1)+B1;JOUR(A1)))

* Afficher une DATE + nombre d'année mentionné dans une cellule :
   En A1 : 15/06/2015
   En B1 : 3
   Formule en C1 : =DATE(ANNEE(A1)+B1;MOIS(A1);JOUR(A1))
   Résultat : 15/06/2018 pour information le chiffre en bleu correspond à la cellule B1 pour plus 2 ans. Même déplacement que les formules évoquées ci-dessus.

Avec gestion année bixestile (29/2)

=DATE(ANNEE(A1)+B1;MOIS(A1);JOUR(A1))-(MOIS(DATE(ANNEE(A1)+B1;MOIS(A1);JOUR(A1)))<>MOIS(A1)).


* Calculer les sommes des chiffres de tous les lundis :
   Données dans la plage A1:B3
  
   Formule en B4 :   =SOMMEPROD((JOURSEM(A1:B3;2)=1)*(A1:B3))
    Résultat : 20 € pour information le chiffre en bleu correspond au numéro du jour soit 1 = Lundi car pour vendredi nous aurions indiqué le chiffre 5



* Transformer une date Format Anglais au format Français :
   En A1 : 06/15/2015
   Formule en B1 : =SI(ESTTEXTE(A1);DATE(DROITE(A1;NBCAR(A1)-TROUVE("/";A1;TROUVE("/";A1)+1));GAUCHE(A1;TROUVE("/";A1)-1);STXT(A1;TROUVE("/";A1)+1;TROUVE("/";A1;TROUVE("/";A1)+1)-TROUVE("/";A1)-1));DATE(ANNEE(A1);JOUR(A1);MOIS(A1)))
   Résultat : 15/06/2015 


* Trouver la date par rapport à une autre date :
   En A1 : 15/06/2015
   En plage de cellules  A3:A9
   

  
    => Pour calculer la date la plus proche :
    Formule en A12 : =MAX(SI(A3:A9<A1;A3:A9))
    Résultat : 01/06/2015

    => Pour calculer la date la plus éloignée :
    Formule en A12 : =MIN(SI(A3:A9<A1;A3:A9))
    Résultat : 21/02/2001
    Attention formules matricielles : Validez la formule par les touches CTRL + MAJ + ENTREE (vous devez avoir des parenthèses en début et fin de formule {..........}  )


* Obtenir la différence entre deux date :
   En A1 : 26/04/1980
   En B1 : =AUJOURDHUI() (soit dans notre exemple 03/11/2015
   
Formule en C1 : =DATEDIF($A$2;$B$2;"y")&" Ans"
Résultat : 35 Ans

Formule en C1 : =DATEDIF($A$2;$B$2;"m")&" Mois"
Résultat : 426 Mois

Formule en C1 : =DATEDIF($A$2;$B$2;"d")&" Jours"
Résultat : 12 974 Jours

Formule en C1 : =DATEDIF($A$2;$B$2;"y")&" Ans "&DATEDIF($A$2;$B$2;"ym")&" Mois"
Résultat : 35 Ans 6 Mois

Formule en C1 :=DATEDIF($A$2;$B$2;"y")&" Ans "&DATEDIF($A$2;$B$2;"ym")&" Mois "&DATEDIF($A$2;$B$2;"md")&" Jours"
Résultat : 35 Ans 6 Mois 8 Jours