× Aidez la recherche contre le COVID-19 avec votre ordi ! Rejoignez l'équipe PC Astuces Folding@home
 > Tous les forums > Forum Bureautique
 excel 2013, formule de cellulesSujet résolu
Ajouter un message à la discussion
Pages : [1] 2 ... Fin
Page 1 sur 2 [Fin]
prosper34
  Posté le 02/05/2017 @ 01:15 
Aller en bas de la page 
Astucien

j'ai un tableau de dates comme ceci :
17 01 1781
06 04 1786
09 03 1777
02 06 1721
13 06 1711

je veux le mettre dans une cellule comme ça :
1781-01-17
1786-04-06
1777-03-09
1721-06-02
1711-06-13

pour cela j'utilise la formule (dans d2) : =c2&"-"&b2&"-"&a2
et j'obtiens ça qui ne me convient pas :
1781-1-17
1786-4-6
1777-3-9
1721-6-2
1711-6-13

comment conserver les "0"
j'ai essayé les formats de cellule sans résultat.

merci à vous.

Publicité
Daneel Olivaw
 Posté le 02/05/2017 à 04:42 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Maître astucien

Bonjour

Si toutes les cellules sont formatées en tant que texte, ça fonctionne.

Magnan
 Posté le 02/05/2017 à 06:53 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucien

Bonjour,

Pourquoi ne pas prendre directement Clic droit// format de Cellules//Nombres//Personalisée et saisir yyyy-mm-jj

Plus simple, en plus tu pourras faire des calculs et statistiques.

A+

prosper34
 Posté le 02/05/2017 à 09:29 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucien

et merci pour vos réponses

Daneel Olivaw, Magnan, Anonyme

mais voilà : la feuille est créée depuis 2006, le format des cellules est personalisé 00.

si je change ce format en texte les 0 disparaissent (si je crée la feuille en format texte OK)

si je les met yyyy-mm-jj idem (aaaa-mm-jj où yyyy-mm-dd ?)

Et surtout éviter comme la peste de mettre des informations numériques sous forme d'un texte

formatage d'origine 00 pour cette raison. (je suis à peu près nul mais ça je sais)



Modifié par prosper34 le 02/05/2017 09:37
Daneel Olivaw
 Posté le 02/05/2017 à 17:36 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Maître astucien

Bonjour

Ceci devrait convenir :

=C1&"-"&SI(NBCAR(B1)=2,GAUCHE(B1,1)&DROITE(B1,1),"0"&GAUCHE(B1,1))&"-"&SI(LEN(A1)=2,GAUCHE(A1,1)&DROITE(A1,1),"0"&DROITE(A1,1))

J'ai traduit de l'anglais. J'espère ne pas m'être trompé.

=C1&"-"&IF(LEN(B1)=2,LEFT(B1,1)&RIGHT(B1,1),"0"&RIGHT(B1,1))&"-"&IF(LEN(A1)=2,LEFT(A1,1)&RIGHT(A1,1),"0"&RIGHT(A1,1))

prosper34
 Posté le 02/05/2017 à 18:55 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucien

merci,

copié/collé depuis la feuille originale et des formules,

en anglais ou en français ça butte sur left/gauche

et en remplaçant les , par ; la réponse #NOM?



Modifié par prosper34 le 02/05/2017 19:04
Daneel Olivaw
 Posté le 02/05/2017 à 19:02 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Maître astucien

Salut

Chez moi, en anglais, ça marche. Faut voir si je ne me suis pas gourré dans la traduction.

Daneel Olivaw
 Posté le 02/05/2017 à 19:05 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Maître astucien

Salut

Il y avait bien une erreur.

=C1&"-"&SI(NBCAR(B1)=2,GAUCHE(B1,1)&DROITE(B1,1),"0"&DROITE(B1,1))&"-"&SI(LEN(A1)=2,GAUCHE(A1,1)&DROITE(A1,1),"0"&DROITE(A1,1))

=C1&"-"&IF(LEN(B1)=2,LEFT(B1,1)&RIGHT(B1,1),"0"&RIGHT(B1,1))&"-"&IF(LEN(A1)=2,LEFT(A1,1)&RIGHT(A1,1),"0"&RIGHT(A1,1))



