.NumberFormat parfois renvoie la valeur faux avec les dates et les heures

Il semble que chaque semaine ou si quelqu'un poste une question sur les dates converti (corrompu?) de format Américain. Comme beaucoup d'autres, j'ai tenté de l'aider mais le problème est difficile à cerner. Je me demande maintenant si j'ai découvert la cause.

Je suis en train de travailler sur une application dans laquelle j'ai besoin d'extraire des données à partir d'une feuille de calcul Excel et de sortie comme les chaînes de format correspond à la valeur de l'utilisateur Excel pouvez le voir. Donc, si la valeur est “1” au format à l'écran comme suit: “1.00” je veux la chaîne à “1.00”.

Je obtenir cet effet en testant la valeur de la cellule à un nombre, une date ou de l'heure. Si elle l'est, j'ai récupérer le format de nombre et de l'utiliser pour le format de la valeur de la cellule afin de:

With .Cells(Row, Column) 
  Output string = Format(.Value, .NumberFormat)
End With

Dans la plupart des cas, cela me donne exactement la sortie j'ai besoin. Cependant, j'ai parfois l'Américain dates et les heures où la source est formaté comme un royaume-UNI date ou de l'heure.

Après beaucoup d'expérimentation avec Excel 2003 et Excel 2007, j'ai découvert la cause. (Je n'ai pas accès à Excel 2010, mais à partir de questions-je en déduire qu'il a le même problème.) Cette question est en partie destiné à révéler ce problème pour le monde parce que je peux découvrir sur internet, rien n'indiquer que quelqu'un d'autre a remarqué. (Pas de doute que quelqu'un va répondre qu'ils googlé “xyz” et a obtenu la réponse tout de suite.) Toutefois, le principal objectif de cette question est de solliciter des suggestions pour obtenir le résultat dont j'ai besoin dans toutes les situations.

Généralement, je entrez des dates comme, par exemple, “23mar12”. Excel reconnaît ce qu'une date et formats comme “23-Mar-12”. Je peux sélectionner Format Cells et de saisir ou de sélectionner un format personnalisé ou sélectionnez l'un des formats de date, de sorte que je peux avoir n'importe quel format je peux imaginer vouloir y compris les non-anglais des noms des jours et des mois.

Cependant, dans un cas, sélectionnez le format que je n'est pas le format qui est enregistrée: Personnalisé format “dd/mm/yyyy” est enregistré en tant que format de la Date “* 14/03/2001”. Ce n'est évidemment pas un problème jusqu'à ce que plus loin sur la ligne.

J'ai créé une colonne de dates et d'horaires et formaté chacun avec un standard ou personnalisée format. J'ai écrit une macro pour extraire les NumberFormat pour chacune de ces dates et heures ainsi que de l'écrire comme une chaîne de caractères à une colonne adjacente. J'ai aussi formaté la valeur à l'aide du format de nombre et écrit la chaîne troisième colonne.

Dans un certain nombre de cas, le format sélectionné et enregistré par Excel n'était pas le format retourné par NumberFormat:

Excel format            NumberFormat
Date: * 14/03/2001       m/d/yyyy
Date: * 14 March 2001   [$-F800]dddd, mmmm dd, yyyy
Date: 14/03/2001        dd/mm/yyyy;@
Date: 14/03/01          dd/mm/yy;@
Date: 14/3/01           d/m/yy;@
Date: 14.3.01           d.m.yy;@
Date: 2001-03-14        yyyy-mm-dd;@
Date: 14 March 2001 (1) [$-809]dd mmmm yyyy;@
Date: 14 March 2001 (2) [$-809]d mmmm yyyy;@
Custom: hh:mm:ss        h:mm:ss
Time: * 13:30:55        [$-F400]h:mm:ss AM/PM
Time: 13:30:55 (1)      hh:mm:ss;@
Time: 13:30:55 (2)      h:mm:ss;@
Time: 01:30:55 PM       [$-409]hh:mm:ss AM/PM;@
Time: 1:30:55 PM        [$-409]h:mm:ss AM/PM;@

Les valeurs (1) et (2) dans le format Excel colonne ont été ajoutés par moi, pour indiquer qu'il y a deux apparemment identiques formats. Comme peut être vu à partir de la NumberFormat colonne, dans chaque cas, la deuxième version supprime un zéro.

La plupart des modifications n'ont pas d'effet important. “[$-F800]” et ainsi de suite sont apparemment des valeurs fictives qui n'ont aucun effet. Apparemment, vous pouvez remplacer “F800” avec un Microsoft country code pour avoir les noms de jours et de mois traduit dans la langue de ce pays.

Cependant, les trois formats standards des marques de Microsoft avec un astérisque sont modifiées de façon inacceptable. Les dates sont modifiées par little endian, du moyen-endian; le temps est modifié à partir de 24 heure à 12 heure et le jour de la semaine a été ajouté à “* 14 Mars 2001”.

L'astérisque contre les dates, les références du commentaire:“, Sauf pour les éléments qui ont un astérisque () dans la liste Type (onglet Nombre, le Format des Cellules de la boîte de dialogue), les formats de date que vous appliquez ne passez pas la date de commandes avec le système d'exploitation.” L'astérisque contre le temps, les références du commentaire:“, Sauf pour les éléments qui ont un astérisque () dans la liste Type (onglet Nombre, le Format des Cellules de la boîte de dialogue), les formats de temps que vous appliquez ne pas passer commande avec le système d'exploitation.”

Si je dois le faire, je peux avertir mes utilisateurs standard formats de date et heure ne peut pas donner le résultat escompté. Cependant, si ils veulent le populaire format “jj/mm/aaaa”, ils ne peuvent pas l'avoir. “dd-mm-yyyy”, par exemple, est OK mais la coutume format “jj/mm/aaaa” devient le format de date “* 14/03/2001” devient “m/d/yyyy”.

De revenir à mon point: est-ce étrange de la manipulation d'un format de date la raison pour laquelle tant de nombreuses personnes affirment leurs dates sont parfois convertis en format Américain et est-ce pour cette raison que le problème est si insaisissable? J'ai rencontré ce type de problème ailleurs d'un groupe de Microsoft programmeurs de ne pas savoir ce que l'autre groupe sont en train de faire. Est-ce pour cette raison que certaines fonctions fonctionnent toujours et parfois d'autres ne le font pas? Microsoft certains programmeurs de savoir où chercher pour le mettre au bon format et d'autres pas?

Le plus important, pour moi, quelqu'un peut-il suggérer:

• Comment j'obtiens la vraie date ou de l'heure format?
• Une autre façon de déterminer l'utilisateur choisi le format d'affichage d'une date ou une heure?

BTW 1: je me souviens qu'une trentaine d'années m'a dit que l'armée Américaine n'utilisez pas de mois/jour/année; seuls les civils Américains utilisent ce format. Quelqu'un peut-il me dire si cela est vrai?

BTW 2: Le problème similaire se pose avec Excel couleurs. Excel tient ses couleurs "ggbbrr" alors que tout le monde retient que "rrvvbb". Les programmeurs pour la .Net Excel inter-op n'ont pas dit et et ne pas inverser les Excel couleur nombre avant de l'utiliser pour contrôler l'écran.

OriginalL'auteur Tony Dallimore | 2012-03-23