Abra una hoja de cálculo, haga clic en una celda que muestre 2026-06-19 y la barra de fórmulas aún mostrará una fecha. Lea la misma celda desde Delphi y obtendrá el número 46192. Ambas vistas son correctas, porque Excel nunca almacenó una fecha en esa celda. Almacenó un número de serie, un recuento de días, y adjuntó un formato de número que le dice a la pantalla que represente el recuento como una fecha de calendario. No hay un tipo de fecha en el valor de la celda. Hay un número y una regla de visualización, y la regla de visualización es lo único que distingue una fecha de una simple cantidad
Esa separación es la raíz de cada error de fecha que una biblioteca de hojas de cálculo tiene que esquivar. Un número de serie por sí solo no dice qué día es, porque no dice cuál fue el día cero. El mismo número significa dos fechas con cuatro años de diferencia dependiendo de una sola bandera en el libro de trabajo. Y un número que debería leerse como una fecha se leerá como una simple cantidad a menos que algo inspeccione su formato y reconozca un patrón de fecha. Así es como está construido el modelo de fechas en HotXLS, y por qué tiene que ser así
Una celda de fecha es un número más un formato
Excel almacena una fecha como el número de días desde una época, con la hora del día en la parte fraccionaria. El mediodía en un número de serie lleva .5. La parte entera es el recuento de días. Nada en el valor almacenado lo marca como temporal. Lo que lo marca es el formato de número de la celda: ECMA-376 llama a esto un numFmt, y una celda cuyo código de formato describe un patrón de fecha u hora se muestra como una fecha. Si se quita el formato, la misma celda muestra un número; el valor subyacente nunca cambió
Es por eso que leer el valor de una celda le da un Variant que puede ser un varDate o puede ser un Double simple, y por qué el formato de número en la misma celda es la señal que decide qué quiso decir un tercero. Cuando HotXLS abre un archivo XLSX, una celda lleva tanto su Value como su NumberFormatIndex hacia TXLSXCell, y el índice de formato es lo que usted consulta para saber si el número es una fecha
var
Book: TXLSXWorkbook;
Cell: TXLSXCell;
begin
Book := TXLSXWorkbook.Create;
try
if Book.Open('timesheet.xlsx') <> 1 then
raise Exception.Create('Cannot open workbook');
Cell := Book.Sheets[0].Cells[1, 1]; // fila 1, col 1 (basado en 1)
// Value puede llegar como varDate o como un número de serie simple;
// el índice de formato es la señal que los distingue
Writeln('raw value : ', VarToStr(Cell.Value));
Writeln('numFmt idx: ', Cell.NumberFormatIndex);
Writeln('format : ', Cell.NumberFormat);
finally
Book.Free;
end;
end;Dos épocas, con 1462 días de diferencia
El sistema de fechas predeterminado, el que usa todo libro de trabajo de Windows, cuenta desde el final de 1899, de modo que el número de serie 1 cae en el primer día de 1900. El otro sistema se remonta a los primeros Macintosh y cuenta desde el inicio de 1904, por lo que su número de serie 1 es cuatro años y un día después. Un libro de trabajo registra qué sistema usa en una bandera. En un paquete OOXML esa bandera es date1904 en la parte del libro; HotXLS la expone como la propiedad Date1904 del libro de trabajo
La brecha entre las dos épocas es exactamente de 1462 días. Eso es cuatro años calendario, tres de 365 días y uno de 366, sumando 1461, más uno adicional por el desfase de un día y pico entre las convenciones del día cero de ambos sistemas. El número es fijo y se puede memorizar. Su importancia radica en que no es cero. Un número de serie copiado de un libro de 1904 e interpretado bajo las reglas de 1900, o a la inversa, desplaza cada fecha en 1462 días, lo que se presenta como fechas que están erradas por poco más de cuatro años y es fácil confundir con datos corruptos
Dado que el propio TDateTime de Delphi está anclado a la convención de 1900, una biblioteca que asigne números de serie de Excel a TDateTime tiene que compensar por 1462 en ambas direcciones cada vez que el libro esté marcado con 1904. Al leer un número de serie de 1904, reste 1462 antes de tratarlo como un TDateTime; al escribir un TDateTime en un libro de 1904, reste 1462 del número de serie para que Excel muestre el día que usted pretendía. HotXLS aplica este cambio internamente al serializar valores de fecha para un libro de trabajo que tiene configurado Date1904, para que el valor que asigne como un TDateTime complete su ciclo al mismo día calendario en la pantalla
La peculiaridad deliberada del año bisiesto de 1900
Hay una famosa particularidad en el sistema de 1900. Excel trata a 1900 como un año bisiesto y acepta el 29 de febrero de 1900 como una fecha real, el número de serie 60. El año 1900 no fue bisiesto, porque los años de fin de siglo son bisiestos solo cuando son divisibles por 400, y 1900 no lo es. El día fantasma es un comportamiento de compatibilidad deliberado heredado de una de las primeras hojas de cálculo que se lanzó con el error, mantenido desde entonces para que la aritmética de los números de serie se mantenga idéntica a lo largo de décadas de archivos
La consecuencia práctica es pequeña pero real: para cualquier fecha a partir del 1 de marzo de 1900, el número de serie es uno mayor de lo que daría un conteo de días estrictamente correcto, porque el inexistente 29 de febrero consumió un número. Una biblioteca de hojas de cálculo reproduce la anomalía en lugar de arreglarla, porque coincidir con la aritmética de Excel exactamente es todo el trabajo. Corregirlo desfasaría cada fecha moderna en un día con respecto a lo que muestra Excel, lo que es un resultado peor que arrastrar un desfase de uno de hace cuarenta mil días que ninguna fecha real en uso comercial toca jamás. El sistema de 1904 no tiene un día fantasma equivalente, que es una de las razones por las que algunos lugares de trabajo lo prefirieron históricamente
Detectar una fecha a partir de numFmt
Cuando llega un número de un archivo que escribió otra persona, su formato es la única evidencia de que es una fecha. ECMA-376 asigna un bloque de identificadores de formato integrados cuyo significado está fijado por la especificación, y los formatos de fecha y hora ocupan rangos conocidos. Los ID del 14 al 22 son los formatos de fecha y hora de configuración regional general, los conocidos m/d/yyyy, h:mm y sus parientes. Los ID del 45 al 47 son los formatos de tiempo transcurrido. Otras dos bandas, del 27 al 36 y del 50 al 58, son los formatos de fecha y hora específicos de la configuración regional utilizados para los calendarios CJK, definidos en ECMA-376 18.8.30. Una celda cuyo ID de formato de número cae en cualquiera de estos rangos es una celda de fecha u hora
Los ID integrados cubren los casos comunes pero no los personalizados. Cuando un libro de trabajo define su propio código de formato, digamos un orden no estándar o un nombre de mes localizado, el ID está por encima del rango integrado y apunta a la tabla de formatos de número del libro de trabajo. Para esos casos, reconocer una fecha significa leer la cadena de código de formato y buscar tokens de fecha. HotXLS junta ambas comprobaciones en un predicado interno, XlsxNumFmtIsDate, que devuelve verdadero de inmediato para los rangos de fechas integrados y de lo contrario analiza el código de formato personalizado a través de XlsxFormatCodeIsDate. El lado público de esto es la cadena NumberFormat de la celda y su NumberFormatIndex, que le brindan tanto el código de formato resuelto como el ID para probar
Por qué el analizador de formato no puede simplemente escanear d y m
Analizar un código de formato en busca de tokens de fecha parece trivial hasta que se recuerda qué más vive en un formato de número. Una búsqueda ingenua de las letras que componen las fechas, la d, m, y, h y s de día, mes, año, hora y segundo, fallará en dos estructuras que no son tokens de fecha en absoluto
El primero es el literal de cadena entre comillas. Un formato de número puede incrustar texto literal entre comillas dobles, por lo que un formato financiero como #,##0 "MM" agrega los caracteres M y M a un número sin ningún significado temporal. Un escáner que cuente las letras dentro de las comillas como tokens de mes marcaría erróneamente ese formato de moneda como una fecha. La segunda es la sección de corchetes. Los formatos de número llevan directivas entre corchetes, nombres de colores como [Red], condiciones de comparación como [>1000], etiquetas de configuración regional y los marcadores de tiempo transcurrido [h] y [mm]. Algunos contenidos entre corchetes contienen letras de fecha y otros no, y tratar el texto entre corchetes igual que el cuerpo del formato genera tanto falsos positivos como casos omitidos
El analizador correcto recorre el código de formato carácter por carácter, registrando si está dentro de un literal entre comillas y qué tan profundo está dentro del anidamiento de corchetes, y también respeta el escape de barra invertida que cita un único carácter siguiente. Solo una letra de fecha sin escape que se encuentre fuera de cualquier literal de cadena y fuera de cualquier sección de corchetes cuenta como un token de fecha real. Así es exactamente como escanea XlsxFormatCodeIsDate: una comilla alterna un estado de dentro de literal que suprime la detección de tokens hasta la comilla de cierre, una barra invertida salta el siguiente carácter y un contador de profundidad de corchetes suprime la detección dentro de tramos de [...]. La recompensa es que #,##0 "MM" se lee correctamente como un formato de número, mientras que un código personalizado conciso que no contiene nada más que una sola m o d fuera de comillas sigue siendo reconocido correctamente como una fecha
Leer fechas de archivos de terceros
Todo lo anterior converge en un flujo de trabajo: convertir un número que escribió otra aplicación de vuelta en una fecha en la que se pueda confiar. El número de serie le da el recuento de días, la bandera Date1904 del libro de trabajo le indica desde qué época se mide el recuento y el ID del formato de número de la celda o el código personalizado es la única prueba de que el número estaba destinado a ser una fecha en primer lugar. Si omite cualquiera de los tres, obtendrá una respuesta incorrecta plausible en lugar de un error visible
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Cell: TXLSXCell;
r: Integer;
begin
Book := TXLSXWorkbook.Create;
try
if Book.Open('vendor-export.xlsx') <> 1 then
raise Exception.Create('Cannot open export');
// La bandera 1904 se aplica a todo el libro: léala una vez y aplíquela a
// cada número de serie que el libro devuelva
if Book.Date1904 then
Writeln('el libro de trabajo usa el sistema de fechas de 1904')
else
Writeln('el libro de trabajo usa el sistema de fechas de 1900');
Sheet := Book.Sheets[0];
for r := 1 to 10 do
begin
Cell := Sheet.Cells[r, 1];
// Una fecha es una fecha solo cuando su formato lo dice; el mismo valor numérico
// con un formato simple es solo una cantidad
Writeln(Format('row %d value=%s numFmt=%d code="%s"',
[r, VarToStr(Cell.Value), Cell.NumberFormatIndex, Cell.NumberFormat]));
end;
finally
Book.Free;
end;
end;El lado del BIFF heredado tiene una trampa adicional que vale la pena mencionar. En un flujo .xls más antiguo, una secuencia de celdas numéricas adyacentes se puede empaquetar en un solo registro multicelda, el MULRK, que almacena varios valores con sus referencias de formato en una estructura. Las celdas de fecha almacenadas de esa manera no son menos fechas por estar empaquetadas, por lo que la misma prueba de ID de formato tiene que llegar al interior del registro multicelda y aplicarse por celda, y el desfase de 1904 aún rige a cada número de serie que produce. Un lector que solo inspecciona registros de números independientes, y omite los empaquetados, convertirá silenciosamente una columna de fechas en una columna de enteros
Mapear números de serie a TDateTime en la práctica
Una vez que la comprobación de formato confirma una fecha y se conoce la bandera Date1904, la conversión es mecánica. Un valor que HotXLS ya devuelve como varDate es un TDateTime que puede usar directamente. Un valor que llega como un simple Double, lo que sucede cuando la fuente escribió un número de serie sin un formato de fecha reconocido, se convierte leyéndolo como un recuento de días en el eje de 1900 y, para un libro de trabajo de 1904, restando primero el desfase de 1462 días para que las épocas se alineen. En sentido contrario, al asignar un TDateTime a una celda se almacena el número de serie con base en 1900, y HotXLS aplica el mismo desplazamiento de 1462 días al guardar cuando el libro está marcado con 1904, para que el archivo guardado muestre la fecha prevista en lugar de una desviada por cuatro años
Configure la bandera deliberadamente al generar un libro de trabajo. El valor predeterminado deja Date1904 en falso, lo que coincide con Excel para Windows y es casi siempre lo que se desea; establézcalo en verdadero solo cuando esté reproduciendo un libro de origen Mac o cuando un sistema posterior espere específicamente el eje de 1904. La única regla que evita toda la clase de errores de cuatro años es la coherencia: elija la época una vez por libro de trabajo, escriba cada fecha bajo esa regla y vuelva a leer cada número de serie bajo la bandera que el archivo tiene realmente
Las fechas son una columna en una historia más amplia sobre lo que realmente contiene una celda. La capa de metadatos vecina, el título y el autor y las marcas de tiempo que acompañan a la cuadrícula, se cubren en nuestro artículo sobre metadatos de libros y propiedades de documentos, donde los mismos valores Created y Modified se almacenan como TDateTime con la misma convención de que no establecido es igual a cero. Cuando una fecha es el resultado de un cálculo en lugar de un valor almacenado, las reglas de evaluación en nuestro artículo sobre el motor de fórmulas y funciones personalizadas determinan el número de serie que luego representa el formato. Ambos funcionan sobre el mismo modelo de fecha que se incluye en el componente HotXLS para Delphi y C++Builder, el cual lee y escribe fechas XLS y XLSX sin automatización de Excel