Parece que cada semana, más o menos, alguien publica una pregunta sobre las fechas que se están convirtiendo (¿corruptas?) Al formato estadounidense. Como muchos otros, he intentado ayudar pero el problema es difícil de alcanzar. Ahora me pregunto si he descubierto la causa.

Estoy trabajando en una aplicación en la que necesito extraer datos de una hoja de cálculo de Excel y generarlos como cadenas formateadas para que coincidan con el valor que el usuario de Excel puede ver. Entonces, si el valor es "1" formateado para mostrar como "1.00", entonces quiero que la cadena sea "1.00".

Logro este efecto probando que el valor de la celda sea un número, fecha u hora. Si es así, recupero el formato de número y lo uso para formatear el valor de la celda, entonces:

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

En la mayoría de los casos, esto me da exactamente la salida que necesito. Sin embargo, a veces obtengo fechas y horas estadounidenses cuando la fuente está formateada como una fecha u hora del Reino Unido.

Después de mucha experimentación con Excel 2003 y Excel 2007, descubrí la causa. (No tengo acceso a Excel 2010, pero de las preguntas deduzco que tiene el mismo problema). Esta pregunta está destinada en parte a revelar este problema al mundo porque no puedo descubrir nada en Internet que indique que alguien más lo haya notado. . (Sin duda, alguien responderá que buscaron en Google "xyz" y obtuvieron la respuesta de inmediato). Sin embargo, el objetivo principal de esta pregunta es buscar sugerencias para obtener el resultado que necesito en todas las situaciones.

Normalmente, introduzco fechas como, por ejemplo, "23mar12". Excel reconoce esto como una fecha y lo formatea como "23-mar-12". Puedo seleccionarFormat Cells e ingrese o seleccione un formato personalizado o seleccione uno de los formatos de fecha para poder tener cualquier formato que pueda imaginar querer incluir nombres que no estén en inglés por días y meses.

Sin embargo, en un caso, el formato que selecciono no es el formato que se graba: el formato personalizado “dd / mm / aaaa” se graba como formato de fecha “* 14/03/2001”. Esto obviamente no es un problema hasta más adelante.

Creé una columna de fechas y horas y formateé cada una con un formato personalizado o estándar diferente. Escribí una macro para extraer el NumberFormat para cada una de estas fechas y horas y escribirlo como una cadena en una columna adyacente. También formateé el valor usando el formato de número y escribí esa cadena en una tercera columna.

En varios casos, el formato seleccionado y registrado por Excel no era el formato devuelto por 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;@

Los valores (1) y (2) en la columna de formato de Excel fueron agregados por mí para indicar que hay dos formatos aparentemente idénticos. Como se puede ver en la columna NumberFormat, en cada caso la segunda versión suprime un cero inicial.

La mayoría de los cambios no tienen un efecto importante. "[$ -F800]" y así sucesivamente son valores ficticios aparentemente sin efecto. Aparentemente, puede reemplazar "F800" con un código de país de Microsoft para que los nombres de días y meses se traduzcan al idioma de ese país.

Sin embargo, los tres formatos estándar que Microsoft marca con un asterisco se cambian de manera inaceptable. Las fechas se cambian de little endian a middle endian; la hora cambia de 24 horas a 12 horas y el día de la semana se ha agregado a "* 14 de marzo de 2001".

El asterisco contra las fechas, hace referencia al comentario: "Excepto para los elementos que tienen un asterisco ) en la lista Tipo (pestaña Número, cuadro de diálogo Formato de celdas), los formatos de fecha que aplique no cambian los pedidos de fecha con el sistema operativo ". El asterisco contra la hora, hace referencia al comentario:" Excepto los elementos que tienen un asterisco ) en la lista Tipo (pestaña Número, cuadro de diálogo Formato de celdas), los formatos de hora que aplique no cambian las órdenes de tiempo con el sistema operativo ".

Si tengo que hacerlo, puedo advertir a mis usuarios que los formatos estándar de fecha y hora pueden no dar el resultado deseado. Sin embargo, si quieren el formato popular "dd / mm / aaaa", no pueden tenerlo. "Dd-mm-aaaa", por ejemplo, está bien, pero el formato personalizado "dd / mm / aaaa" se convierte en formato de fecha "* 14/03/2001" se convierte en "m / d / aaaa".

Volviendo a mi punto de partida: ¿es este manejo extraño de un formato de fecha en particular la razón por la que muchas personas afirman que sus fechas a veces se están convirtiendo al formato estadounidense y es por eso que el problema es tan difícil de resolver? Me he encontrado con este tipo de problema en otro lugar de un grupo de programadores de Microsoft que no saben lo que otro grupo está haciendo. ¿Es por eso que algunas funciones siempre funcionan y otras a veces no? Algunos programadores de Microsoft saben dónde buscar el formato correcto y otros no?

Lo que es más importante, para mí, ¿alguien puede sugerir:

¿Cómo obtengo el verdadero formato de fecha u hora? ¿Hay alguna otra forma de determinar el formato de visualización elegido por el usuario para una fecha u hora?

BTW 1: Recuerdo que hace unos treinta años me dijeron que el ejército estadounidense no usa el formato mes / día / año; solo los civiles estadounidenses usan este formato. ¿Alguien me puede decir si esto es cierto

BTW 2: El problema similar es con los colores de Excel. Excel mantiene sus colores como "ggbbrr" mientras que todos los demás los mantienen como "rrggbb". Los programadores para la operación .Net Excel no fueron informados y no invirtieron el número de color de Excel antes de usarlo para controlar la pantalla.