Oto mapowanie w celach informacyjnych, zakładając UTC dla 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 sekcję Błędy poniżej. † Excel 2011 dla komputerów Mac (i starsze) używają systemu daty 1904 .
Jak często korzystają awkz procesem CSV i zawartości przestrzeni oddzielonych, opracowałem sposób przekonwertować UNIX epoka do stref czasowych / DST -appropriate datę Excel format:
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 echow tym przykładzie, 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. Zmień, $1aby przedstawić żądany numer kolumny / komórki lub zamiast tego użyj zmiennej.
Powoduje to wywołanie systemowe date. Jeśli niezawodnie będziesz mieć wersję GNU, możesz usunąć 2>/dev/null || date … +%%zi drugą wersję , $1. Biorąc pod uwagę, jak powszechne jest GNU, nie polecałbym zakładać wersji BSD.
getlineCzyta przesunięcia strefy czasowej wyprowadzony przez date +%zsię tz, który jest następnie przetłumaczone na hours. Format będzie podobny do -0700( PDT ) lub +0530( IST ), więc pierwszy wyodrębniony podciąg to 07lub 05, drugi to 00lub 30(następnie podzielony przez 60 w celu wyrażenia w godzinach), a trzecie użycie tzsprawdza, czy nasze przesunięcie jest ujemne i zmienia hoursJeśli potrzebne.
Formuła podana we wszystkich innych odpowiedziach na tej stronie służy do ustawiania excel, z dodatkiem dostosowania strefy czasowej uwzględniającej czas letni jako hours/24.
Jeśli korzystasz ze starszej wersji programu Excel dla komputerów Mac, musisz użyć 24107zamiast 25569(zobacz mapowanie powyżej).
Aby przekonwertować dowolny czas nieokresowy na czas zgodny z Excelem 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 -dnie ma już @symbolu reprezentującego epokę uniksową (biorąc pod uwagę, jak zdolny jest parser ciągów, jestem właściwie zaskoczony, że @jest obowiązkowy; jaki inny format daty ma 9-10 cyfr?) I teraz jest pytany dla dwóch wyjść: epoki i przesunięcia strefy czasowej. Możesz więc użyć np. @1234567890Jako wejścia.
Pluskwa
Lotus 1-2-3 (oryginalne oprogramowanie do obsługi arkuszy kalkulacyjnych) celowo potraktował rok 1900 jako rok przestępny, mimo że tak nie było (zmniejszyło to bazę kodów w momencie, gdy liczył się każdy bajt). Microsoft Excel zachował ten błąd ze względu na kompatybilność, pomijając dzień 60 (fikcyjny 1900/02/29), zachowując mapowanie dnia 59 Lotus 1-2-3 do 1900/02/28. LibreOffice zamiast tego przypisał dzień 60 do 1900/02/28 i cofnął wszystkie poprzednie dni o jeden.
Dowolna data przed 1900/03/01 może oznaczać nawet dzień wolny:
Day Excel LibreOffice
-1 -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 dat ujemnych i ma specjalną definicję Zeroth stycznia (1899/12/31) dla dnia zerowego. Wewnętrznie program Excel rzeczywiście obsługuje daty ujemne (w końcu są to tylko liczby), ale wyświetla je jako liczby, ponieważ nie wie, jak wyświetlić je jako daty (ani nie może konwertować starszych dat na liczby ujemne). 29 lutego 1900 roku, dzień, który nigdy się nie wydarzył, jest rozpoznawany przez Excel, ale nie przez LibreOffice.
"11/09/2009 3:23:24 PM"?