Problème dans les formules EXCEL - Calcul d'âge [Résolu/Fermé]

Signaler
Messages postés
10
Date d'inscription
jeudi 15 mai 2014
Statut
Membre
Dernière intervention
4 juin 2014
-
Messages postés
17258
Date d'inscription
dimanche 17 février 2008
Statut
Contributeur
Dernière intervention
19 octobre 2020
-
Bonjour,

J'ai un problème avec les formules que vous proposez. Je les ai toutes essayées et celles ci ne fonctionnent pas pour ce que je souhaite faire.

C'est à dire que l'intérêt pour moi serait de calculer l'âge d'une personnage à une date donnée qui peut, soit être fixe pour tout le monde (disons le 1er janv 37), soir variable en fonction des individus (ça oscille entre 36 et 39).
Sauf que, vu les formules, il y a des erreurs de calculs. DATEDIF, qui est pour moi la plus intéressante car la plus précise, ne fonctionne pas lorsque les individus sont nés avant 1900 et, dans mon cas ça fait beaucoup de personnes... L'autre formule, déjà pas très précise, qui consiste à calculer l'âge d'une personne aujourd'hui (ce à quoi je retire ensuite un nombre d'année, 77 en l'occurrence) ne fonctionne pas non plus quand les personnes sont nées après 1910.

Alors, je sais que je vais me faire remettre à ma place, qu'on va me dire que je sais pas taper une formule, mais dites ce que vous voulez, je veux juste une formule qui marche dans TOUS les cas.

Merci d'avance pour votre écoute (lecture) et vos services

Cdlt, PS

14 réponses

Messages postés
17258
Date d'inscription
dimanche 17 février 2008
Statut
Contributeur
Dernière intervention
19 octobre 2020
4 297
Bonjour,

Effectivement tu vas te faire taper dessus, avant 1900 c'est normal que le calcul ne fonctionne pas en l'état. Excel n'as pas prévu ce genre de calcul parce que le premier jour informatique 1 correspond au 01/01/1900
mais il y a des possibilités à un jour prés de calcul que je puis te donner

Quand à la deuxième observation ou tu vas te faire remettre en place, Excel est capable et le fait très bien, calculer n'importe quel âge avec DATEDIF entre le 01/01/1900 et le 31/12/9999 tu vois qu'il y a de la marge.

Revenons à nos moutons, dans quelle colonne as tu les dates de départ, et dans quelle colonne les dates de fin à part que ce soit par rapport à AUJOURDHUI()
A+
Mike-31

Une période d'échec est un moment rêvé pour semer les graines du savoir.
Messages postés
10
Date d'inscription
jeudi 15 mai 2014
Statut
Membre
Dernière intervention
4 juin 2014

Bonjour Mike,

Déjà, merci d'avoir répondu si promptement. Je constate donc que Excel n'est pas fait pour les historiens, à mon grand désespoir.

Pour les dates de départ (naissance), c'est en E et les dates données (la date où je veux savoir leur âge), en M. Mais je pense qu'il serait plus simple d'arrêter la date commune pour tous mes données, donc on peut dire que M=01/01/1937.

Peut faire un calcul du type "si né le 28 janv 1884 c'est égal à 1900 plus 5 ans, 11 mois et 3 jours"?
Messages postés
17258
Date d'inscription
dimanche 17 février 2008
Statut
Contributeur
Dernière intervention
19 octobre 2020
4 297
Re,

tu vas voir qu'il est très simple de contourner le problème de date antérieur à 1900 avec une marge d'erreur en fonction des divers calendriers et modifications. que veux tu dire par "Mais je pense qu'il serait plus simple d'arrêter la date commune pour tous mes données, donc on peut dire que M=01/01/1937"

