Exercices aidés et corrigés
Guides Excel et Internet Facile
Téléchargement
Trucs et Astuces
Applications Excel
Touches de raccourci
Liens

Calculs sur les dates avec la fonction =DATEDIF()

 

---

Calculs sur les dates avec la fonction =DATEDIF()

La fonction =DATEDIF() permet de calculer la différence entre deux dates en années, mois et jours. Elle conmporte 3 arguments :

Syntaxe : =DATEDIF(Date1;Date2;code)

Cette fonction renvoie la différence entre Date1 et Date2 (attention, Date2 >= Date1) selon l'argument Code, qui peut prendre les valeurs suivantes :

• "y" : différence en années
• "m" : différence en mois
• "d" : différence en jours
• "ym" : différence en mois, une fois les années soustraites
• "yd" : différence en jours, une fois les années soustraites
• "md" : différence en jours, une fois les années et les mois soustraits

La fonction =DATEDIF() peut être en particulier utilisée pour calculer des âges. Par exemple, si la cellule A1 contient une date de naissance et la cellule A2 la date du jour :

Pour obtenir l'affichage d'un nombre en chiffre romains, il suffit d'utiliser la formule =ROMAIN()

par exemple,

• vous tapez le nombre 1963 dans la cellule A1,
• en A2 vous écrivez : =ROMAIN(A1)
• vous obtenez : MXMLXIII

La fonction =SOUSTOTAL(no_fonction;réf1;réf2;...) vous permettra d'effectuer tout un tas d'opérations portant sur une série de données. Cette fonction exige 2 arguments :

• no_fonction représente le nombre compris entre 1 et 11 indiquant quelle fonction utiliser pour calculer les sous-totaux d'une liste (voir ci-dessous).
• Réf1, réf2, représentent les 1 à 29 plages ou références pour lesquelles vous voulez un sous-total.

La liste des opérations disponible dans le 1er argument est la suivante :

Par exemple, =SOUSTOTAL(9;A1:A18) fera la somme des cellules A1 à A18. Chose interessante, si ces cellules contiennent elles-même un sous total, il ne sera pas pris en compte (voir ci-dessous)

Remarques

• Si d'autres sous-totaux se trouvent à l'intérieur de la plage définie par les arguments réf1, réf2,... (ou sous-totaux imbriqués), ces sous-totaux imbriqués ne sont pas pris en compte afin d'éviter tout comptage en double.
• La fonction SOUS.TOTAL ne prend pas en compte les lignes masquées suite à un filtrage. Le sous-total ne porte que sur les données visibles résultant du filtrage d'une liste.
• Si l'une des références est une référence 3D, la fonction SOUS.TOTAL renvoie la valeur d'erreur #VALEUR!

Essayez d'addidtionner : 15:30 et 20:50 , vous obtiendrez : 12:20 !

Comment dès lors pouvoir additionner des nombres saisis au format heures-minutes ?

Il suffit en fait d'appliquer un format de nombre personnalisé à la cellule contenant la formule de somme . Le format est le suivant (format à créer dans le menu "format/cellule/onglet nombre/catégorie personnalisé" et écrire dans la zone "type")

Un petit truc maintenant pour calculer en heures REELLES et en finir avec les centièmes.

soit 825,00 . Ne pas oublier de formater C1 en monnaie.

Etant donné un nombre dans la cellule A1, la formule est la suivante :

Etant donné un nombre dans la cellule A1, la formule est la suivante :

Il arrive que, en fontion des données de votre feuille de calcul, vos formules affichent des messages d'erreur.
Cela peut se produire notamment lorsque les formules sont préparées à l'avance et que les cellules utilisées dans une formule sont vide. Par exemple dans la cellule A3 on tape la formule : =A1/A2. Cette formule affichera #DIV/0! si A2 est vide.

Pour masquer ce message d'erreur, on peut utiliser la mise en forme conditionnelle d'Excel.

