Pertanyaan Tanggal Excel ke stempel waktu Unix


Apakah ada yang tahu cara mengonversi tanggal Excel ke stempel waktu Unix yang benar?


76
2017-11-09 20:21


asal


Jawaban:


Bukan ini bekerja untuk saya ... ketika saya mengubah timestamp kembali itu 4 tahun.

Ini bekerja dengan sempurna: =(A2-DATE(1970,1,1))*86400

Kredit masuk ke: Filip Czaja http://fczaja.blogspot.ca

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


94
2018-06-21 14:57



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

Sebagai referensi:

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



Jika kita menganggap tanggal di Excel dalam sel A1 yang diformat sebagai Tanggal dan stempel waktu Unix harus dalam sel A2 yang diformat sebagai angka, rumus dalam A2 seharusnya:

= (A1 * 86400) - 2209075200

dimana:

86400 adalah jumlah detik dalam sehari 2209075200 adalah jumlah detik antara 1900-01-01 dan 1970-01-01 yang merupakan tanggal dasar untuk cap waktu Excel dan Unix.

Di atas benar untuk Windows. Di Mac tanggal dasar di Excel adalah 1904-01-01 dan nomor detik harus dikoreksi ke: 2082844800


11
2017-11-09 20:46



Berikut ini adalah pemetaan untuk referensi, dengan asumsi UTC untuk sistem spreadsheet seperti 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" adalah 1899/12/31; lihat Bug bagian bawah.  Excel 2011 untuk Mac (dan yang lebih tua) menggunakan Sistem tanggal 1904.

Seperti yang sering saya gunakan awk proses CSV dan konten yang dibatasi ruang, saya mengembangkan cara untuk mengubah era UNIX menjadi zona waktu/DST-sesuai Format tanggal 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
}'

Saya menggunakan echo untuk contoh ini, tetapi Anda dapat menyalurkan file di mana kolom pertama (untuk sel pertama dalam format .csv, sebut sebagai awk -F,) adalah zaman UNIX. Mengubah $1 untuk mewakili nomor kolom / sel yang Anda inginkan atau menggunakan variabel sebagai gantinya.

Ini membuat sistem dipanggil date. Jika Anda akan memiliki versi GNU, Anda dapat menghapus 2>/dev/null || date … +%%z dan yang kedua , $1. Mengingat betapa umum GNU, saya tidak akan merekomendasikan asumsi versi BSD.

Itu getline membaca offset zona waktu yang dikeluarkan oleh date +%z ke tz, yang kemudian diterjemahkan ke dalam hours. Formatnya akan seperti -0700 (PDT) atau +0530 (IST), jadi substring pertama yang diekstrak adalah 07 atau 05, yang kedua adalah 00 atau 30 (Kemudian dibagi dengan 60 untuk diekspresikan dalam jam), dan penggunaan ketiga tz melihat apakah offset kami negatif dan berubah hours jika diperlukan.

Rumus yang diberikan di semua jawaban lain di halaman ini digunakan untuk mengatur excel, dengan tambahan penyesuaian zona waktu sadar-hemat-sadar sebagai hours/24.

Jika Anda menggunakan versi Excel yang lebih lama untuk Mac, Anda harus menggunakannya 24107 di tempat 25569 (lihat pemetaan di atas).

Untuk mengonversi waktu non-epoch sembarang ke waktu Excel-friendly dengan tanggal 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
}'

Ini pada dasarnya kode yang sama, tetapi date -d tidak lagi memiliki @ untuk merepresentasikan unix epoch (mengingat seberapa cakapnya parser string, saya benar - benar terkejut @adalah wajib; apa format tanggal lain yang memiliki 9-10 digit?) dan sekarang diminta dua output: zaman dan zona waktu diimbangi. Karena itu Anda dapat menggunakan mis. @1234567890 sebagai masukan.

Bug

Lotus 1-2-3 (perangkat lunak spreadsheet asli) dengan sengaja diperlakukan 1900 sebagai tahun kabisat Terlepas dari kenyataan bahwa itu tidak (ini mengurangi basis kode pada suatu waktu ketika setiap byte dihitung). Microsoft Excel ditahan bug ini untuk kompatibilitas, melewatkan hari ke 60 (fiktif 1900/02/29), mempertahankan pemetaan Lotus 1-2-3 hari 59 hingga 1900/02/28. LibreOffice bukannya ditugaskan hari 60 hingga 1900/02/28 dan mendorong semua hari sebelumnya kembali satu.

Setiap tanggal sebelum 1900/03/01 dapat berupa hari libur:

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 tidak mengakui tanggal negatif dan memiliki definisi khusus dari Zeroth Januari untuk hari nol. Secara internal, Excel memang menangani tanggal negatif (mereka hanya angka setelah semua), tetapi tidak tahu bagaimana menampilkannya sebagai tanggal (juga tidak dapat mengubah tanggal lama menjadi angka negatif). 29 Februari 1900, hari yang tidak pernah terjadi, diakui oleh Excel tetapi tidak LibreOffice.


6
2017-09-15 05:01



Karena pengeditan saya di atas ditolak (apakah ada di antara Anda yang benar-benar mencoba?), Inilah yang Anda butuhkan untuk membuat karya ini:

Windows (Dan Mac Office 2011+):

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

MAC OS X (pre Office 2011):

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

3
2018-02-13 16:11



Anda tampaknya mati oleh satu hari, tepatnya 86400 detik. Gunakan angka 2209161600 Bukan nomor 2209075200 Jika Anda Google dua nomor, Anda akan menemukan dukungan untuk hal di atas. Saya mencoba formula Anda tetapi selalu muncul 1 hari berbeda dari server saya. Ini tidak jelas dari stempel waktu unix kecuali Anda berpikir dalam unix, bukan waktu manusia ;-) tetapi jika Anda memeriksa ulang maka Anda akan melihat ini mungkin benar.


2
2018-03-23 14:43



Tak satu pun dari jawaban saat ini bekerja untuk saya karena data saya dalam format ini dari sisi unix:

2016-02-02 19:21:42 UTC

Saya perlu mengkonversinya ke Epoch untuk memungkinkan referensi data lain yang memiliki cap waktu zaman.

  1. Buat kolom baru untuk bagian tanggal dan parsing dengan rumus ini

    =DATEVALUE(MID(A2,6,2) & "/" & MID(A2,9,2) & "/" & MID(A2,1,4)) 
    
  2. Sebagaimana Grendler lainnya telah nyatakan di sini, buat kolom lain

    =(B2-DATE(1970,1,1))*86400 
    
  3. Buat kolom lain hanya dengan waktu yang ditambahkan bersama untuk mendapatkan detik total:

    =(VALUE(MID(A2,12,2))*60*60+VALUE(MID(A2,15,2))*60+VALUE(MID(A2,18,2)))
    
  4. Buat kolom terakhir yang hanya menambahkan dua kolom terakhir secara bersamaan:

    =C2+D2
    

0
2018-02-03 20:17



Inilah jawaban terakhir saya untuk ini.

Juga ternyata javascript new Date(year, month, day) konstruktor tidak memperhitungkan detik kabisat juga.

// 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