en M il y aura que cette date 01/01/1937 (il est possible d'avoir toute sorte de date Excel gére)

ensuite le résultat attendu simplement le nombre d'années ou le nombre d'années, mois et jours sous ce format par exemple 100 ans 3 mois 12 jours

après je t'expliquerais
Messages postés
10
Date d'inscription
jeudi 15 mai 2014
Statut
Membre
Dernière intervention
4 juin 2014

Alors,

Quand je parlais d'arrêter la date en M c'est plus par facilité qu'autre chose car, travaillant sur une base de criminels, je comptais d'abord prendre la date de passage à l'acte mais il se trouve que je ne les ai pas toutes. Donc, autant tabler sur une date arrêtée, comme celle que je t'ai fournie (1er janv 37).

Ensuite, je préfère encore avoir un date qui compte années, mois et jours qu'une date avec simplement le nombre d'année.

Merci
Messages postés
10
Date d'inscription
jeudi 15 mai 2014
Statut
Membre
Dernière intervention
4 juin 2014

Bonjour,

Je suis en train de finir ma base de données,et le format "années, mois et jours" pose en réalité problème. En effet, je voudrais faire une moyenne mais certains de mes individus ont un âge qui ne vient pas de la formule mais qui est directement donné (type: 35 ans). J'ai essayé de le mettre en "35 ans, 0 mois, 0 jours" et cela ne donne rien.
Pourriez vous me communiquer la formule à l'arrondi (35, 2 ans) afin que je puisse faire une moyenne d'âge?

Cordialement,

PS.
Messages postés
17258
Date d'inscription
dimanche 17 février 2008
Statut
Contributeur
Dernière intervention
19 octobre 2020
4 297
Re,

Breuuu ça fait froid ton truc.

alors comme je te disais plus haut Excel gère les dates jusqu'au 31/12/9999, il suffit donc d'ajouter 4000 ans à une date pour tenir compte des années bissextiles
qui doivent être divisibles par 4 ou par 100, mais pas par 400.

partant de la il serait facile d'ajouter 4000 à la date antérieure à 1900 avec un recherche des 4 dernier chiffre et d'appliquer la fonction DATEDIF, mais le problème est qu'il est possible d'avoir colonne E des dates postérieures à 1900 alors l'astuce est de tester l'erreur date et d'ajouter ces 4000 ans et appliquer la fonction DATEDIF

je te monte une formule après manger
Messages postés
10
Date d'inscription
jeudi 15 mai 2014
Statut
Membre
Dernière intervention
4 juin 2014

Entendu,

Merci beaucoup
Messages postés
2369
Date d'inscription
mercredi 3 février 2010
Statut
Membre
Dernière intervention
16 octobre 2020
880
Bonjour,
Mike31 me pardonnera d'avoir triché: j'avais commencé avant le repas;-)
C'est loin d'être parfait, mais je le livre quand même:
https://www.cjoint.com/?3EpowSg5UdS
Messages postés
17258
Date d'inscription
dimanche 17 février 2008
Statut
Contributeur
Dernière intervention
19 octobre 2020
4 297
Re, Salut Tontong,

voilà ma copie

https://www.cjoint.com/?DEppjdSAfLh
Messages postés
2369
Date d'inscription
mercredi 3 février 2010
Statut
Membre
Dernière intervention
16 octobre 2020
880
C'est plus complet: Esterreur....
C'est plus clair: colonnes auxiliaires au lieu de formules nommées.
C'est mieux expliqué...
Ça reste un peu alambiqué, mais ça c'est Excel, espérons que b-de-rosen s'y retrouve.
Messages postés
17258
Date d'inscription
dimanche 17 février 2008
Statut
Contributeur
Dernière intervention
19 octobre 2020
4 297
Re,

J'ai inséré deux colonnes mais si on est à l'aise avec les formules genre usine à gaz, il est possible de supprimer les colonne O et P et d'imbriquer toutes les formules en R2, ou placer les formules dans des champs nommés

