Как добавить или вычесть дату и время в Microsoft Excel?

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

Сериализация информации о дате и времени в Excel

В Excel используются две системы дат: одна начинается с 1 января 1900 года, а другая — с 1 января 1904 года. Последние версии Excel, как для Windows, так и для Mac, по умолчанию используют систему дат 1900. В этой статье мы объясним все функции с системой дат 1900. Процесс вычислений остается таким же, даже если вы используете систему дат 1904, в то время как внутренняя сериализация дат в Excel может измениться.

В системе дат 1900 года Excel начинает дату с 01.01.1900 (01 января 1900 года) и назначает сериализацию как 1. Таким образом, дата 01.10.1900 (10 января 1900 года) будет преобразована в число 10 в Excel и 31.03.2018 (31 Март 2018 г.) будет сериализован как 43190.

Точно так же информация о времени сохраняется внутри как десятичная. Например, 0,5 означает полдень, 0,25 — 6 утра и 0,75 — 6 вечера в Excel.

А теперь давайте посчитаем 31 марта 2018 г., 9:45. Откройте лист Excel и введите 31 марта 2018 г. в одну ячейку. Щелкните правой кнопкой мыши и выберите «Форматировать ячейки…». На вкладке «Число» выберите формат даты (без времени) для этой ячейки. Введите 31-марта-2018 09:45:00 в другую ячейку и отформатируйте эту ячейку как дату, показывающую время.

Теперь скопируйте две ячейки и вставьте в другую строку. Выделите обе ячейки, щелкните правой кнопкой мыши и отформатируйте их как простые числа. Вы можете выбрать без десятичной дроби для первой ячейки и 5 десятичных знаков для второй ячейки. Это должно выглядеть так, как показано ниже, 31 марта 18 9:45 = 43190,40625 в Excel.

Преобразование даты в числа

Преобразование даты в числа

Вы можете просто ввести 43190,40625 в ячейку и изменить формат даты со временем. Excel автоматически преобразует число в формат даты со временем 31 марта 18 9:45.

Помните, что число без десятичных знаков считается 00:00:00 в формате времени. Только десятичное число считается временем в Excel.

Формат даты, числа и времени в Excel

Формат даты, числа и времени в Excel

Расчет даты и времени в Excel

Давайте добавим несколько дней к существующей дате. Например, прибавив 10 дней к 01.01.2018, вы получите 1/11/2018. Введите 01.01.2018 в ячейку B3, введите = 10 + B3 в ячейку B4 и нажмите Enter. Вы увидите результат 1/11/2018.

Добавление дней к дате

Добавление дней к дате

Простое добавление двух дат может привести к абсолютному сложению в Excel, что не имеет смысла. Например, если вы добавите 31/31/18 и 3/1/18, получится 4/2/36. Но вы можете вычесть две даты и показать разницу дней в числовом формате. Например, 3/1/18 — 1/31/18 покажет результат как 29 дней.

Вычитание двух дат

Вычитание двух дат

Как и в случае с датами, вы можете складывать или вычитать две ячейки в формате времени. Убедитесь, что вы всегда вычитаете меньшее значение из более высокого значения, а формат ячеек соответствует времени. Например, 18:00 — 15:00 должно привести к 3:00.

Сложение и вычитание времени

Сложение и вычитание времени

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

= «24.12.2018 ″ -« 12.05.2018 ″ (в результате вы получите 19)
= «20:00» — «C9» (в результате вы увидите 18:45, поскольку ячейка C9 имеет значение 1:15)

Использование СЕЙЧАС () и СЕГОДНЯ () для расчетов

Вы также можете использовать две функции для расчета даты и времени. Now () используется для добавления или вычитания времени из текущего времени, а Today () используется для добавления или вычитания даты из текущей даты. Приведем несколько примеров:

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

= NOW () вернет текущее время

= СЕЙЧАС () — «02:00 ″ вычитает два часа из текущего времени.

= СЕЙЧАС () + «03:00» добавит три часа к текущему времени.

Перед использованием функции СЕГОДНЯ () убедитесь, что ячейка отформатирована как формат даты:

= СЕГОДНЯ () вернет сегодняшнюю дату

= СЕГОДНЯ () — 3 вычитает три дня из сегодняшнего дня

= СЕГОДНЯ () + 5 добавит пять дней с сегодняшнего дня

Основные функции даты

В Microsoft Excel есть следующие функции даты — СЕГОДНЯ (), ГОД (), МЕСЯЦ (), ДЕНЬ () и ДЕНЬ НЕДЕЛИ (). Пример использования показан ниже. Введите функцию = СЕГОДНЯ () и нажмите клавишу ввода в ячейке C4. Вы можете использовать все остальные функции со ссылкой на ячейку C4.

Функции даты в Excel

Функции даты в Excel

Функция WEEKDAY () имеет дополнительный тип возвращаемого параметра. Это необходимо для определения первого рабочего дня недели. Если вы введете = WEEKDAY (C4,2) в приведенном выше примере, вы получите 1 в качестве ответа. Это потому, что 2 означает, что неделя начинается с понедельника по воскресенье. Следовательно, понедельник в контрольной ячейке возвращается как 1, что означает, что это первый рабочий день.

Функция буднего дня Excel

Функция буднего дня Excel

Также существует функция DAYS (), которая позволяет вычислить количество дней между любыми двумя заданными датами. Синтаксис: = ДНЕЙ (конечная_дата, начальная_дата).

Основные функции времени

Подобно функциям даты, Excel также поддерживает различные функции времени — СЕЙЧАС (), ЧАС (), МИНУТА () и СЕКУНДА ().

Функции времени в Excel

Функции времени в Excel

Функции ДАТА и ВРЕМЯ

Функция ДАТА просто используется для сбора года, месяца и даты из разных ссылочных ячеек и отображения их в формате даты. Точно так же ВРЕМЯ — это функция для объединения часов, минут и секунд из разностных эталонных ячеек.

Пример функции даты и времени

Пример функции даты и времени

Использование функции DATEDIF для вычитания даты в Excel

Помимо всех формальных функций даты и времени, Excel также поддерживает неформальную функцию DATEDIF. Согласно документу поддержки Microsoft, эта функция по-прежнему работает для поддержки старых книг, перенесенных в Excel из Lotus 1-2-3. Но он может давать неверные результаты в разных ситуациях, поэтому не рекомендуется использовать.

Функция РАЗНДАТ помогает вычислить количество лет, месяцев или дат между любыми заданными двумя датами. Синтаксис следующий:

= РАЗНДАТ (дата начала, дата окончания, единица измерения)

Стоимость единицы может быть одной из следующих:

  • Y — количество полных лет между начальной и конечной датами.
  • M — количество полных месяцев
  • D — количество дней
  • MD — количество дней между начальной и конечной датами без учета лет и месяцев. Это значение обычно дает неверные результаты.
  • YM — количество месяцев между датами начала и окончания, без учета дней и лет.
  • YD — количество дней между двумя заданными датами без учета лет.

Примеры вычитания даты с использованием DATEDIF:

Использование функции РАЗНДАТ в Excel

Использование функции РАЗНДАТ в Excel

Обратите внимание, что DATEDIF является вспомогательной функцией для целей миграции, поэтому при вводе функции в ячейку вы не увидите текст справки по формуле.

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *