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


Có ai biết làm thế nào để chuyển đổi một ngày Excel để một dấu thời gian Unix đúng?


76
2017-11-09 20:21


gốc


Những gì bạn có nghĩa là bởi một "excel ngày"? Bạn có nghĩa là văn bản được định dạng như chuỗi ngày giờ có thể đọc được của con người như "11/09/2009 3:23:24 PM"? - Matt Ball
Đừng quên rằng vào ngày 19 tháng 1 năm 2038, Dấu thời gian Unix sẽ ngừng hoạt động do tràn 32 bit. Trước thời điểm này, hàng triệu ứng dụng sẽ cần phải áp dụng một quy ước mới cho tem thời gian hoặc được di chuyển sang hệ thống 64 bit sẽ mua dấu thời gian thêm một chút thời gian.
giống như 32bits nhiều thời gian hơn. - user606723


Các câu trả lời:


Những thứ này không có tác dụng đối với tôi ... khi tôi chuyển đổi dấu thời gian trở lại thì đã 4 năm.

Điều này làm việc hoàn hảo: =(A2-DATE(1970,1,1))*86400

Tín dụng đi đến: Filip Czaja http://fczaja.blogspot.ca

Bài đăng gốc: http://fczaja.blogspot.ca/2011/06/convert-excel-date-into-timestamp.html


95
2018-06-21 14:57



Tương tự ở đây, và điều này dễ đọc hơn nhiều - Roy Truelove
Đừng quên múi giờ của bạn (trừ khi bạn đang ở UTC và không quan sát Thời gian mùa hè). UNIX epoch là bất khả tri với múi giờ trong khi bảng tính thì không. Đây là hướng dẫn với các ví dụ. - Adam Katz
GMT + 8: =((A1+28800)/86400)+25569 - Ivan Chau
thời gian hiện tại cho CDT (giờ ban ngày trung tâm): =(NOW()-DATE(1970,1,1))*86400 + 5*3600 - hBrent
@tonygil Nó sẽ hoạt động nếu các ô mà bạn đang nhắm mục tiêu thực sự có trong Excel. Nếu đó là văn bản được cho là đại diện cho một ngày thì tất cả mọi phiên cược đều bị tắt. - Casey


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)

64
2018-05-27 16:10



Office 2011 trên Mac có vẻ hoạt động tốt với phiên bản "Windows" của các công thức này. Các phiên bản "Mac" là cách tắt trên nó. - radicand
Tôi nghĩ rằng bạn đã đảo ngược dấu thời gian excel và unix trong các công thức đó. Dấu thời gian Unix một số giây, và do đó cần phải được chia cho 86400, thay vì dấu thời gian excel, đó là những gì các công thức nói. Xem câu trả lời của @ radicand. - Mike Houston
Cảm ơn các ý kiến, thử nghiệm này trên Mac Excel 2011 và có vẻ như họ là giống như phiên bản Windows. - Jeff Wu
Tuyệt vời, công thức này hoạt động ngay cả khi không có excel !!! +1 - Luis Siquot
Có cách nào để chuyển đổi một dấu thời gian unix trong mili giây để datetime, bảo quản mili giây? - Lorenzo Belli


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


11
2017-11-09 20:46



Nó không hoạt động chính xác trên windows ít nhất. Nó được tắt bởi 19 giờ. - LLBBL
Vì vậy, nó phải là này: = (A1 * 86400) - 2209143600 - LLBBL
Cơ sở ngày mặc định của Mac Excel là 1904 trong khi Windows Excel là 1900, nhưng bạn có thể thay đổi cơ sở ngày trong Mac Excel bằng cách bỏ chọn "sử dụng hệ thống ngày 1904" trong tùy chọn / tính toán, sau đó nó hoạt động như excel của windows. - Cloudranger


Đâ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.


6
2017-09-15 05:01





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

3
2018-02-13 16:11





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.


2
2018-03-23 14:43



Điều này là chính xác, do Excel tính năm đầu tiên là năm nhuận mặc dù không phải vậy. - ANisus
Tôi có thể xác nhận số ma thuật thực sự là 2209161600, đây là 1970-01-01 - 1900-01-01 + 1 ngày * 86400. Nếu bạn nhập 1900-01-01 trong excel và lưu dưới định dạng sylk và xem tệp trong một trình soạn thảo văn bản, bạn sẽ thấy nó lưu ngày tháng đó là 1 thay vì bằng không, vì vậy bạn phải thêm 1 ngày - Cloudranger


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
    

0
2018-02-03 20:17





Đâ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);
};

0
2017-09-07 15:06