Pregunta Fecha de Excel a la marca de tiempo de Unix


¿Alguien sabe cómo convertir una fecha de Excel en una marca de tiempo de Unix correcta?


76
2017-11-09 20:21


origen


Respuestas:


Ninguno de estos funcionó para mí ... cuando convertí la marca de tiempo, faltan 4 años.

Esto funcionó perfectamente =(A2-DATE(1970,1,1))*86400

El crédito va a: Filip Czaja http://fczaja.blogspot.ca

Publicación original: http://fczaja.blogspot.ca/2011/06/convert-excel-date-into-timestamp.html


94
2018-06-21 14:57



Windows y Mac Excel (2011):

Unix Timestamp = (Excel Timestamp - 25569) * 86400
Excel Timestamp =  (Unix Timestamp / 86400) + 25569

MAC OS X (2007):

Unix Timestamp = (Excel Timestamp - 24107) * 86400
Excel Timestamp =  (Unix Timestamp / 86400) + 24107

Para referencia:

86400 = Seconds in a day
25569 = Days between 1970/01/01 and 1900/01/01 (min date in Windows Excel)
24107 = Days between 1970/01/01 and 1904/01/02 (min date in Mac Excel 2007)

64
2018-05-27 16:10



Si suponemos que la fecha en Excel está en la celda A1 formateada como Fecha y la marca de tiempo de Unix debe estar en una celda A2 formateada como número, la fórmula en A2 debe ser:

= (A1 * 86400) - 2209075200

dónde:

86400 es la cantidad de segundos en el día 2209075200 es la cantidad de segundos entre 1900-01-01 y 1970-01-01 que son las fechas base para las marcas de tiempo de Excel y Unix.

Lo anterior es cierto para Windows. En Mac, la fecha base en Excel es 1904-01-01 y el número de segundos debe corregirse a: 2082844800


11
2017-11-09 20:46



Aquí hay un mapeo de referencia, asumiendo UTC para sistemas de hoja de cálculo como Microsoft Excel:

                         Unix  Excel Mac    Excel    Human Date  Human Time
Excel Epoch       -2209075200      -1462        0    1900/01/00* 00:00:00 (local)
Excel ≤ 2011 Mac† -2082758400          0     1462    1904/12/31  00:00:00 (local)
Unix Epoch                  0      24107    25569    1970/01/01  00:00:00 UTC
Example Below      1234567890      38395.6  39857.6  2009/02/13  23:31:30 UTC
Signed Int Max     2147483648      51886    50424    2038/01/19  03:14:08 UTC

One Second                  1       0.0000115740…             —  00:00:01
One Hour                 3600       0.0416666666…             ―  01:00:00
One Day                 86400          1        1             ―  24:00:00

*"Jan Zero, 1900" es 1899/12/31; ver el Error la sección a continuación.  Excel 2011 para Mac (y anteriores) usa el Sistema de fecha 1904.

Como uso a menudo awk para procesar CSV y el contenido delimitado por espacios, desarrollé una forma de convertir UNIX epoch a zona horaria/DST-apropiado Formato de fecha de Excel:

echo 1234567890 |awk '{ 
  # tries GNU date, tries BSD date on failure
  cmd = sprintf("date -d@%d +%%z 2>/dev/null || date -jf %%s %d +%%z", $1, $1)
  cmd |getline tz                                # read in time-specific offset
  hours = substr(tz, 2, 2) + substr(tz, 4) / 60  # hours + minutes (hi, India)
  if (tz ~ /^-/) hours *= -1                     # offset direction (east/west)
  excel = $1/86400 + hours/24 + 25569            # as days, plus offset
  printf "%.9f\n", excel
}'

solía echo para este ejemplo, pero puede canalizar un archivo donde la primera columna (para la primera celda en formato .csv, llámelo como awk -F,) es una época UNIX. Alterar $1 para representar su número de columna / celda deseado o usar una variable en su lugar.

Esto hace que un sistema llame a date. Si tiene la versión de GNU confiablemente, puede eliminar 2>/dev/null || date … +%%z y el segundo , $1. Dado lo común que es GNU, no recomendaría suponer la versión de BSD.

los getline lee el desplazamiento de zona horaria generado por date +%z dentro tz, que luego se traduce a hours. El formato será como -0700 (PDT) o +0530 (IST), por lo que la primera subcadena extraída es 07 o 05, el segundo es 00 o 30 (luego dividido por 60 para ser expresado en horas), y el tercer uso de tz ve si nuestro offset es negativo y altera hours si es necesario.

La fórmula dada en todas las otras respuestas en esta página se usa para establecer excel, con la adición del ajuste de zona horaria de ahorro de luz diurna como hours/24.

Si tiene una versión anterior de Excel para Mac, deberá usar 24107 en lugar de 25569 (ver el mapeo arriba).

Para convertir cualquier hora arbitraria que no sea de época en tiempos de Excel con fecha GNU:

echo "last thursday" |awk '{ 
  cmd = sprintf("date -d \"%s\" +\"%%s %%z\"", $0)
  cmd |getline
  hours = substr($2, 2, 2) + substr($2, 4) / 60
  if ($2 ~ /^-/) hours *= -1
  excel = $1/86400 + hours/24 + 25569
  printf "%.9f\n", excel
}'

Este es básicamente el mismo código, pero el date -d ya no tiene una @ para representar la época de Unix (dado lo capaz que es el analizador de cadenas, estoy realmente sorprendido @es obligatorio; ¿Qué otro formato de fecha tiene 9-10 dígitos?) y ahora se solicita dos salidas: la época y el desplazamiento de la zona horaria. Por lo tanto, podría usar, p. @1234567890 como una entrada.

Error