Modifié par Daneel Olivaw le 02/05/2017 19:05
Daneel Olivaw
 Posté le 02/05/2017 à 19:14 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Maître astucien
Salut

En FR le séparateur est le point-virgule et pas la virgule ( version US ).

Anonyme

Anonyme

Bien vu. J'avais oublié ce détail.

Donc :

=C1&"-"&SI(NBCAR(B1)=2;GAUCHE(B1,1)&DROITE(B1,1);"0"&DROITE(B1,1))&"-"&SI(LEN(A1)=2;GAUCHE(A1,1)&DROITE(A1,1);"0"&DROITE(A1,1))

Publicité
prosper34
 Posté le 02/05/2017 à 19:20 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucien

merci à tous les 2,

j'ai appliqué les corrections + LEN > NBCAR

=C1&"-"&SI(NBCAR(B1)=2;GAUCHE(B1;1)&DROITE(B1;1);"0"&DROITE(B1;1))&"-"&SI(NBCAR(A1)=2;GAUCHE(A1;1)&DROITE(A1;1);"0"&DROITE(A1;1))

plus que quelques dizaines de classeurs à modifier.



Modifié par prosper34 le 02/05/2017 19:38
Braxton
 Posté le 03/05/2017 à 03:35 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petit astucien

Bonjour,

Plus simple

=D4&"-"&DROITE("0"&C4;2)&"-"&DROITE("0"&B4;2)

avec le jour en B4

mois en C4

année en d4

ferrand
 Posté le 03/05/2017 à 06:35 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Bonjour à tous,

Une solution à base de fonction personnalisée.

Function FRMATDATE(jma As Range)
Dim d
Application.Volatile
With jma
d = DateSerial(.Cells(1, 3), .Cells(1, 2), .Cells(1, 1))
End With
FRMATDATE = Format(d, "yyyy-mm-dd")
End Function

La fonction traite les données jour-mois-année en tant que date et renvoie une chaîne texte formatée.

