9 Frage: Excel-Datum zum Unix-Zeitstempel

Frage erstellt am Mon, Nov 9, 2009 12:00 AM

Weiß jemand, wie man ein Excel-Datum in einen korrekten Unix-Zeitstempel konvertiert?

    
80
  1. Was meinen Sie mit einem "Excel-Datum"? Meinen Sie damit Text, der als lesbare Datums- /Uhrzeitzeichenfolge wie "11/09/2009 3:23:24 PM" formatiert ist?
    2009-11-09 20: 23: 49Z
  2. Vergessen Sie nicht, dass der Unix-Zeitstempel am 19. Januar 2038 aufgrund eines 32-Bit-Überlaufs nicht mehr funktioniert. Vor diesem Zeitpunkt müssen Millionen von Anwendungen entweder eine neue Konvention für Zeitstempel anwenden oder auf 64-Bit-Systeme migriert werden, wodurch der Zeitstempel ein bisschen mehr Zeit erhält.
    23.08.2010 14: 23: 05Z
  3. Mehr als 32 Bit mehr Zeit.
    2011-09-15 16: 55: 30Z
9 Antworten                              9                         

Keines davon hat bei mir funktioniert ... als ich den Zeitstempel zurückkonvertiert habe, ist es 4 Jahre später.

Das hat perfekt funktioniert: =(A2-DATE(1970,1,1))*86400

Der Kredit geht an: Filip Czaja http://fczaja.blogspot.ca

Ursprünglicher Beitrag: http: //fczaja.blogspot .ca /2011/06 /excel-date-into-timestamp.html

konvertieren     
99
2012-06-21 14: 57: 47Z
  1. Das Gleiche gilt hier, und das ist viel einfacher zu lesen
    2013-04-03 13: 13: 58Z
  2. Vergessen Sie nicht Ihre Zeitzone (es sei denn, Sie sind in UTC und beobachten die Sommerzeit nicht). Die UNIX-Epoche ist unabhängig von der Zeitzone, Tabellenkalkulationen hingegen nicht. Hier ist ein Leitfaden mit Beispielen.
    2015-06-25 02: 34: 58Z
  3. GMT + 8: =((A1+28800)/86400)+25569
    2016-01-19 03: 10: 05Z
  4. aktuelle Zeit für CDT (zentrale Sommerzeit): =(NOW()-DATE(1970,1,1))*86400 + 5*3600
    2016-05-12 22: 51: 04Z
  5. @ tonygil Es funktioniert, wenn die Zellen, auf die Sie abzielen, tatsächlich Daten in Excel sind. Wenn es sich um Text handelt, der ein Datum darstellen soll, sind alle Wetten deaktiviert.
    2017-12-18 16: 09: 41Z

Windows und 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

Als Referenz:

 
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)
    
68
2013-07-01 14: 49: 20Z
  1. Office 2011 auf Mac funktioniert anscheinend problemlos mit der "Windows" -Version dieser Formeln. Die "Mac" -Versionen sind weit davon entfernt.
    2013-02-11 21: 46: 32Z
  2. Ich glaube, Sie haben die Excel- und Unix-Zeitstempel in diesen Formeln umgekehrt. Der Unix-Zeitstempel ist eine Anzahl von Sekunden und muss daher durch 86400 geteilt werden, nicht durch den Excel-Zeitstempel, wie in den Formeln angegeben. Siehe die Antwort von @ radicand.
    2013-02-19 11: 59: 38Z
  3. Vielen Dank für die Kommentare, die auf Mac Excel 2011 getestet wurden und anscheinend mit der Windows-Version identisch sind.
    2013-02-19 18: 20: 35Z
  4. Großartig, diese Formel funktioniert auch ohne Excel !!! +1
    2013-07-01 14: 57: 03Z
  5. Gibt es eine Möglichkeit, einen Unix-Zeitstempel in Millisekunden in Datum und Uhrzeit umzuwandeln, wobei die Millisekunden erhalten bleiben?
    2017-05-25 10: 53: 42Z

Wenn wir annehmen, dass das Datum in Excel in der A1-Zelle als Datum formatiert ist und der Unix-Zeitstempel in einer A2-Zelle als Zahl formatiert ist, sollte die Formel in A2 lauten:

= (A1 * 86400) - 2209075200

wo:

86400 ist die Anzahl der Sekunden am Tag 2209075200 ist die Anzahl der Sekunden zwischen 1900-01-01 und 1970-01-01, die die Basisdaten für Excel- und Unix-Zeitstempel sind.

Das oben Gesagte gilt für Windows. Unter Mac ist das Basisdatum in Excel 1904-01-01 und die Sekundenanzahl sollte auf 2082844800

