Формула Excel: перетворити час Excel на мітку часу Unix або на зворотну

У цьому нарисі мова йде про формули, які перетворюють час Excel та позначку часу Unix на аркуші Excel.

Прочитайте перед формулою:

Що таке мітка часу Unix?

Мітка часу Unix фіксує час, використовуючи секунди. У системі Unix він відлічує час з 1 січня 1970 року. Іншими словами, мітка часу Unix - це загальна кількість секунд між даною датою і 1 січня 1970 року.

Якщо ви хочете дотримуватися цього підручника, завантажте приклад електронної таблиці.

Перетворити час Excel на мітку часу Unix

Загальна формула:

Синтакст та аргументи

значення, що повертається

Формула повертає номер серії. Зазвичай повернене значення настільки велике, що відображається як #####, і ви можете відформатувати повернене значення як Загальне or Номер щоб показати його як звичайний номер серії.

Зауваження

Якщо заданий час Excel більше або дорівнює 1 січня 1900 р., Але менше 1 січня 1970 р., Він повертає від’ємне число; якщо час Excel дорівнює 1 січня 1970 р., він повертає нуль; якщо час Excel більше 1 січня 1970 р., це повертає додатне число.

помилка

Якщо час Excel менше 1 січня 1900 р., Результат повертає значення помилки #VALUE!

Як працює ця формула

Тут для перетворення часу Excel у комірці B3: B6 у час Unix використовуйте формулу нижче:

прес Що натомість? Створіть віртуальну версію себе у , щоб отримати перший результат, а потім перетягніть маркер автоматичного заповнення до комірки C7.

Пояснення

Функція дати: створити дату з номером року, номером місяця та номером дня.

Формула (B3-DATE (1970,1,1)) використовується для обчислення різниці між заданою датою і часом Unix.

86400: Як ми знали, один день = 24 години, одна година = 60 хвилин і одна хвилина = 60 секунд, тому один день = 24 * 60 * 60 = 86400 секунд.

Перетворити мітку часу Unix на час Excel

Загальна формула:

Синтакст та аргументи

значення, що повертається

Формула повертає номер серії, тоді вам потрібно відформатувати номер серії у форматі datetime. Зазвичай результат є позитивним числовим числом, якщо результат є негативним, він повернеться до ряду ##### після форматування його як дати та часу.

Як працює ця формула

Щоб перетворити час Unix у клітинці B3: B6 у час Excel, використовуйте формулу нижче:

прес Що натомість? Створіть віртуальну версію себе у клавішу та перетягніть маркер автоматичного заповнення до комірки C6.

Потім тримайте вибрані результати, потім натисніть Ctrl + 1 щоб відобразити Формат ячеек діалогове вікно, під Номер вкладка, виберіть Дата від Категорія списку, а потім у правому розділі тип, виберіть один потрібний формат.

Натисніть OK, тоді результати формули будуть відформатовані як дата-час.

Пояснення

Функція дати: створити дату з номером року, номером місяця та номером дня.

Формула (B3 / 86400) + DATE (1970,1,1) отримує загальну кількість днів між датою і датою Unix.

86400: Як ми знали, один день = 24 години, одна година = 60 хвилин, одна хвилина = 60 секунд, тому один день = 24 * 60 * 60 = 86400 секунд. І навпаки, дата / 86400 отримає номер дня.

Відносні формули

• Перетворити десяткові хвилини у формат часу
  Цей підручник містить формулу для перетворення десяткових хвилин у час Excel.
• Перетворення десяткових секунд у час
  Щоб перетворити десяткові секунди в час Excel в Excel, вам може допомогти формула.
• Перетворення часу в десяткові години
  Тут наведено формулу, яка перетворює час у десяткові години в Excel та пояснює, як працює формула.
• Перетворення часу в десяткові хвилини
  У цьому посібнику він містить формулу для перетворення часу в десяткові хвилини в Excel.

Відносні функції

• Функція Excel IF
  Перевірте задані умови, потім повертає відповідні значення
• Функція Excel VALUE
  Перетворити текст на число.
• Функція МІСЯЦЬ Excel
  МІСЯЦ використовується, щоб отримати місяць як ціле число (від 1 до 12) з дати.
• Функція Excel ДЕНЬ
  Функція DAY отримує день як число (від 1 до 31) від дати
• Функція Excel YEAR
  Функція YEAR повертає рік на основі заданої дати у 4-значному форматі серійного номера.