Pytanie Data Excel do znacznika czasu Unix


Czy ktoś wie, jak przekonwertować datę programu Excel na poprawny znacznik czasu Uniksa?


76
2017-11-09 20:21


pochodzenie


Co masz na myśli mówiąc o "randce programu Excel"? Masz na myśli tekst sformatowany jako czytelny dla człowieka łańcuch dat i czasu, taki jak "11/09/2009 3:23:24 PM"? - Matt Ball
Nie zapominaj, że 19 stycznia 2038 roku znaczek czasu unixowego przestanie działać z powodu 32-bitowego przelewu. Do tego momentu miliony aplikacji będą musiały albo przyjąć nową konwencję dla znaczników czasu, albo zostać przeniesione do systemów 64-bitowych, które kupią znacznik czasu "trochę" więcej czasu.
bardziej jak 32bity więcej czasu. - user606723


Odpowiedzi:


Żadne z nich nie zadziałało dla mnie ... kiedy skonwertowałem znacznik czasu, jest to 4 lata.

To działało idealnie: =(A2-DATE(1970,1,1))*86400

Podziękowania dla: Filip Czaja http://fczaja.blogspot.ca

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


95
2018-06-21 14:57



To samo tutaj, a to jest o wiele łatwiejsze do odczytania - Roy Truelove
Nie zapomnij swojej strefy czasowej (chyba, że ​​jesteś w UTC i nie obserwujesz czasu letniego). Epoka UNIX nie jest agnostyczna dla strefy czasowej, podczas gdy arkusze kalkulacyjne nie są. Oto przewodnik z przykładami. - Adam Katz
GMT + 8: =((A1+28800)/86400)+25569 - Ivan Chau
aktualny czas na CDT (centralny czas letni): =(NOW()-DATE(1970,1,1))*86400 + 5*3600 - hBrent
@tonygil Będzie działać, jeśli komórki, na które kierujesz reklamy, są faktycznie datami w Excelu. Jeśli jest to tekst, który ma przedstawiać datę, wszystkie zakłady są wyłączone. - Casey


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

Na przykład:

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



Wydaje się, że Office 2011 na komputerze Mac działa dobrze z wersją tych formuł "Windows". Wersje "Mac" są na nim zbyt daleko. - radicand
Wydaje mi się, że w tych formułach zostały odwrócone znaczniki czasu excel i unix. Uniksowy znacznik czasu ma kilka sekund, a więc musi być podzielony przez 86400, a nie znacznik czasu excel, co mówią formuły. Zobacz odpowiedź @ radicand. - Mike Houston
Dzięki za komentarze, przetestowaliśmy to na Mac Excel 2011 i wygląda na to, że są takie same jak wersja dla systemu Windows. - Jeff Wu
Świetnie, te formuły działają nawet bez programu Excel !!! +1 - Luis Siquot
Czy istnieje sposób przekształcenia timstamp unix w milisekundach w datetime, zachowując milisekundy? - Lorenzo Belli


Jeśli przyjmiemy, że data w Excelu jest w komórce A1 sformatowanej jako Data, a datownik Unix powinien znajdować się w komórce A2 sformatowanej jako liczba, wzór na A2 powinien być:

= (A1 * 86400) - 2209075200

gdzie:

86400 to liczba sekund w ciągu dnia 2209075200 to liczba sekund między 1900-01-01 a 1970-01-01, które są datami podstawowymi znaczników czasowych Excel i Unix.

Powyższe odnosi się do systemu Windows. Na komputerze Mac data podstawowa w programie Excel to 1904-01-01, a liczba sekund powinna zostać poprawiona na: 2082844800


11
2017-11-09 20:46



To nie działa dokładnie na co najmniej Windows. Jest wyłączony o 19 godzin. - LLBBL
Powinno być tak: = (A1 * 86400) - 2209143600 - LLBBL
Domyślną datą Mac Excela jest 1904, podczas gdy w Windows Excel to 1900, ale możesz zmienić bazę dat w Mac Excelu, odznaczając "użyj daty 1904" w preferencjach / wyliczeniach, wtedy działa jak Windows Excel. - Cloudranger


Oto mapowanie dla odniesienia, zakładając UTC w przypadku systemów arkuszy kalkulacyjnych, takich jak 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" to 1899/12/31; zobacz Pluskwa sekcja poniżej.  Program Excel 2011 dla komputerów Mac (i starszych) korzysta z 1904 system daty.

Jak często używam awk w procesie CSV oraz treści oddzielone spacjami, opracowałem sposób konwersji epoki UNIX na strefa czasowa/DST-właściwy Format daty w programie 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
}'

użyłem echo dla tego przykładu, ale możesz potokować plik, w którym pierwsza kolumna (dla pierwszej komórki w formacie .csv, nazwij ją jako awk -F,) jest epoką UNIX. Zmieniać $1 do reprezentowania żądanej kolumny / numeru komórki lub użyj zamiast niej zmiennej.

W ten sposób wywołuje się systemowe wywołanie date. Jeśli będziesz niezawodnie posiadał wersję GNU, możesz usunąć 2>/dev/null || date … +%%z i drugi , $1. Biorąc pod uwagę, jak powszechne jest GNU, nie polecam przyjmowania wersji BSD.

