Кто-нибудь знает, как преобразовать дату Excel в правильную временную метку Unix?
Дата Excel для отметки времени Unix
Ответ 1
Без них они работали для меня... когда я преобразовал временную метку назад в 4 года.
Это сработало отлично: =(A2-DATE(1970,1,1))*86400
Кредит идет: Filip Czaja http://fczaja.blogspot.ca
Оригинальное сообщение: http://fczaja.blogspot.ca/2011/06/convert-excel-date-into-timestamp.html
Ответ 2
Windows и 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
Справка:
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)
Ответ 3
Если предположить, что дата в Excel находится в ячейке A1, отформатированной как Date, а временная метка Unix должна быть в ячейке A2, отформатированной как номер, формула в A2 должна быть:
= (A1 * 86400) - 2209075200
где:
86400 - количество секунд в день 2209075200 - это количество секунд между 1900-01-01 и 1970-01-01, которые являются базовыми датами для временных меток Excel и Unix.
Вышеупомянутое верно для Windows. На Mac базовая дата в Excel равна 1904-01-01, а номер секунд должен быть скорректирован на: 2082844800
Ответ 4
Вот сопоставление для ссылки, предполагая UTC для систем электронных таблиц, таких как 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" - 1899/12/31; см. раздел Ошибка ниже. † Excel 2011 для Mac (и старше) использует 1904 date system.
Как я часто использую awk для обработки CSV и содержимое с разделителями-пробелами, я разработал способ конвертировать UNIX-эпоху в часовой пояс /DST -локальный Формат даты Excel:
echo 1234567890 |awk '{
# tries GNU date, tries BSD date on failure
cmd = sprintf("date [email protected]%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
}'
Я использовал echo для этого примера, но вы можете передать файл, где первый столбец (для первой ячейки в формате .csv, называемый как awk -F,) - это эпоха UNIX. Alter $1, чтобы указать нужный номер столбца/ячейки или вместо него использовать переменную.
Это делает системный вызов date. Если вы будете надежно иметь версию GNU, вы можете удалить 2>/dev/null || date … +%%z и второй , $1. Учитывая, насколько распространен GNU, я бы не стал рекомендовать версию BSD.
getline считывает смещение часового пояса, выводимое date +%z в tz, которое затем преобразуется в hours. Формат будет выглядеть как -0700 (PDT) или +0530 (IST), поэтому первая полученная подстрока 07 или 05, вторая - 00 или 30 (тогда деленное на 60, выраженное в часах), а третье использование tz показывает, является ли наше смещение отрицательным и при необходимости меняет hours.
Формула, приведенная во всех других ответах на этой странице, используется для установки excel с добавлением настройки часового пояса с учетом дневного света как hours/24.
Если вы используете более старую версию Excel для Mac, вам нужно использовать 24107 вместо 25569 (см. приведенное выше сопоставление).
Чтобы преобразовать любое произвольное время без времени в удобное для Excel время с датой 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
}'
Это в основном тот же код, но date -d больше не имеет @ для представления unix epoch (учитывая, насколько способна синтаксический анализатор строк, я действительно удивлен, что @ является обязательным; какая другая дата формат имеет 9-10 цифр?), и теперь он запросил два выхода: эпоху и смещение часового пояса. Поэтому вы можете использовать, например, @1234567890 в качестве входа.
Ошибка
Lotus 1-2-3 (исходное программное обеспечение для электронной таблицы) намеренно обработал 1900 как високосный год, несмотря на то, что он не был (это уменьшало кодовую базу в то время, когда подсчитывался каждый байт). Microsoft Excel сохранил эту ошибку для совместимости, пропустив день 60 (фиктивный 1900/02/29), сохранив отображение Lotus 1-2-3 день 59-1900/02/28. LibreOffice вместо этого назначил день с 60 по 1900/02/28 и нажал все предыдущие дни назад.
Любая дата до 1900/03/01 может быть как выходной день:
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 не признает отрицательные даты и имеет специальное определение Zeroth of January за нулевой день. Внутри Excel действительно обрабатывает отрицательные даты (они просто цифры в конце концов), но он не знает, как отображать их как даты (а также не может преобразовать более старые даты в отрицательные числа). 29 февраля 1900 года, который никогда не случался, распознается Excel, но не LibreOffice.
Ответ 5
Вы, очевидно, отключены на один день, ровно на 86400 секунд. Используйте номер 2209161600 Не номер 2209075200 Если вы Google два номера, вы найдете поддержку выше. Я пробовал вашу формулу, но всегда был на 1 день отличным от моего сервера. Это не очевидно из timestamp unix, если вы не думаете в unix вместо человеческого времени;-), но если вы дважды проверите, вы увидите, что это может быть правильно.
Ответ 6
Поскольку мои исправления к вышеуказанному были отклонены (кто-нибудь из вас действительно пытался?), вот что вам действительно нужно сделать для этой работы:
Windows (и Mac Office 2011 +):
- Unix Timestamp =
(Excel Timestamp - 25569) * 86400 - Временная метка Excel =
(Unix Timestamp / 86400) + 25569
MAC OS X (pre Office 2011):
- Unix Timestamp =
(Excel Timestamp - 24107) * 86400 - Временная метка Excel =
(Unix Timestamp / 86400) + 24107
Ответ 7
Ни один из текущих ответов не работал у меня, потому что мои данные были в этом формате со стороны unix:
2016-02-02 19:21:42 UTC
Мне нужно было преобразовать это в Epoch, чтобы можно было ссылаться на другие данные, которые имели временные метки времени.
-
Создайте новый столбец для части даты и проанализируйте с помощью этой формулы
=DATEVALUE(MID(A2,6,2) & "/" & MID(A2,9,2) & "/" & MID(A2,1,4)) -
Как уже упоминал еще один Grendler, создайте еще один столбец
=(B2-DATE(1970,1,1))*86400 -
Создайте еще один столбец с добавленным временем, чтобы получить общее количество секунд:
=(VALUE(MID(A2,12,2))*60*60+VALUE(MID(A2,15,2))*60+VALUE(MID(A2,18,2))) -
Создайте последний столбец, который просто добавляет последние два столбца вместе:
=C2+D2
Ответ 8
Вот мой окончательный ответ на этот вопрос.
Также очевидно, что javascript new Date(year, month, day) конструктор не учитывает также прыжки секунд.
// 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 unlikely that an Excel serial date
// is gonna contain even seconds.
//
return new Date(Math.round((excelSerialDate - daysBeforeUnixEpoch) * 24 * hour) + 12 * hour);
};