korrigiert werden     
11
2009-11-09 20: 46: 00Z
  1. Zumindest unter Windows funktioniert dies nicht genau. Es ist um 19 Stunden ausgeschaltet.
    2011-08-19 15: 55: 31Z
  2. Also sollte es sein: = (A1 * 86400) - 2209143600
    2011-08-19 15: 59: 51Z
  3. Die Standard-Datumsbasis von Mac Excel ist 1904, während sie in Windows Excel 1900 ist. Sie können die Datumsbasis in Mac Excel jedoch ändern, indem Sie das Kontrollkästchen "1904-Datumssystem verwenden" in den Einstellungen deaktivieren /Berechnung, dann funktioniert es wie Windows Excel.
    2013-02-06 16: 02: 59Z

Hier ist eine Zuordnung als Referenz, unter der Annahme, dass UTC für Tabellensysteme wie 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" ist 1899/12/31; Weitere Informationen finden Sie im Abschnitt Fehler . Excel 2011 für Mac (und älter) verwendet den 1904er Datumssystem .

Wie ich oft awk verwende, um CSV und durch Leerzeichen getrennte Inhalte. Ich habe eine Möglichkeit entwickelt, die UNIX-Epoche in die Zeitzone umzuwandeln / Sommerzeit -angemessenes Excel-Datum Format:

 
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
}'

Für dieses Beispiel habe ich echo verwendet, aber Sie können eine Pipe für eine Datei erstellen, bei der die erste Spalte (für die erste Zelle im CSV-Format awk -F,) eine UNIX-Epoche ist. Ändern Sie $1, um die gewünschte Spalten- /Zellennummer darzustellen, oder verwenden Sie stattdessen eine Variable.

Dadurch wird ein Systemaufruf an date gesendet. Wenn Sie die GNU-Version zuverlässig haben, können Sie den 2>/dev/null || date … +%%z und den zweiten , $1 entfernen. Angesichts der Verbreitung von GNU würde ich die Annahme der BSD-Version nicht empfehlen.

Der getline liest den von date +%z ausgegebenen Zeitzonenversatz in tz, der dann in hours übersetzt wird. Das Format ist -0700 ( PDT ) oder +0530 ( IST ), daher ist der erste extrahierte Teilstring 07 oder 05. Der zweite Wert ist 00 oder 30 (dann geteilt durch 60, um in Stunden ausgedrückt zu werden). Bei der dritten Verwendung von tz wird geprüft, ob unser Offset negativ ist, und bei Bedarf wird hours geändert.

Die in allen anderen Antworten angegebene FormelAuf dieser Seite wird excel eingestellt, wobei die Zeitzonenanpassung mit Berücksichtigung der Sommerzeit als hours/24 hinzugefügt wird.

Wenn Sie eine ältere Version von Excel für Mac verwenden, müssen Sie 24107 anstelle von 25569 verwenden (siehe Abbildung oben).

So konvertieren Sie beliebige nicht-epochale Zeiten in Excel-freundliche Zeiten mit GNU-Datum:

 
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
}'

Dies ist im Grunde derselbe Code, aber der date -d verfügt nicht mehr über einen @, der die Unix-Epoche repräsentiert (angesichts der Fähigkeit des String-Parsers bin ich eigentlich überrascht, dass der @ obligatorisch ist; welches andere Datumsformat 9-10 Stellen hat) ?) und es werden nun zwei Ausgaben angefordert: die Epoche und die Zeitzonenverschiebung. Sie könnten daher z.B. @1234567890 als Eingabe.

Fehler

Lotus 1-2-3 (die ursprüngliche Tabellenkalkulationssoftware) absichtlich 1900 als Schaltjahr behandelt , obwohl dies nicht der Fall war (dies verringerte die Codebasis) zu einem Zeitpunkt, an dem jedes Byte gezählt wurde). Microsoft Excel behielt diesen Fehler aus Kompatibilitätsgründen bei und übersprang Tag 60 (der Fiktive) 1900/02/29), wobei die Zuordnung von Lotus 1-2-3 vom Tag 59 zum 1900/02/28 beibehalten wird. LibreOffice hat stattdessen Tag 60 bis 1900/02/28 zugewiesen und alle vorherigen Tage um einen Tag zurückgesetzt.

Jeder Tag vor dem 1. März 1900 kann bis zu einem Ruhetag sein:

 
Day        Excel   LibreOffice
-1            -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 erkennt keine negativen Daten an und hat eine spezielle Definition des Zeroth vom Januar (1899/12/31) für Tag Null. Intern verarbeitet Excel zwar negative Datumsangaben (sie sind schließlich nur Zahlen), zeigt sie jedoch als Zahlen an, da es nicht weiß, wie sie als Datumsangaben angezeigt werden sollen (und ältere Datumsangaben können auch nicht in negative Zahlen umgewandelt werden). Der 29. Februar 1900, ein Tag, der nie passiert ist, wird von Excel erkannt, nicht jedoch von LibreOffice.

    
6
2018-10-18 15: 07: 18Z