Lotus 1-2-3 (el software original de la hoja de cálculo) intencionalmente tratado 1900 como un año bisiesto a pesar del hecho de que no lo era (esto redujo la base de código en un momento en que cada byte contaba). Microsoft Excel retenido este error de compatibilidad, omitiendo el día 60 (el ficticio 1900/02/29), conservando el mapeo de Lotus 1-2-3 del día 59 al 1900/02/28. En su lugar, LibreOffice asignó el día 60 a 1900/02/28 y empujó todos los días anteriores una vez más.

Cualquier fecha anterior al 1900/03/01 podría ser tanto como un día libre:

Day        Excel   LibreOffice
-1             —    1899/12/29
 0    1900/01/00*   1899/12/30
 1    1900/01/01    1899/12/31
 2    1900/01/02    1900/01/01
 …
59    1900/02/28    1900/02/27
60    1900/02/29(!) 1900/02/28
61    1900/03/01    1900/03/01

Excel no reconoce las fechas negativas y tiene una definición especial del Zeroth de enero para el día cero. Internamente, Excel maneja las fechas negativas (son solo números después de todo), pero no sabe cómo mostrarlas como fechas (ni puede convertir fechas antiguas en números negativos). El 29 de febrero de 1900, un día que nunca sucedió, es reconocido por Excel pero no por LibreOffice.


6
2017-09-15 05:01



Debido a que mis ediciones a lo anterior fueron rechazadas (¿alguno de ustedes realmente lo intentó?), Esto es lo que realmente necesita para que esto funcione:

Windows (y Mac Office 2011+):

  • Marca de tiempo de Unix = (Excel Timestamp - 25569) * 86400
  • Timestamp de Excel = (Unix Timestamp / 86400) + 25569

MAC OS X (pre Office 2011):

  • Marca de tiempo de Unix = (Excel Timestamp - 24107) * 86400
  • Timestamp de Excel = (Unix Timestamp / 86400) + 24107

3
2018-02-13 16:11



Aparentemente estás fuera por un día, exactamente 86400 segundos. Use el número 2209161600 No es el número 2209075200 Si busca los dos números en Google, encontrará soporte para lo anterior. Probé tu fórmula, pero siempre surgió un día diferente de mi servidor. No es obvio desde la marca de tiempo de Unix a menos que piense en Unix en lugar de en tiempo humano ;-) pero si lo comprueba dos veces, verá que esto podría ser correcto.


2
2018-03-23 14:43



Ninguna de las respuestas actuales funcionó para mí porque mis datos estaban en este formato desde el lado de Unix:

2016-02-02 19:21:42 UTC

Necesitaba convertir esto a Epoch para permitir referenciar otros datos que tenían marcas de tiempo de época.

  1. Crea una nueva columna para la parte de la fecha y analiza con esta fórmula

    =DATEVALUE(MID(A2,6,2) & "/" & MID(A2,9,2) & "/" & MID(A2,1,4)) 
    
  2. Como ya ha indicado Grendler aquí, crea otra columna

    =(B2-DATE(1970,1,1))*86400 
    
  3. Cree otra columna con solo el tiempo agregado para obtener segundos totales:

    =(VALUE(MID(A2,12,2))*60*60+VALUE(MID(A2,15,2))*60+VALUE(MID(A2,18,2)))
    
  4. Cree una última columna que solo agregue las dos últimas columnas juntas:

    =C2+D2
    

0
2018-02-03 20:17



Aquí está mi última respuesta a esto.

También aparentemente javascript new Date(year, month, day) el constructor tampoco cuenta los segundos intercalares.

// Parses an Excel Date ("serial") into a
// corresponding javascript Date in UTC+0 timezone.
//
// Doesn't account for leap seconds.
// Therefore is not 100% correct.
// But will do, I guess, since we're
// not doing rocket science here.
//
// https://www.pcworld.com/article/3063622/software/mastering-excel-date-time-serial-numbers-networkdays-datevalue-and-more.html
// "If you need to calculate dates in your spreadsheets,
//  Excel uses its own unique system, which it calls Serial Numbers".
//
lib.parseExcelDate = function (excelSerialDate) {
  // "Excel serial date" is just
  // the count of days since `01/01/1900`
  // (seems that it may be even fractional).
  //
  // The count of days elapsed
  // since `01/01/1900` (Excel epoch)
  // till `01/01/1970` (Unix epoch).
  // Accounts for leap years
  // (19 of them, yielding 19 extra days).
  const daysBeforeUnixEpoch = 70 * 365 + 19;

  // An hour, approximately, because a minute
  // may be longer than 60 seconds, see "leap seconds".
  const hour = 60 * 60 * 1000;

  // "In the 1900 system, the serial number 1 represents January 1, 1900, 12:00:00 a.m.
  //  while the number 0 represents the fictitious date January 0, 1900".
  // These extra 12 hours are a hack to make things
  // a little bit less weird when rendering parsed dates.
  // E.g. if a date `Jan 1st, 2017` gets parsed as
  // `Jan 1st, 2017, 00:00 UTC` then when displayed in the US
  // it would show up as `Dec 31st, 2016, 19:00 UTC-05` (Austin, Texas).
  // That would be weird for a website user.
  // Therefore this extra 12-hour padding is added
  // to compensate for the most weird cases like this
  // (doesn't solve all of them, but most of them).
  // And if you ask what about -12/+12 border then
  // the answer is people there are already accustomed
  // to the weird time behaviour when their neighbours
  // may have completely different date than they do.
  //
  // `Math.round()` rounds all time fractions
  // smaller than a millisecond (e.g. nanoseconds)
  // but it's unlikely that an Excel serial date
  // is gonna contain even seconds.
  //
  return new Date(Math.round((excelSerialDate - daysBeforeUnixEpoch) * 24 * hour) + 12 * hour);
};

0
2017-09-07 15:06