1/ on sélectionne la cellule contenant la formule fournissant éventuellement un message d'erreur, (dans notre exemple la cellule A3),
2/ on utilise le menu "Format - Mise en forme conditionnelle",
3/ dans condition 1, on déroule la liste et on choisi "La formule est",
4/ dans le champ de droite on écrit : =ESTERREUR(A3)
5/ on clique sur le bouton "Format" et dans l'onglet "Police" on choisi le blanc come couleur de police,
6/ on valide en cliquant sur OK pour les 2 boîtes de dialogue.

Si A3 est rempli, le résultat du calcul apparaît, sinon la cellule semble vide.

Selectionner dans la barre de formule la référence de cellule à changer et taper sur la touche "F4"

Vous voulez repérer les cellules prises en compte dans un calcul ? : Menu "Outils-Audit-Repérer les antécédents".

Vous voulez savoir si une cellule est utilisée dans un calcul ? : Menu "Outils-Audit-Repérer les dépendants".

Vous voulez supprimez les flêches générées par le menu "Outils-Audit" ? : Menu "Outils-Audit-Supprimez toutes les flêches.

Passer d'heure Excel en heure centième : HeureExcel*24 . Ex A1=1:30. A1*24 donne 1,50 (ne pas oublier de mettre la cellule contenant la formule dans un format numérique et non date)

Détecter si une date est dans une année est bissextile.
La formule suivante "=SI(MOIS(DATE(ANNEE(A1);2;29)) =2;VRAI;FAUX)" renvoie vrai si la date en A1est bissextile et faux dans le cas contraire.

Rechercher le dernier jour d'un mois.(30, 31, 28 ou 29) 

A1 contient une date au format excel évidemment.

Afficher le jour d'une date avec une majuscule pour la première lettre :

A1 contient une date au format excel
On obtient le même résultat avec :

A noter que "jjjj" peut être remplacer par "mmmm" (pour obtenir le mois) ou encore par "jjjj mm aaaa"....

Rechercher le nombre de valeurs différentes dans une plage :

Saisir :

et valider avec touches Ctrl-Maj-Entrée (Merci à Laurent Longre..)

L'indirection ! ou comment utiliser une formule pour faire appel à une adresse qui se trouve dans une cellule
A2 contient le nom d'un classeur ("Exefac.xls" par exemple)
A3 contient la formule: =INDIRECT(A2&"B6") 
Donc A3 aura la valeur contenu dans la cellule B6 de Exefac.xls (qui doit etre ouvert, bien entendu)

Calculer et obtenir des heures négatives :

Dans ce cas si

Calculer le nombre de date en fonction d'un mois Avec NB.SI

en G2, saisir :

=NB.SI($A$2:$D$3;">"&DATE(ANNEE(B5);MOIS(B5);0))-NB.SI($A$2:$D$3;">"&DATE(ANNEE(B5);MOIS(B5)+1;0))

(Cette formule fait suite à une question d'un internaute ,qui lui même chercher à répondre à une internaute, etc...)

Lorsqu'apparaît la croix, plutôt que tirer laborieusement la formule vers le bas, il suffit de double-cliquer dessus. La formule se duplique alors jusqu'à la dernière cellule, à condition qu'il y ait des données sur la colonne de droite ou de gauche.

exemple avec un calcul d'âge (dont la formule est apparente) >>>> il suffit de double-cliquer sur la croix noire (1ère figure) pour que la formule se duplique jusqu'en B14 (2ème figure).

en oubliant les fameuses références A5, D3, H4 ... (la bataille navale, c'est terminé !!) -

Exemple avec des taux de remise variables en fonction d'un code (1,2 ou 3) affichant des remises de 5%, 10% ou 15 % :

La cellule A1 contient le code d'escompte (1, 2 ou 3),
La cellule en B1 s'écrit :

où Si le code de remise est "1" la valeur de B1 sera 5%, si le code de remise est "2" la valeur de B1 sera 10% et si le code d'escompte est "3", la valeur de B1 sera 15%.

La formule peut contenir autant de variables que vous le désirez.

Si le nombre se trouve dans la cellule A1, la formule sera la suivante :

Il suffit de diviser le nombre par 86400 et mettre ensuite la cellule au format : HH:MM:SS