Da meine Änderungen an den oben genannten Elementen abgelehnt wurden (hat einer von Ihnen es tatsächlich versucht?), müssen Sie Folgendes tun, damit dies funktioniert:

Windows (und Mac Office 2011 +):

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

MAC OS X (vor Office 2011):

  • Unix-Zeitstempel = (Excel Timestamp - 24107) * 86400
  • Excel-Zeitstempel = (Unix Timestamp / 86400) + 24107
3
2013-02-13 16: 11: 44Z

Sie haben anscheinend einen Tag Zeit, genau 86400 Sekunden. Verwenden Sie die Nummer 2209161600 Nicht die Nummer 2209075200 Wenn Sie die beiden Zahlen googeln, finden Sie Unterstützung für die oben genannten. Ich habe Ihre Formel ausprobiert, kam aber immer einen Tag anders als auf meinem Server. Aus dem Unix-Zeitstempel wird nichts ersichtlich, es sei denn, Sie denken in Unix statt in menschlicher Zeit ;-). Wenn Sie dies jedoch noch einmal überprüfen, werden Sie feststellen, dass dies möglicherweise korrekt ist.

    
2
2011-03-23 ​​14: 43: 25Z
  1. Dies ist korrekt, da Excel das erste Jahr als Schaltjahr berechnet, obwohl dies nicht der Fall ist.
    2011-09-09 09: 27: 07Z
  2. Ich kann bestätigen, dass die magische Nummer in der Tat 2209161600 lautet, dh 1970-01-01 - 1900-01-01 + 1 Tag * 86400. Wenn Sie 1900-01 eingeben -01 in Excel und als Sylk-Format speichern und die Datei in einem Texteditor betrachten. Sie werden sehen, dass dieses Datum als 1 statt als Null gespeichert wird, wie es sollte. Aus diesem Grund müssen Sie 1 Tag hinzufügen
    2013-02-06 15: 59: 17Z

Ich hatte eine alte Excel-Datenbank mit "lesbaren" Daten wie 2010.03.28 20:12:30 Diese Daten wurden in UTC + 1 (MEZ) angegeben und mussten in die Epochenzeit umgerechnet werden.

Ich habe die Formel = (A4-DATE (1970; 1; 1)) * 86400-3600 verwendet, um die Daten in die Epochenzeit von den Werten in Spalte A in Spalte B zu konvertieren. Überprüfen Sie Ihren Zeitzonenversatz und rechnen Sie damit. 1 Stunde entspricht 3600 Sekunden.

Das Einzige, warum ich hier eine Antwort schreibe, dass dieses Thema älter als 5 Jahre ist, ist, dass ich die neuen Excel-Versionen und auch rote Beiträge in diesem Thema verwende, aber sie sind falsch. Das DATUM (1970; 1; 1). Hier müssen die 1970 und der Januar mit getrennt werden; und nicht mit,

Wenn dieses Problem auch auftritt, hoffen Sie, dass es Ihnen hilft. Einen schönen Tag noch:)

    
1
2018-11-08 22: 57: 00Z

Keine der aktuellen Antworten hat für mich funktioniert, da meine Daten von der Unix-Seite in diesem Format vorliegen:

2016-02-02 19:21:42 UTC

Ich musste dies in "Epoche" konvertieren, um auf andere Daten mit Epochenzeitstempeln verweisen zu können.

  1. Erstellen Sie eine neue Spalte für den Datumsteil und analysieren Sie mit dieser Formel

     
    =DATEVALUE(MID(A2,6,2) & "/" & MID(A2,9,2) & "/" & MID(A2,1,4)) 
    
  2. Wie andere Grendler bereits ausgeführt haben, erstellen Sie eine weitere Spalte

     
    =(B2-DATE(1970,1,1))*86400 
    
  3. Erstellen Sie eine weitere Spalte, indem Sie nur die Zeit addieren, um die Gesamtsekunden zu erhalten:

     
    =(VALUE(MID(A2,12,2))*60*60+VALUE(MID(A2,15,2))*60+VALUE(MID(A2,18,2)))
    
  4. Erstellen Sie eine letzte Spalte, in der nur die letzten beiden Spalten zusammengefasst werden:

     
    =C2+D2
    
0
2016-02-03 20: 17: 28Z

Hier ist meine ultimative Antwort darauf.

Offensichtlich berücksichtigt der new Date(year, month, day)-Konstruktor von Javascript auch keine Schaltsekunden.

 
// 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: 20Z
Quelle platziert Hier