Excel ne reconnaît pas les dates avant le 1er janvier 1900 (et même elle ne sont justes qu'à partir du 1er mars 1900...) alors que la question ne se pose pas pour VBA qui n'a pas de limitation pour identifier une date antérieure à 1900.

La fonction la renvoie formatée sous forme texte (le résultat de la fonction VBA Format est toujours du texte) et non valeur-date, car Excel ne reconnaissant pas une date, il n'est pas possible de lui appliquer un format de date au niveau cellule. Par contre le texte formaté est accueilli sans difficulté par Excel.

Voir Classeur joint. La macro est dans Module1. La fonction s'utilise comme n'importe quelle autre fonction d'Excel.

http://www.cjoint.com/c/GEdeHTNLseu

Cordialement.

ferrand
 Posté le 03/05/2017 à 08:54 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Bonjour Anonyme

Pour ma part, j'ai opéré sur une plage de 3 cellules car c'est ce qui apparaissait dans les images de tableur, et d'ailleurs également ce qu'avait exprimé Prosper :

pour cela j'utilise la formule (dans d2) : =c2&"-"&b2&"-"&a2

Mais tu as raison, rien ne s'oppose à opérer à partir de la date apposée dans une seule cellule. On tape :

17/1/1781 qu'Excel va cadrer à gauche car ne reconnaissant pas l'expression comme date...

Function FORMATDATE(jma As String)
Dim d
Application.Volatile
d = CDate(jma)
FORMATDATE = Format(d, "yyyy-mm-dd")
End Function

Fonction alternative à la précédente.

Si on a 17/1/1781 en A8, =FORMATDATE(A8) renverra : 1781-01-17.

Cordialement.

http://www.cjoint.com/c/GEdg1W7Kq3u

prosper34
 Posté le 03/05/2017 à 08:54 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucien

merci ferrand je teste dès que possible.

prosper34
 Posté le 03/05/2017 à 09:19 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucien

Anonyme a écrit :

Salut

Quel est l'intérêt d'obliger à décomposer la date sous la forme de 3 cellules distinctes

alors qu'il est possible de la travailler à partir d'une seule cellule ( cf. demande initiale ) ?

Anonyme

Anonyme

à l'origine la date est déjà décomposée en 3 cellules et ce qui me posait problème c'était de les remettre en 1 seule.

prosper34
 Posté le 03/05/2017 à 10:45 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Astucien

Anonyme a écrit :

Salut

Au temps pour moi !

Anonyme

Anonyme

ce soir je me coucherais moins bête.

ton expression ne me paraissait pas correcte, j'ai donc cherché :

http://bescherelletamere.fr/autant-pour-moi-ou-au-temps-pour-moi/

Braxton
 Posté le 03/05/2017 à 23:16 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petit astucien

Re-bonjour,

Dans son message initial, prosper34 disait qu'il utilisait la formule :
"... pour cela j'utilise la formule (dans d2) : =c2&"-"&b2&"-"&a2 ..." et il obtenait : 1781-1-17
Ce qui signifie que :
dans a2, il y a le jour
dans b2, le mois
et dans c2, l'année.

Donc pour obtenir un résultat comme espéré par prosper34, il faut utiliser la formule ci-après en d2 :

=C2&"-"&DROITE("0"&B2;2)&"-"&DROITE("0"&A2;2)

C'est le plus SIMPLE et le résultat obtenu est une date ( en tous cas, dans le classeur de Open Office ) !

'Au temps 'ou... autant le savoir ... !

Publicité
ferrand
 Posté le 04/05/2017 à 08:19 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Bonjour Braxton,

Pas dans Excel : avec ta formule tu obtiens bien : 1781-01-17 mais ce n'est pas une date, c'est du texte !

La différence entre une date et du texte est qu'une date est un nombre alors que le texte n'en est pas !

Pour obtenir une date, il faudrait utiliser la fonction DATE : =DATE(C2;B2;A2) et mettre en format de cellule : "aaaa-mm-jj"

Mais cela ne peut pas marcher non plus car 1781 ! Excel ne reconnaît pas de date avant 1900.

Cordialement.

Braxton
 Posté le 04/05/2017 à 21:14 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petit astucien

Re,

Donc un logiciel gratuit ( le classeur de Open Office ) est plus commode que Excel qui est payant ...!

Prosper34 ... a bon entendeur, salut.

ferrand
 Posté le 05/05/2017 à 06:46 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Bonjour,

Je me range tout à fait à l'avis de Anonyme ! L'étendue des possibilités d'Excel, VBA inclus s'entend, est indéniable...

Mais il est vrai que dans certains domaines Excel comporte des carences inexplicables et inexcusables. C'est le cas en cas qui concerne les dates pour lesquelles on comprend difficilement pourquoi la reconnaissance n'en a pas été étendue aux dates antérieures à 1900 !

Pire même, le fameux bogue du 29 février 1900, qui rend le travail sur la période 1er janvier-28 février 1900 aléatoire (si on cherche le jour de semaine d'une de ces dates, il sera faux dans Excel), que Microsoft n'a jamais ignoré puisque introduit volontairement à l'origine pour des raisons de concurrence commerciale avec Lotus 123 (qui avait fait cette erreur sur le 29 février 1900, date qui n'existe pas), n'a jamais été rectifié !

Il s'ensuit que l'on peut avoir la surprise de voir se décaler d'un jour en passant par VBA sur cette période, car les numéros de série entre Excel et VBA ne se correspondent pas.

Excel n'est pas un outil rêvé pour les généalogistes, mais en travaillant sur dates avec VBA on peut pallier à ses carences en la matière : le jour 1 pour VBA est le 31 déc. 1899, et le 30 déc. est le jour 0, et la série se continue en valeurs négatives jusqu'au 1er janvier 100...

Ceci dit, mais c'est le cas de tous les tableurs, la série de dates est calculée selon un calendrier Grégorien proleptique, soit comme si le calendrier en vigueur avait toujours existé... Ainsi si l'on cherche la veille du 15 octobre 1582, on aura 14 octobre alors qu'il s'agissait du 4 octobre (calendrier Julien) [C'est vrai pour moi qui suit niçois ! l'état de Savoie ayant appliqué les directives vaticanes à la date prévue ; pour la France qui a quelque peu cafouillé dans l'application, le passage s'est fait du 9 au 20 déc. 1582...]

Notons que pour régler ce type de problème on peut mettre à contribution VBA pour aligner l'affichage des dates sur la réalité historique (correctif d'affichage variant selon le siècle et le pays, en conservant la série continue de base qui permettra le calcul du jour de semaine, la succession des 7 jours de la semaine n'ayant jamais varié depuis l'origine...)

