Qui est fou ? FMP ou Excel

[iluro_64]

Ça, c'est un sujet pour l'ami Aliboron (si ça l'amuse) …

Il y a quelques jours, j'ai sorti un extrait de base de données FileMaker Pro à destination d'Excel, en format Excel 1997-2004 (.xls) et aussi en format Excel 2008 (.xlsx). Puis, j'ai ouvert le(s) fichier(s) obtenu(s) dans les deux versions d'Excel. Pas de problème, les deux versions reconnaissent parfaitement leurs fichiers respectifs (et même plus dans le cas d'Excel 2008).

Parmi les données exportés, il y a une rubrique date qui sort en format texte sous la forme jj/mm/aa (ou jj/mm/aaaa, c'est pareil). Dans Excel,la reconnaissance se fait sans problème, et la date est affichée correctement avec les formats définis dans les styles.

Où ça se gâte c'est quand je fais l'une des deux opérations suivantes :

1• Dans le fichier issu de FM Pro et correctement lu par Excel, si je crée une feuille et que dans cette feuille par une formule de type "=LignexxColonneyy" j'appelle chaque date de la feuille qui les contient, j'obtiens un résultat tout à fait curieux et différent, c'est la date d'origine augmentée de 4 ans, et pour être tout à fait précis ça donne ceci :
Date originale = 31/08/2010, date reportée - 01/09/2014.

2•Si j'effectue la même opération par copier-coller de la colonne date originale dans la colonne date de la nouvelle feuille j'obtiens strictement la même chose.

C'est à la fois embêtant mais rectifiable, soit par une petite macro dans Excel 2004, soit en bidouillant une formule dans Excel 2008.

Je me demande si la raison de cette "correction forcée" et automatique ne vient pas du fait qu'Excel considère que les dates importées viennent du monde PC (origine 1/1/1900) et que comme il s'agit d'Excel pour Mac (origine 1/1/1904) il doit les corriger.

Qu'en pense les bons connaisseurs d'Excel ?

 

[Pascal 77]

Pour trouver l'origine de ton mystère, tu devrais plutôt aller voir dans les prefs d'Excel ce qu'il y a là, ça te donnera la clé du problème (en cochant ou décochant la case, c'est selon) !

 

[iluro_64]

Oui, je sais que les Préférences des deux Excel que j'utilise sont en date début 1904.
Mais cela ne résout pas mon problème car, si je laisse la case cochée (par défaut), les dates importées deviennent fausses, et si je la décoche, ce sont les dates existantes qui deviennent fausses lorsque les dates importées restent justes.

Bref, on tourne en rond

 

[Pascal 77]

Oui, je sais que les Préférences des deux Excel que j'utilise sont en date début 1904.
Mais cela ne résout pas mon problème car, si je laisse la case cochée (par défaut), les dates importées deviennent fausses, et si je la décoche, ce sont les dates existantes qui deviennent fausses lorsque les dates importées restent justes.

Bref, on tourne en rond

Alors, il te faut repartir sur des bases saines, c'est à dire prendre la même date d'origine pour Excel et FM avant d'importer les données FM dans Excel ! Pour effectuer tes corrections, le nombre magique est 1461. Par exemple (dans Excel), "1/3/1953-1461=1/3/1949" et "1/3/1953+1461=1/3/1957".

 

[Aliboron]

Comme l'a très bien exposé Pascal77, ce n'est pas une question de "folie" mais uniquement une question de paramétrage. Avec Excel 2004, on peut même contourner ça élégamment grâce à une macro complémentaire "DateXlaFr" de Guillôme, à télécharger par ici., Evidement, ça ne peut pas marcher avec Excel 2008 !!!

 

[iluro_64]

Comme l'a très bien exposé Pascal77, ce n'est pas une question de "folie" mais uniquement une question de paramétrage. Avec Excel 2004, on peut même contourner ça élégamment grâce à une macro complémentaire "DateXlaFr" de Guillôme, à télécharger par ici., Evidement, ça ne peut pas marcher avec Excel 2008 !!!

Je constate que nous sommes bien tous sur la même longueur d'onde, et je conçois que le paramétrage des préférences est un des solutions possibles. Toutefois, et je ne sais pas depuis quand existe cette possibilité, cela me pose un problème, facile à résoudre, heureusement. En effet, dans les fichiers Excel qui remontent à l'antiquité, et qui concernent essentiellement toute ma comptabilité, la date est par défaut, et fondement historique, calée sur l'année 1904. C'est donc la macro qui va remette de l'ordre en calant la date sur l'année 1900. Heureusement que tous les fichiers concernés (depuis 1979 …) sont toujours en format Excel 1997-2004, et que l'opération est bien ciblée, et facile à programmer si celle qui m'est proposée ne me convient pas..

Toutefois, si vous avez la réponse, cela comblera ma curiosité, pourquoi les dates importées ne sont-elles pas affectées par la correction automatique ? Merci d'avance … Et merci d'avoir répondu …

 

[Pascal 77]

Toutefois, et je ne sais pas depuis quand existe cette possibilité

Depuis l'apparition de la version "Mac" de multiplan, ça tient au fait que sur les premiers Mac, la date d'origine (voir explication plus bas) était le 1/1/1904, alors que les PC (sous DOS à l'époque), eux remontaient au 1/1/1900.

Toutefois, si vous avez la réponse, cela comblera ma curiosité, pourquoi les dates importées ne sont-elles pas affectées par la correction automatique ? Merci d'avance … Et merci d'avoir répondu …

Ça tient au fait que, que ça soit pour Excel ou FM, les dates n'existent pas, elles ne sont qu'une convention d'affichage, ce sont en fait des nombres comme les autres, et le 1401 magique dont je te parlais, c'est le nombre qui représente le 1/1/1904 dans la notation à origine 1/1/1900. Ce nombre est tout simplement le nombre de jours écoulé entre la date notée et la date d'origine retenue, donc, par exemple, lorsque tu crois importer le 18/6/2010, FM Pro transmet en réalité le nombre 40286 à Excel, or, si ce dernier est en "origine 1904" et FM Pro en "origine 1900", ce nombre est traduit par Excel comme étant le 18/6/2014 car pour lui, le 18/6/2010, c'est 38885.

Détail amusant, si tu ajoutes une partie décimale à ces nombres, tu obtiens une "date et heure", par exemple en "origine 1904", 38885,66666667, c'est le 18/6/2010 à 16 heures.

Si tu veux toucher ça du doigt, dans Excel, saisis une date dans une cellule, puis fais "format cellule -> nombre" sur ta date, et le N° de série de la datte en question s'affiche, avec les décimales si tu as ajouté une heure (tu peux aussi taper un nombre au hasard, et faire "format cellule -> date et heure").

 

[iluro_64]

Depuis l'apparition de la version "Mac" de multiplan, ça tient au fait que sur les premiers Mac, la date d'origine (voir explication plus bas) était le 1/1/1904, alors que les PC (sous DOS à l'époque), eux remontaient au 1/1/1900.



Ça tient au fait que, que ça soit pour Excel ou FM, les dates n'existent pas, elles ne sont qu'une convention d'affichage, ce sont en fait des nombres comme les autres, et le 1401 magique dont je te parlais, c'est le nombre qui représente le 1/1/1904 dans la notation à origine 1/1/1900. Ce nombre est tout simplement le nombre de jours écoulé entre la date notée et la date d'origine retenue, donc, par exemple, lorsque tu crois importer le 18/6/2010, FM Pro transmet en réalité le nombre 40286 à Excel, or, si ce dernier est en "origine 1904" et FM Pro en "origine 1900", ce nombre est traduit par Excel comme étant le 18/6/2014 car pour lui, le 18/6/2010, c'est 38885.

Détail amusant, si tu ajoutes une partie décimale à ces nombres, tu obtiens une "date et heure", par exemple en "origine 1904", 38885,66666667, c'est le 18/6/2010 à 16 heures.

Si tu veux toucher ça du doigt, dans Excel, saisis une date dans une cellule, puis fais "format cellule -> nombre" sur ta date, et le N° de série de la datte en question s'affiche, avec les décimales si tu as ajouté une heure (tu peux aussi taper un nombre au hasard, et faire "format cellule -> date et heure").

Oui, j'ai connaissance de ces conventions, et c'est grâce à elle que les calculs sur les dates sont faciles à mettre en ouvre puisque qu'ils ne se font pas sur les dates affichées mais sur les nombres représentant les dates à partir d'une origine. Ainsi pour FMP, il s'agit de l'an 1 si mes souvenirs sont exacts.

Mais cela ne réponds pas bien à ma question qui est :

Pourquoi les dates importées dans la colonne A demeurent-elles des textes tandis que celle copier-coller dans la colonne B deviennent-elles des dates alors qu'on copie des textes ?

Pour moi, cela signifie qu'Excel, lors de la copie, interprète le texte comme un affichage de date, le transforme en nombre conventionnel de date, et gère ensuite le décalage éventuel du à la préférence cochée. Tout cela me semble "absurde" dans la mesure où la date d'origine étant un texte, une chaîne de caractères, et non le nombre conventionnel de FMP, la logique voudrait qu'on n'y touche pas, quelque soit la convention d'Excel : le 31/08/2010 devrait rester le même, c'est-à-dire une chaîne de caractère dans la mesure on ne déclare pas qu'il s'agit d'une date. Que la transformation se fasse ensuite sur déclaration de date, alors, d'accord.

 

[Aliboron]

Pour moi, cela signifie qu'Excel, lors de la copie, interprète le texte comme un affichage de date, le transforme en nombre conventionnel de date, et gère ensuite le décalage éventuel du à la préférence cochée. Tout cela me semble "absurde" dans la mesure où la date d'origine étant un texte, une chaîne de caractères, et non le nombre conventionnel de FMP, la logique voudrait qu'on n'y touche pas, quelque soit la convention d'Excel : le 31/08/2010 devrait rester le même, c'est-à-dire une chaîne de caractère dans la mesure on ne déclare pas qu'il s'agit d'une date. Que la transformation se fasse ensuite sur déclaration de date, alors, d'accord.

Oui, c'est bien ce qui se passe, de toute évidence. Je ne connais pas précisément les fichiers FileMaker mais, en règle générale, ce type de fichier est au format texte tabulé (ou avec des points-virgules, voire des virgules comme séparateurs). Et c'est au moment de l'import dans Excel qu'il y a conversion : lorsque le texte est identifié comme une date possible, il y a conversion en fonction du choix fait dans les préférences, 1900 ou 1904.

On peut bien entendu garder la colonne au format texte (dans le dernier volet de l'assistant d'import, on détermine le format de chacune des colonnes) mais l'intérêt n'en est pas forcément évident.

La principale difficulté avec ces deux formats de date est que c'est un paramétrage qui est mémorisé pour chaque classeur et que le copier/coller d'un classeur à l'autre tourne vite au casse-tête. C'est là qu'interviennent les recettes indiquées plus haut (ajout (ou soustraction) des 1462 jours "manquants" au coup par coup ou fonction macro)...

 

[iluro_64]

Merci à tous les deux ! Je clos le débat !