The getline odczytuje przesunięcie strefy czasowej wyprowadzane przez date +%z w tz, który jest następnie tłumaczony na hours. Format będzie podobny -0700 (PDT) lub +0530 (IST), więc pierwszy wyodrębniony podciąg jest 07 lub 05, drugi jest 00 lub 30 (następnie podzielone przez 60 wyrażone w godzinach), a trzecie użycie tz widzi, czy nasze przesunięcie jest ujemne i zmienia się hours Jeśli potrzebne.

Wzór podany we wszystkich innych odpowiedziach na tej stronie służy do ustawienia excel, z dodatkiem regulacji strefy czasowej świadomej oszczędności jako hours/24.

Jeśli używasz starszej wersji programu Excel dla komputerów Mac, musisz użyć 24107 zamiast 25569 (patrz powyższe mapowanie).

Aby przekonwertować dowolny dowolny czas nie będący epoką na czas przyjazny dla Excela z datą 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
}'

Jest to w zasadzie ten sam kod, ale date -d już nie ma @ do reprezentowania epoki unixowej (biorąc pod uwagę to, jak zdolny jest parser łańcuchowy), jestem naprawdę zaskoczony @jest obowiązkowe; jaki inny format daty ma 9-10 cyfr?) i teraz jest to wymagane dwa wyjścia: epokę i przesunięcie strefy czasowej. Możesz więc użyć np. @1234567890 jako wejście.

Pluskwa

Lotus 1-2-3 (oryginalne oprogramowanie arkusza kalkulacyjnego) celowo traktował rok 1900 jako rok przestępny pomimo tego, że tak nie było (zmniejszyło to bazę kodów w momencie liczenia każdego bajtu). Microsoft Excel zatrzymany ten błąd zgodności, pomijając dzień 60 (fikcyjny 1900/02/29), zachowując mapowanie Lotus 1-2-3 z dnia 59 do 1900/02/28. Zamiast tego LibreOffice przypisał dni od 60 do 1900/02/28 i wypchnął wszystkie poprzednie dni wstecz.

Każda data przed 1900/03/01 może być nawet dniem wolnym:

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 nie uznaje negatywnych dat i ma specjalną definicję Zeroth of January na dzień zero. Wewnętrznie, Excel rzeczywiście obsługuje ujemne daty (są to przecież tylko liczby), ale nie wie, jak wyświetlać je jako daty (ani nie może konwertować starszych dat na liczby ujemne). 29 lutego 1900, dzień, który nigdy się nie wydarzył, jest rozpoznawany przez Excel, ale nie przez LibreOffice.


6
2017-09-15 05:01





Ponieważ moje poprawki do powyższego zostały odrzucone (czy któreś z was faktycznie próbowało?), Oto, co naprawdę trzeba zrobić, aby to działało:

Windows (i 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





Masz najwyraźniej jeden dzień, dokładnie 86400 sekund. Użyj numeru 2209161600 Nie numer 2209075200 Jeśli masz Google dwie liczby, znajdziesz wsparcie dla powyższych. Próbowałem twojej formuły, ale zawsze przychodziło 1 dzień inaczej niż mój serwer. To nie jest oczywiste z unixowego timestampu, chyba że myślisz o unixie zamiast o ludzkim czasie ;-), ale jeśli sprawdzisz dwukrotnie, zobaczysz, że to może być poprawne.


2
2018-03-23 14:43



Jest to poprawne, ponieważ program Excel oblicza pierwszy rok jako rok przestępny, mimo że tak nie jest. - ANisus
Mogę potwierdzić, że numer magiczny to rzeczywiście 2209161600, to jest 1970-01-01 - 1900-01-01 + 1 dzień * 86400. Jeśli wpiszesz 1900-01-01 w programie Excel i zapiszesz jako format sylk i spojrzysz na plik w edytor tekstu zobaczysz, że zapisuje tę datę jako 1 zamiast zera, tak jak powinien, dlatego musisz dodać 1 dzień - Cloudranger


Żadna z obecnych odpowiedzi nie sprawdziła się, ponieważ moje dane były w tym formacie od strony systemu UNIX:

2016-02-02 19:21:42 UTC

Musiałem przekonwertować to na epoka, aby umożliwić odniesienie innych danych, które miały epotypowe znaczniki czasu.

  1. Utwórz nową kolumnę dla części daty i przeanalizuj ją za pomocą tej formuły

    =DATEVALUE(MID(A2,6,2) & "/" & MID(A2,9,2) & "/" & MID(A2,1,4)) 
    
  2. Jak już o tym pisał inny Grendler, stwórz kolejną kolumnę

    =(B2-DATE(1970,1,1))*86400 
    
  3. Utwórz kolejną kolumnę z czasem dodanym, aby uzyskać łączną liczbę sekund:

    =(VALUE(MID(A2,12,2))*60*60+VALUE(MID(A2,15,2))*60+VALUE(MID(A2,18,2)))
    
  4. Utwórz ostatnią kolumnę, która po prostu dodaje dwie ostatnie kolumny razem:

    =C2+D2
    

0
2018-02-03 20:17