Câu hỏi Excel ngày để Unix dấu thời gian

Windows và 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

Để tham khảo:

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)

Nếu chúng ta giả sử ngày tháng trong Excel nằm trong ô A1 được định dạng là Ngày và dấu thời gian Unix phải nằm trong ô A2 được định dạng là số thì công thức trong A2 phải là:

= (A1 * 86400) - 2209075200

Ở đâu:

86400 là số giây trong ngày 2209075200 là số giây giữa 1900-01-01 và 1970-01-01 là ngày cơ sở cho dấu thời gian Excel và Unix.

Điều trên là đúng cho Windows. Trên máy Mac ngày cơ sở trong Excel là 1904-01-01 và số giây phải được sửa thành: 2082844800

Đây là một ánh xạ để tham khảo, giả sử UTC cho các hệ thống bảng tính như 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” là 1899/12/31; xem Lỗi phần dưới đây. † Excel 2011 cho Mac (và cũ hơn) sử dụng Hệ thống ngày 1904.}

Như tôi thường sử dụng awk để xử lý CSV và nội dung được phân cách bằng không gian, tôi đã phát triển một cách để chuyển đổi kỷ nguyên UNIX thành Múi giờ/DST-thích hợp Định dạng ngày tháng 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
}'

Tôi đã sử dụng echo cho ví dụ này, nhưng bạn có thể đặt tên cho tệp ở cột đầu tiên (đối với ô đầu tiên ở định dạng .csv, hãy gọi nó là awk -F,) là một kỷ nguyên UNIX. Thay đổi $1 để đại diện cho số cột / ô mong muốn của bạn hoặc sử dụng biến thay thế.

Điều này làm cho một hệ thống gọi đến date. Nếu bạn chắc chắn sẽ có phiên bản GNU, bạn có thể xóa 2>/dev/null || date … +%%z va thu hai , $1. Với GNU phổ biến như thế nào, tôi sẽ không khuyên bạn nên giả định phiên bản của BSD.

Các getline đọc chênh lệch múi giờ được xuất bởi date +%z vào tz, sau đó được dịch sang hours. Định dạng sẽ giống như -0700 (PDT) hoặc là +0530 (IST), vì vậy chuỗi con đầu tiên được trích xuất là 07 hoặc là 05, thứ hai là 00 hoặc là 30 (sau đó chia cho 60 được biểu thị bằng giờ) và lần sử dụng thứ ba tz xem liệu bù đắp của chúng tôi có âm hay không và thay đổi hours Nếu cần thiết.

Công thức được đưa ra trong tất cả các câu trả lời khác trên trang này được sử dụng để đặt excel, với việc bổ sung điều chỉnh múi giờ tiết kiệm ánh sáng ban ngày hours/24.

Nếu bạn đang sử dụng phiên bản Excel cũ hơn cho Mac, bạn sẽ cần phải sử dụng 24107 thay cho 25569 (xem bản đồ ở trên).

Để chuyển đổi bất kỳ thời gian không sử dụng tùy ý nào thành thời gian Excel thân thiện với ngày 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
}'

Điều này về cơ bản là cùng một mã, nhưng date -d không còn có @ đại diện cho unoch epoch (cho khả năng phân tích cú pháp chuỗi, tôi thực sự ngạc nhiên @là bắt buộc; định dạng ngày nào khác có 9-10 chữ số?) và bây giờ nó được yêu cầu hai kết quả đầu ra: epoch và offset múi giờ. Do đó, bạn có thể sử dụng ví dụ: @1234567890 làm đầu vào.

Lỗi

Lotus 1-2-3 (phần mềm bảng tính gốc) cố ý coi năm 1900 là năm nhuận mặc dù thực tế là nó không phải là (điều này làm giảm codebase tại một thời điểm khi mỗi byte tính). Microsoft Excel giữ lại lỗi này cho khả năng tương thích, bỏ qua ngày 60 (hư cấu 1900/02/29), giữ lại bản đồ Lotus 1-2-3 của ngày 59 đến 1900/02/28. LibreOffice thay vào đó được chỉ định ngày 60 đến 1900/02/28 và đẩy tất cả các ngày trước đó trở lại một.

Bất kỳ ngày nào trước ngày 1900/03/01 có thể là ngày nghỉ nhiều nhất:

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 không thừa nhận ngày tháng âm và có định nghĩa đặc biệt về Zeroth của tháng 1 cho ngày không. Bên trong, Excel thực sự xử lý ngày tháng tiêu cực (chúng chỉ là số sau), nhưng nó không biết cách hiển thị chúng như ngày tháng (cũng không thể chuyển đổi ngày cũ thành số âm). Ngày 29 tháng 2 năm 1900, một ngày không bao giờ xảy ra, được Excel công nhận chứ không phải LibreOffice.

Bởi vì các chỉnh sửa của tôi ở trên đã bị từ chối (có ai trong số các bạn thực sự thử không?), Đây là những gì bạn thực sự cần để thực hiện công việc này:

Windows (và Mac Office 2011+):

• Dấu thời gian Unix = (Excel Timestamp - 25569) * 86400
• Dấu thời gian Excel = (Unix Timestamp / 86400) + 25569

MAC OS X (trước Office 2011):

• Dấu thời gian Unix = (Excel Timestamp - 24107) * 86400
• Dấu thời gian Excel = (Unix Timestamp / 86400) + 24107

Bạn dường như tắt một ngày, chính xác là 86400 giây. Sử dụng số 2209161600 Không phải số 2209075200 Nếu bạn Google hai số, bạn sẽ tìm thấy hỗ trợ cho các bên trên. Tôi đã thử công thức của bạn nhưng luôn luôn đến 1 ngày khác với máy chủ của tôi. Nó không rõ ràng từ dấu thời gian unix trừ khi bạn nghĩ rằng trong unix thay vì thời gian của con người ;-) nhưng nếu bạn kiểm tra lại sau đó bạn sẽ thấy điều này có thể là chính xác.

Không có câu trả lời hiện tại nào phù hợp với tôi vì dữ liệu của tôi ở định dạng này từ phía unix:

2016-02-02 19:21:42 UTC

Tôi cần chuyển đổi nó thành Epoch để cho phép tham chiếu đến các dữ liệu khác có dấu thời gian epoch.

1. Tạo cột mới cho phần ngày và phân tích cú pháp với công thức này

   =DATEVALUE(MID(A2,6,2) & "/" & MID(A2,9,2) & "/" & MID(A2,1,4)) 
   

2. Như Grendler khác đã nêu ở đây rồi, hãy tạo một cột khác

   =(B2-DATE(1970,1,1))*86400 
   

3. Tạo cột khác chỉ với thời gian được thêm cùng nhau để có tổng số giây:

   =(VALUE(MID(A2,12,2))*60*60+VALUE(MID(A2,15,2))*60+VALUE(MID(A2,18,2)))
   

4. Tạo cột cuối cùng chỉ thêm hai cột cuối cùng với nhau:

   =C2+D2
   

Đây là câu trả lời cuối cùng của tôi cho điều này.

Cũng rõ ràng là javascript new Date(year, month, day) constructor cũng không tính đến số giây nhảy vọt.

// 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);
};