=SI(OU(E2="";M2="");"";SI(SI(ET(ESTERREUR(DATE(ANNEE(E2);MOIS(E2);JOUR(E2)));ESTERREUR(DATE(ANNEE(M2);MOIS(M2);JOUR(M2))));(GAUCHE(M2;NBCAR(M2)-4)&DROITE(M2;4)+4000)*1;SI(ESTERREUR(DATE(ANNEE(E2);MOIS(E2);JOUR(E2)));DATE(ANNEE(M2)+4000;MOIS(M2);JOUR(M2));M2))-SI(ESTERREUR(DATE(ANNEE(E2);MOIS(E2);JOUR(E2)));(GAUCHE(E2;NBCAR(E2)-4)&DROITE(E2;4)+4000)*1;E2);TEXTE(DATEDIF(SI(ESTERREUR(DATE(ANNEE(E2);MOIS(E2);JOUR(E2)));(GAUCHE(E2;NBCAR(E2)-4)&DROITE(E2;4)+4000)*1;E2);SI(ET(ESTERREUR(DATE(ANNEE(E2);MOIS(E2);JOUR(E2)));ESTERREUR(DATE(ANNEE(M2);MOIS(M2);JOUR(M2))));(GAUCHE(M2;NBCAR(M2)-4)&DROITE(M2;4)+4000)*1;SI(ESTERREUR(DATE(ANNEE(E2);MOIS(E2);JOUR(E2)));DATE(ANNEE(M2)+4000;MOIS(M2);JOUR(M2));M2));"y");"[>1]0"" ans "";[>]""1 an "";"))&TEXTE(DATEDIF(SI(ESTERREUR(DATE(ANNEE(E2);MOIS(E2);JOUR(E2)));(GAUCHE(E2;NBCAR(E2)-4)&DROITE(E2;4)+4000)*1;E2);SI(ET(ESTERREUR(DATE(ANNEE(E2);MOIS(E2);JOUR(E2)));ESTERREUR(DATE(ANNEE(M2);MOIS(M2);JOUR(M2))));(GAUCHE(M2;NBCAR(M2)-4)&DROITE(M2;4)+4000)*1;SI(ESTERREUR(DATE(ANNEE(E2);MOIS(E2);JOUR(E2)));DATE(ANNEE(M2)+4000;MOIS(M2);JOUR(M2));M2));"ym");"[>]0"" mois "";")&SI(DATEDIF(SI(ESTERREUR(DATE(ANNEE(E2);MOIS(E2);JOUR(E2)));(GAUCHE(E2;NBCAR(E2)-4)&DROITE(E2;4)+4000)*1;E2);SI(ET(ESTERREUR(DATE(ANNEE(E2);MOIS(E2);JOUR(E2)));ESTERREUR(DATE(ANNEE(M2);MOIS(M2);JOUR(M2))));(GAUCHE(M2;NBCAR(M2)-4)&DROITE(M2;4)+4000)*1;SI(ESTERREUR(DATE(ANNEE(E2);MOIS(E2);JOUR(E2)));DATE(ANNEE(M2)+4000;MOIS(M2);JOUR(M2));M2));"md")=0;"";SI(SI(ET(ESTERREUR(DATE(ANNEE(E2);MOIS(E2);JOUR(E2)));ESTERREUR(DATE(ANNEE(M2);MOIS(M2);JOUR(M2))));(GAUCHE(M2;NBCAR(M2)-4)&DROITE(M2;4)+4000)*1;SI(ESTERREUR(DATE(ANNEE(E2);MOIS(E2);JOUR(E2)));DATE(ANNEE(M2)+4000;MOIS(M2);JOUR(M2));M2))-SI(ESTERREUR(DATE(ANNEE(E2);MOIS(E2);JOUR(E2)));(GAUCHE(E2;NBCAR(E2)-4)&DROITE(E2;4)+4000)*1;E2);TEXTE(TRONQUE(DATEDIF(SI(ESTERREUR(DATE(ANNEE(E2);MOIS(E2);JOUR(E2)));(GAUCHE(E2;NBCAR(E2)-4)&DROITE(E2;4)+4000)*1;E2);SI(ET(ESTERREUR(DATE(ANNEE(E2);MOIS(E2);JOUR(E2)));ESTERREUR(DATE(ANNEE(M2);MOIS(M2);JOUR(M2))));(GAUCHE(M2;NBCAR(M2)-4)&DROITE(M2;4)+4000)*1;SI(ESTERREUR(DATE(ANNEE(E2);MOIS(E2);JOUR(E2)));DATE(ANNEE(M2)+4000;MOIS(M2);JOUR(M2));M2));"md"));"[>1]0"" jours "";[>]""1 jour "";"))))
Messages postés
10
Date d'inscription
jeudi 15 mai 2014
Statut
Membre
Dernière intervention
4 juin 2014

Vous êtes bien aimables,

J'applique ça et je vous en dis des nouvelles. En tous cas, ce sont de bien belles formules :-)

Merci
Messages postés
17258
Date d'inscription
dimanche 17 février 2008
Statut
Contributeur
Dernière intervention
19 octobre 2020
4 297
Re,

on va faire le point sur ce que tu as fait et ce que tu cherches faire

quel est la formule que tu as retenue pour calculer l'âge
quel est le résultat affiché
as tu formaté tes formules avec un format personnalisé

pour revenir à ta question si j'ai bien compris tu saisis 30 ans et tu voudrais que le format d'affichage soit 35 ans 0 mois 0jour
c'est bien cela

par contre je ne comprends pas ce que tu veux dire par "formule à l'arrondi (35, 2 ans)"
Messages postés
10
Date d'inscription
jeudi 15 mai 2014
Statut
Membre
Dernière intervention
4 juin 2014

Rebonjour,

