Frage Excel-Datum zu Unix-Zeitstempel


Kann jemand ein Excel-Datum in einen korrekten Unix-Zeitstempel umwandeln?


76
2017-11-09 20:21


Ursprung


Antworten:


Nichts davon hat für mich funktioniert ... als ich den Zeitstempel zurückkonvertiert habe, sind es 4 Jahre.

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

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

Original Beitrag: http://fczaja.blogspot.ca/2011/06/convert-excel-date-into-timestamp.html


94
2018-06-21 14:57



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)

64
2018-05-27 16:10



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

= (A1 * 86400) - 2209075200

woher:

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 darstellen.

Das oben genannte gilt für Windows. Auf Mac ist das Basisdatum in Excel 1904-01-01 und die Sekundenzahl sollte korrigiert werden zu: 2082844800


11
2017-11-09 20:46



Hier ist ein Mapping zur Referenz, angenommen koordinierte Weltzeit für Tabellenkalkulationssysteme 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; Siehe die Fehler Abschnitt unten.  Excel 2011 für Mac (und ältere) verwenden Sie die 1904 Datumssystem.

Wie ich oft benutze awk herstellen CSV und durch Leerzeichen getrennte Inhalte, habe ich eine Möglichkeit entwickelt, UNIX-Epochen zu konvertieren Zeitzone/DST-angemessen Excel-Datumsformat:

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

ich benutzte echo für dieses Beispiel, aber Sie können eine Datei mit der ersten Spalte (für die erste Zelle im CSV-Format, nennen Sie es als awk -F,) ist eine UNIX-Epoche. Ändern $1 um die gewünschte Spalten- / Zellennummer darzustellen oder stattdessen eine Variable zu verwenden.

Dies macht einen Systemaufruf zu date. Wenn Sie die GNU-Version zuverlässig haben, können Sie das entfernen 2>/dev/null || date … +%%z und der zweite , $1. Angesichts der allgemeinen GNU würde ich die BSD-Version nicht empfehlen.

Das getline liest den von ausgegebenen Zeitzonenversatz aus date +%z in tz, die dann in übersetzt wird hours. Das Format wird ähnlich sein -0700 (PDT) oder +0530 (IST), also die erste extrahierte Teilkette ist 07 oder 05das zweite ist 00 oder 30 (dann geteilt durch 60, um in Stunden ausgedrückt zu werden), und die dritte Verwendung von tz sieht, ob unser Offset negativ ist und sich ändert hours wenn benötigt.

Die Formel in allen anderen Antworten auf dieser Seite wird verwendet, um festzulegen excel, mit der Hinzufügung der Sommerzeit-bewußten Zeitzonenanpassung als hours/24.

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

Um beliebige Nicht-Epochen-Zeit in Excel-freundliche Zeiten mit GNU-Datum zu konvertieren:

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 der gleiche Code, aber die date -d hat nicht mehr ein @ um die Unix - Epoche zu repräsentieren (angesichts der Tatsache, wie leistungsfähig der String - Parser ist, bin ich eigentlich überrascht @ist obligatorisch; Welches andere Datumsformat hat 9-10 Ziffern?) und es wird nun gefragt zwei Ausgaben: die Epoche und der Zeitzonen-Offset. Sie könnten daher z.B. @1234567890 als Eingabe.

Fehler

Lotus 1-2-3 (die ursprüngliche Tabellenkalkulationssoftware) absichtlich 1900 als Schaltjahr behandelt trotz der Tatsache, dass es nicht war (dies reduzierte die Codebasis zu einem Zeitpunkt, als jedes Byte gezählt wurde). Microsoft Excel beibehalten Dieser Bug für die Kompatibilität, überspringen Tag 60 (der fiktive 1900/02/29), beibehalten Lotus 1-2-3-Mapping von Tag 59 bis 1900/02/28. LibreOffice hat stattdessen Tag 60 bis 1900/02/28 zugewiesen und alle vorherigen Tage um eins zurück geschoben.

Jedes Datum vor 1900/03/01 könnte so viel wie ein freier Tag sein:

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 bestätigt keine negativen Daten und hat eine spezielle Definition des Zeroth von Januar für den Tag Null. Intern behandelt Excel in der Tat negative Daten (sie sind nur Zahlen), aber es weiß nicht, wie sie als Datum angezeigt werden (auch ältere Daten können nicht in negative Zahlen umgewandelt werden). 29. Februar 1900, ein Tag, der nie passiert ist, wird von Excel aber nicht von LibreOffice erkannt.


6
2017-09-15 05:01



Da meine Bearbeitungen zu den oben genannten wurden abgelehnt (hat jemand von euch tatsächlich versucht?), Hier ist, was Sie wirklich brauchen, um dies zu arbeiten:

Windows (und Mac Office 2011+):

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

MAC OS X (vor dem Büro 2011):

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

3
2018-02-13 16:11



Sie sind offensichtlich um einen Tag, genau 86400 Sekunden. Verwenden Sie die Nummer 2209161600 Nicht die Nummer 2209075200 Wenn Sie die beiden Nummern googeln, finden Sie Unterstützung für die oben genannten. Ich habe versucht, Ihre Formel, aber kam immer 1 Tag anders von meinem Server. Es ist nicht offensichtlich aus dem Unix-Zeitstempel, es sei denn du denkst in Unix anstelle von menschlicher Zeit ;-), aber wenn du das nochmal kontrollierst, dann wirst du sehen, dass das korrekt ist.


2
2018-03-23 14:43



Keine der aktuellen Antworten funktionierte für mich, weil meine Daten in diesem Format von der Unix-Seite waren:

2016-02-02 19:21:42 UTC

Ich musste dies in Epoch umwandeln, um auf andere Daten zugreifen zu können, die Epochenzeitstempel hatten.

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

    =DATEVALUE(MID(A2,6,2) & "/" & MID(A2,9,2) & "/" & MID(A2,1,4)) 
    
  2. Wie schon andere Grendler hier angegeben haben, erstellen Sie eine weitere Spalte

    =(B2-DATE(1970,1,1))*86400 
    
  3. Erstellen Sie eine weitere Spalte, in der nur die Zeit addiert wird, um insgesamt Sekunden 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, die nur die letzten beiden Spalten zusammenfügt:

    =C2+D2
    

0
2018-02-03 20:17



Hier ist meine ultimative Antwort darauf.

Auch anscheinend Javascript new Date(year, month, day) Konstruktor berücksichtigt 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