Evolcom.ru

Бытовая техника
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как преобразовать дату и время из одного часового пояса в другой в Excel?

Дата Excel в метку времени Unix

Как можно преобразовать дату Excel в правильную метку времени Unix?

Ни один из них не сработал в моем случае. Когда я перевел временную метку обратно, оказалось, что прошло 4 года.

Он работал идеально: =(A2-DATE(1970,1,1))*86400.

Windows и Mac Excel (2011):

Будем считать, что дата в Excel находится в ячейке A1 как Дата, а метка времени Unix в ячейке A2 как числовое значение, и формула в A2 будет такой:

= (A1 * 86400) — 2209075200

86400 — количество секунд в сутках 2209075200 — количество секунд между 1900-01-01 и 1970-01-01-01, которые являются базовыми датами для временных меток Excel и Unix.

Все вышесказанное относится к Windows. На Mac базовой датой в Excel является 1904-01-01, и количество секунд должно быть скорректировано до: 2082844800.

Вот пример, предполагающий UTC для систем электронных таблиц, таких как Excel:

Дата «Jan Zero, 1900» равна 1899/12/31; см. ошибку ниже. † В Excel 2011 для Mac (и предыдущих версиях) используется система дат 1904 года.

Поскольку я часто использую awk для обработки CSV и содержимого, разделенного пробелами, я разработал способ преобразования эры UNIX в формат даты DST/timezone в Excel:

Для этого примера я использовал echo, но вы можете передать файл, первый столбец которого (для первой ячейки в формате .csv назовите awk -F, ) является эрой UNIX. Измените $1, чтобы представить желаемый номер столбца/ячейки или используйте переменную вместо этого.

Это делает системный вызов date . 2>/dev/null может быть безопасно удален, если ваша версия GNU надежна. +%%z и второе , $1 . Учитывая распространенность GNU, я не рекомендую использовать версию BSD.