Ceci étant le problème de supériorité soulevé à partir de réaction de formule propre à chaque tableur, ne fait que montrer qu'il y a des différences dans la façon de formuler, sans qu'on puisse, sauf quelques cas particuliers (et on en trouvera dans les deux sens), induire qu'une modalité de formulation est supérieure à une autre...

Cordialement et bonne journée.

Braxton
 Posté le 06/05/2017 à 05:22 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petit astucien

Bonjour Anonyme et les autres,

Excel est certainement plus intéressant pour les spécialistes. Moi, je me contente d'un produit "blanc" qui me satisfait et qui permet de lire ou sauvegarder le format Excel.

En relisant les posts ci-dessus, vous comprendrez pourquoi j'ai dit que le CLASSEUR de Open Office était plus commode, d'autant plus qu'il reconnaît les dates du 18ème siècle et les autres... contrairement à Excel... ( voir Ferrand "... Excel ne reconnaît pas de date avant 1900..." ).

Avec mon CLASSEUR

Mais il ne tient pas compte du passage du calendrier julien au grégorien. Donc, à programmer soi-même si on veut calculer le nombre exact de jours entre deux dates, l'une julienne et l'autre grégorienne. Exercice qui n'intéresse que peu de monde sans doute...

Voilà, maintenant je ... car chacun et chaque programme a ses qualités et défauts.

Salutations.

ferrand
 Posté le 06/05/2017 à 16:13 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Salut Braxton,

Avec mon CLASSEUR

Comment as-tu établi qu'il s'agissait respectivement d'un lundi et d'un samedi ?

Cordialement.

Braxton
 Posté le 07/05/2017 à 01:59 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petit astucien

Bonjour,

Simplement comme ci-dessous

ferrand
 Posté le 07/05/2017 à 04:43 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

C'est ce qui me chagrinait ... car mon calculateur m'a indiqué que ces jours étaient les bons, ce qui n'aurait pas dû être le cas pour une série-dates utilisant le calendrier grégorien...

J'ai donc fait une vérification en VBA, qui me renvoie en effet un jeudi et un mardi, décalage de 4 jours correspondant au décalage Julien-(Grégorien) [en projection] du 29 février 700 au 28 février 900 [dates juliennes]. Il fallait donc qu'il y ait une erreur quelque part ou bien que Calc bascule en calendrier Julien !

C'est cette dernière hypothèse qui apparaît finalement être la bonne ! Bien cachée car pour en trouver trace, pas facile. J'ai fouillé toute la doc de Calc sur les dates sans rien trouver, et il a fallu en arriver à de la doc Wiki sur OpenOffice pour voir l'explication apparaître...

https://wiki.documentfoundation.org/FR/Calc:_Fonctions_date_heure

Selon ce document, Calc fonctionne en calendrier Grégorien à partir du 15 octobre 1582 et en calendrier Julien pour les dates antérieures.

Mais en conservant la série continue de nombres-dates du calendrier grégorien. Il opère donc par une conversion d'affichage (j'ai procédé de façon analogue dans la programmation de mon caculateur, ce qui permet d'avoir la validité du jour de semaine...)

C'est assez facile à vérifier : si tu tapes par exemple en A1 la date du 15/10/1582, en formatant la date pour faire apparaître qu'il s'agit d'un vendredi, tu tapes en A2 : =A1-1, tu devrais obtenir la date du jeudi 04/10/1582 !

C'est un bon point pour Calc en matière de dates, mais quelque peu annulé par sa non-divulgation !

Il reste encore à pouvoir paramétrer des options pour adapter cette conversion aux dates de passage variables selon les pays, mais il faut reconnaître que c'est un plus... (Cela ne me fera pas basculer car VBA me manquerait... )

Cordialement.

Braxton
 Posté le 07/05/2017 à 16:29 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
Petit astucien

Explications très intéressantes de Ferrand.
Pour les puristes voir Wikipedia
https://fr.wikipedia.org/wiki/Passage_du_calendrier_julien_au_calendrier_grégorien
car le passage du julien au grégorien ne s'est pas produit à la même date dans tous les pays.
CALC, le 'classeur' que j'utilise ,privilégie l'Espagne, l'Italie, le Portugal, les Etats Pontificaux, ... puisqu'il passe du 4/10/1582 au lendemain 15/10/1582.
Pour les années avant J-C, CALC les interprète erronément si on les tape directement dans la cellule. Une solution est de partir d'une date bien interprétée et d'effectuer une soustraction sur cette date pour afficher qque chose ... qui n'indique pas qu'on est AVANT J-C...
Une autre bizarrerie : Entrez ' 10/3/101' en B2 et l'affichage est '09/03/01' ou en modifiant la présentation ' mardi 9 mars 101 '. Puis voir ci-après...

mardi 9 mars 101
-36958 jeudi 1 janvier 1
-1 mercredi 31 décembre 2
-1 mardi 30 décembre 2
-1 lundi 29 décembre 2
-1 dimanche 28 décembre 2
-1 samedi 27 décembre 2

Maintenant que CALC montre ses limites, Anonyme reprend du poil de la bête ...
Cordialement.

ferrand
 Posté le 08/05/2017 à 16:00 
Aller en bas de la page Revenir au message précédent Revenir en haut de la page
  Astucien

Bonjour,

,privilégie l'Espagne, l'Italie, le Portugal, les Etats Pontificaux, ...

Ne pas oublier la Savoie ! à laquelle le Comté de Nice était rattaché !

Publicité
Pages : [1] 2 ... Fin
Page 1 sur 2 [Fin]

Vous devez être connecté pour poster des messages. Cliquez ici pour vous identifier.

Vous n'avez pas de compte ? Créez-en un gratuitement !


Les bons plans du moment PC Astuces

Tous les Bons Plans
147,99 €Disque dur externe Western Digital My Book 8 To USB 3.0 à 147,99 €
Valable jusqu'au 02 Décembre

Amazon propose actuellement le disque dur externe Western Digital My Book 8 To USB 3.0 à 147,99 € livré gratuitement. On le trouve ailleurs à partir de 190 €. Grâce à la connectique USB 3.0 (compatible USB 2.0), ce disque dur vous offrira d'excellents débits pour vos transferts et vos sauvegardes. Pour info, le disque dur n'est pas soudé et vous pouvez le récupérer pour le mettre dans un PC.


> Voir l'offre
54,95 €SSD Crucial P2 500 Go (3D NAND, NVMe, PCIe, M.2, 2400 Mo/s) à 54,95 €
Valable jusqu'au 30 Novembre

Materiel.net fait une promotion sur le SSD Crucial P2 500 Go (3D NAND, NVMe, PCIe, M.2) qui passe à 54,95 €.  On le trouve ailleurs à partir de 70 €. Ce SSD offre des vitesses de lecture/écriture séquentielle allant jusqu’à 2 400/ 1 900 Mo/s. Il est garanti 5 ans.


> Voir l'offre
99,99 €Disque dur externe portable Western Digital Elements USB 3.0 5 To à 99,99 €
Valable jusqu'au 02 Décembre

La Fnac propose actuellement le disque dur externe portable Western Digital Elements USB 3.0 5 To à 99,99 €. On le trouve habituellement à partir de 119 €. Ce disque dur qui tient dans la main est silencieux, ne chauffe pas et dispose d'une connectique USB 3.0 qui vous offrira des transferts rapides. Il est compatible USB 2.0. Si vous préférez, il est également disponible chez Darty au même prix et Amazon semble également s'être aligné sur le prix. Une très bonne affaire.


> Voir l'offre

Sujets relatifs
excel 2013, déplacement de cellules
Office Etudiant 2013 Excel, problème avec mes cellules
[Excel 2013] Fusion personnalisée de cellules
Formule pour Excel SVP
formule Excel liste sans doublons avec condition supplémentaire
Excel formule matricielle
Formule Excel
REcherche d'une formule EXCEL
cellules sélectionnées à tort excel 2007
formule avec excel
Plus de sujets relatifs à excel 2013, formule de cellules
 > Tous les forums > Forum Bureautique