La formule que j'ai retenu: pour E = date de naissance; O = date où je calcule l'âge (en l'occurrence 1er janv 37) Q = date de naissance antérieure à 1900; R pour une date postérieure à 1900:

=SI(OU(Q170="";E170="");"";DATEDIF(Q170;R170;"y")&" ans, "&DATEDIF(Q170;R170;"ym")&" mois et "&DATEDIF(Q170;R170;"md")&" jours")

Ca me donne donc des résultats en années, mois et jours.

Je n'ai pas formaté mes résultats. Comment puis-je le faire et qu'est ce que cela peut m'apporter? Qu'importe le moyen d'y arriver, je voudrais faire un moyenne de toutes mes personnes en sachant que certains chiffre sont écrits manuellement (type : 35 ans) et ne sont donc pas obtenus à l'aide de formules.

D'où ma question: serait possible d'avoir une formule qui me donne le résultat en années (ex: résultat = 21,5 (soit 21 ans et 6 mois)) afin de rentrer une moyenne qui prendrait en compte, à la fois les résultats entrés manuellement et ceux obtenus grâce à une formule?

J'espère que c'est assez clair.

En vous remerciant,

PS
Messages postés
17258
Date d'inscription
dimanche 17 février 2008
Statut
Contributeur
Dernière intervention
19 octobre 2020
4 297
Re,

Essaye comme cela

=SI(OU(Q170="";E170="");"";DATEDIF(Q170;R170;"y")&","&(TEXTE(DATEDIF(Q170;R170;"ym")/12*100;"0")))*1
Messages postés
10
Date d'inscription
jeudi 15 mai 2014
Statut
Membre
Dernière intervention
4 juin 2014

En fait, j'ai un petit problème, mais qui doit tenir du détail.

J'ai fait des filtres pour mes individus (sur certains je calcule l'âge, d'autres je veux savoir l'emploi, etc). Je voudrais faire un moyenne d'âge, mais que sur les éléments filtrés. Seulement, un problème se pose lors de ma sélection (tout est sélectionné, malgré le filtre).

Mes filtres qui ici m'intéressent sont: "âge" ; "âge - nationalité"; "âge - nationalité - emploi"; "âge - emploi", ils sont en "C"

J'ai donc essayé de rentrer une formule avec un moyenne si ensemble (MOYENNE.SI.ENS) mais ça bloque...

Voilà ce que j'ai fait, sans résultat. C correspond à la case où sont mes critères de filtrage
=MOYENNE.SI.ENS(Y1:Y170;C1:C170"âge";C1:C170"âge - nationalité";C1:C170"âge - nationalité - emploi";C1:C170"âge - emploi")

Sachant que, dans mes résultats en Y, il y a des erreurs de valeurs (je n'ai pas l'âge de tout le monde, donc ça fait une erreur de formule), peut être est-il possible de faire un filtre en Y en ne sélectionnant que les cases au résultat "numérique" (ex: 35, 2 ou 61,1 ... ) et établir une moyenne sur ces chiffres?

Bien à vous

PS
Messages postés
10
Date d'inscription
jeudi 15 mai 2014
Statut
Membre
Dernière intervention
4 juin 2014

Bonjour,

cela fonctionne parfaitement,

Merci.
Messages postés
17258
Date d'inscription
dimanche 17 février 2008
Statut
Contributeur
Dernière intervention
19 octobre 2020
4 297
Re,

Tu saisis cette formule, en supposant que tes âges sont au format numérique si cette dernière fait suite à ta dernière demande et en supposant que ces valeurs sont en colonne Q (à adapter)

teste cette formule et fait joujou avec tes filtres

=SOUS.TOTAL(1;Q1:Q100)

ou tu as également

=SOUS.TOTAL(109;Q1:Q100)/SOUS.TOTAL(3;Q1:Q100)

A+
Mike-31

Une période d'échec est un moment rêvé pour semer les graines du savoir.
Bonjour,

J'ai une question, et je pense que c'est la dernière.

Je voudrais établir des fourchettes d'âge en fonction du SOUS.TOTAL.

Grosso modo, je voudrais savoir, combien de personnes ont entre 20 et 40 ans, combien entre 40 et 60, etc. Ce calcul doit être fait en sous.total car dans mes données, j'ai des cases vide que j'exclue grâce à un filtre (il s'agissait de ma dernière requête, j'avais besoin de faire une moyenne d'âge).

Merci d'avance pour l'attention que vous pourrez porter à ce message.

Cdlt,

PS
Messages postés
10
Date d'inscription
jeudi 15 mai 2014
Statut
Membre
Dernière intervention
4 juin 2014

Bonjour,

Cela fonctionne à merveille, encore merci.

Cordialement,

PS
Messages postés
17258
Date d'inscription
dimanche 17 février 2008
Statut
Contributeur
Dernière intervention
19 octobre 2020
4 297
Re,

Mais le nombre de personnes entre 20 et 40 ans, et 40 et 60 etc... doit il se fais sur les colonnes filtrées ou sur l'ensemble de tes données ce qui serait le plus logique

et ce calcul doit t il se faire par rapport à un age saisi dans une colonne ou par rapport à des dates de naissance