Getline считывает смещение часового пояса, выведенное датой +%z , в tz, которое затем преобразуется в часы. Формат будет выглядеть следующим образом: -0700 ( PDT ) или +0530 ( IST ), поэтому первая извлеченная подстрока — 07 или 05, вторая — 00 или 30 (затем делится на 60 (это выражается в часах), а третье использование tz определяет, является ли наше смещение отрицательным, и при необходимости изменяет часы.

Читайте так же:
Как получить дату начала и окончания недели на основе определенной даты в Excel?

Формула, приведенная во всех других ответах на этой странице, используется для настройки Excel с дополнительным часовым поясом, установленным для включения летнего времени как час/24.

Если вы используете более старую версию Excel для Mac, вам нужно использовать 24107 вместо 25569 (смотрите сравнение выше).

Позволяет конвертировать любое время, кроме эпохи, в удобное для Excel время с помощью GNU date:

По сути, это тот же код, но дата -d больше не имеет @, чтобы представить эпоху Unix (учитывая возможности анализатора строк, я удивлен, что @ является обязательным; какой еще формат даты имеет 9-10 цифр? Поэтому в качестве записи можно использовать, например, @1234567890.

Ошибка

Поскольку Lotus 1-2-3 (оригинальная программа для работы с электронными таблицами) рассматривала 1900 год как високосный, хотя это было не так, это уменьшало кодовую базу на один байт. Excel исправил эту ошибку, пропустив день 60 (фиктивный 1900/02/29), при этом Lotus 1-2-3 сопоставил день 59 с 1900/02/28. Вместо этого LibreOffice назначил день 60 на 1900/02/28 и сдвинул все предыдущие дни на единицу назад.

Любая дата до 1900/03/01 может считаться праздничной:

Отрицательные даты не могут быть введены в Excel, а дата ноль января (1899/12/31) определяется в Excel как нулевой день. В Excel отрицательные даты обрабатываются внутренне (поскольку это просто числа), но он отображает их как числа, поскольку не знает, как отображать их как даты (и не может преобразовать старые даты в отрицательные числа). 29 февраля 1900 года, день, которого никогда не было, распознается Excel, но не LibreOffice.

Поскольку изменения, которые я внес в вышеуказанные пункты, были отклонены (кто-нибудь из вас пробовал?), вот что вам действительно нужно сделать, чтобы завершить эту работу:

Читайте так же:
Как перебирать файлы в каталоге и копировать данные в мастер-лист в Excel?

Windows (и Mac Office 2011+):

  • Unix Timestamp = (Excel Timestamp — 25569) * 86400
  • Отметка времени Excel = (Unix Timestamp / 86400) + 25569

MAC OS X (до Office 2011):

  • Unix Timestamp = (Excel Timestamp — 24107) * 86400
  • Отметка времени Excel = (Unix Timestamp / 86400) + 24107

Очевидно, что вы отключены на один день, ровно на 86400 секунд. Вы можете погуглить эти два числа, если ищете поддержку для вышеупомянутого. 2209161600, а не 2209075200 Всякий раз, когда я пробовал вашу формулу, она всегда совпадала с моим сервером на 1 день. Это не очевидно из временной метки Unix, если только вы не думаете о Unix, а не о человеческом времени ;-), но если вы дважды проверите, вы увидите, что это может быть правильно.

Это была старая база данных Excel с «читаемыми» датами, такими как 2010.03.28 20:12:30 Они были в формате UTC + 1 (CET), и мне нужно было преобразовать их в эпохальные времена.

Я использовал формулу = (A4-DATE (1970; 1; 1; 1; 1)) * 86400-3600 , чтобы преобразовать даты в эпохи из значений в столбце A в значения в столбце B. Проверьте смещение часового пояса и выполните расчет на его основе. 1 час — это 3600 секунд.

Я размещаю ответ здесь только потому, что вы видите, что этому потоку более 5 лет, что я использую новые версии Excel, и что в этом потоке есть сообщения reddit, но они неверны. ДАТА (1970; 1; 1). Здесь 1970 и январь должны быть разделены; не s,

Надеюсь, это поможет вам, если вы также столкнулись с этой проблемой. Хорошего дня 🙂

Это мой последний ответ.

Вы также можете видеть, что новый конструктор Date(year, month, day) в javascript также не учитывает дополнительные секунды.

Читайте так же:
Как посчитать, сколько ячеек содержат определенный текст или значение в Excel?

Ни один из существующих ответов не подошел мне, поскольку мои данные были в этом формате на стороне Unix:

2016-02-02 19:21:42 UTC

Мне нужно было преобразовать эти данные в Epoch, чтобы иметь возможность ссылаться на другие данные с меткой времени Epoch.

Создайте новый столбец для части даты и проанализируйте данную формулу

Как упомянул другой грендлер, следует создать еще одну колонну.

Создайте еще один столбец, в котором вы просто сложите время, чтобы получить общее количество секунд:

Если добавить два последних столбца в качестве последнего столбца, то получим:

Преобразование времени в Excel

Разделите десятичные часы на 24, чтобы преобразовать их в значение, которое Excel сможет распознать как время.

Преобразование часов в Excel

В показанном примере формула представлена в C4 :

Это дает 0,5, эквивалент 12 часов.

В системе дат Excel день равен 1. Поскольку в сутках 24 часа, необходимо разделить на 24, чтобы преобразовать его в значение, которое Excel распознает как время. После преобразования вы можете применить любой формат времени или использовать результат в математических операциях с другими датами или временем.

В примере, поскольку B5 содержит 12 (представляющих 12 часов), результат 12/24 = 0,5, так как в половине дня 12 часов.

Если результат должен быть отформатирован как время, используйте формат времени.

Для отображения часов с временем, превышающим 24 часа, необходимо настроить формат цифр. Просто заключите h в квадратные скобки, как показано ниже:

Для отображения в минутах вы можете сделать тоже самое с m:

Скобки говорят Excel, что время — это продолжительность, а не время суток.

Преобразование минут в Excel

Чтобы перевести минуты в десятичном формате в правильное время в Excel, разделите на 1440.

Читайте так же:
Как посчитать количество выходных / будних дней между двумя датами в Excel?

Преобразование минут в Excel

В представленном примере формула находится в C4 :

Поскольку B4 содержит 360 (360 минут), результат равен 360/1440 = 0,25, поскольку 360 минут = 6 часов = 1/4 суток.

Чтобы отобразить результат в виде времени, примените формат отображения времени.

В системе дат Excel один день равен 1. Поскольку в сутках 24 часа, 1 час = 1/24. Одна минута требует деления на 1440, так как в сутках 1440 минут (24 x 60).

Поскольку B5 в данном примере содержит 360 (т.е. 6 часов), результат равен 360/1440 = 0,25, так как 6 часов = 1/4 суток.

Если вы хотите, чтобы результат отображался в формате времени, используйте Формат времени.

Для отображения часов, которые больше 24 часов, или минут, которые больше 60 минут, необходимо изменить формат числа, добавив квадратные скобки.

Скобки указывают в Excel, что время — это продолжительность, а не время суток.

Преобразование секунд в Excel

Чтобы перевести секунды в десятичном формате в правильное время в Excel, поделите на 86400.

Преобразование секунд в Excel

В приведенном примере деталь находится в позиции C4:

Чтобы отобразить результат в виде часа, используйте формат времени.

В Excel время хранится в виде дней или долей дня. Поскольку в сутках 24 часа, 60 минут в каждом часе и 60 секунд в каждой минуте, необходимо разделить на 24 * 60 * 60 = 86400, чтобы преобразовать десятичные секунды в значение, которое Excel распознает как время.

Так как B4 содержит 3600 (3600 секунд), 3600/86400 = 0,04170, поскольку 3600 секунд равны 1 часу, а 1/24 равна 0,04170.

Чтобы отображать часы длиннее 24 часов, минуты длиннее 60 минут или секунды длиннее 60 секунд, необходимо изменить формат чисел, добавив квадратные скобки.

Скобки сообщают Excel, что время — это продолжительность, а не время суток.

Предупреждение. Чтобы использовать квадратные скобки, необходимо создать и использовать пользовательский формат числа. Выделите ячейки, затем перейдите в раздел Форматы ячеек.

Читайте так же:
Как подсчитать максимальное или минимальное количество вхождений в Excel?

Преобразование времени в часовой пояс

Время можно перевести из одного часового пояса в другой с помощью формулы, которая преобразует часы, введенные в виде целых чисел, в десятичные значения, которые Excel распознает в качестве времени.

Преобразование времени в часовой пояс

В приведенном примере формула находится в поле F5:

Эта формула возвращает число, которое Excel распознает, как 14:00.

Время в Excel — это дробные значения числа 1. Таким образом, 12 часов дня 12/24 = 0,5, 6:00 утра 6/24 = 0,25 и так далее. Таким образом, чтобы преобразовать время по заданным числам, необходимо разделить количество часов на 24:

E5 / 24 // пересчет времени в Excel

Прибавив результат к времени начала, получаем:

Чтобы получить истинное значение времени, следует использовать только десятичное значение. Другими словами, если мы прибавим 12 часов (0,5) к 6 вечера (0,75), мы получим 1,25, но на самом деле нам нужно только 0,25.

Чтобы гарантировать, что мы получим только одно десятичное значение, мы используем OSTAT с делителем 1, что является умным способом упростить формулу.

OSTAT возвращает остаток после деления, и поэтому возвращает десятичное значение в случаях, когда результат больше 1 (т.е. больше, чем 24 часа).

Если мы получим отрицательное дробное значение, OSTAT также вернет противоположное значение. Таким образом, если в итоге мы получим -25, OSTAT вернет 0,75 (что соответствует 6 вечера).

Excel не отображает отрицательные значения.

Если вы работаете с датами + временем (т.е. значениями, включающими и дату, и время), вам не следует использовать OSTAT. Вы можете использовать :

Это позволит вам при необходимости изменить значение даты (вперед или назад) при изменении установки времени на 12:00.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector