Как добавить или вычесть дату и время в 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 AM в другую ячейку и отформатируйте эту ячейку как дату. времени. Теперь скопируйте две ячейки и вставьте в другую строку. Выделите обе ячейки, щелкните правой кнопкой мыши и отформатируйте их как простые числа. Вы можете выбрать без десятичной дроби для первой ячейки и 5 десятичных знаков для второй ячейки. Это должно выглядеть, как показано ниже, с указанием 31 -Mar-18 9:45 = 43190,40625 в Excel.
Преобразование даты в числа
Вы можете просто ввести 43190,40625 в ячейку и изменить формат даты со временем. Excel автоматически преобразует число в формат даты со временем 31 марта 18 9:45. Помните, что число без десятичных знаков считается 00:00:00 в формате времени. Только десятичное число считается временем в Excel.
Формат даты, числа и времени в Excel
Связанный: Как изменить регистр текста в документе Office и электронных письмах?
Расчет даты и времени в Excel
Давайте добавим несколько дней к существующей дате. Например, добавив 10 дней к 01.01.2018, вы получите 1/11/2018. Введите 01.01.2018 в ячейку B3, введите = 10 + B3 в ячейку B4 и нажмите клавишу ВВОД. Вы увидите результат как 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.
Сложение и вычитание времени
Вместо работы со ссылкой на ячейку вы можете использовать прямую дату или время в двойных кавычках для расчета, как показано ниже:
«12/24/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
Функция WEEKDAY () имеет дополнительный тип возвращаемого параметра. Это требуется для определения первого рабочего дня недели. Если вы введете = WEEKDAY (C4,2) в приведенном выше примере, вы получите 1 в качестве ответа. Это потому, что 2 означает, что неделя начинается с понедельника по воскресенье. Следовательно, понедельник в контрольной ячейке возвращается как 1, что означает, что это первый рабочий день.
Функция буднего дня Excel
Также существует функция DAYS (), которая позволяет вычислить количество дней между любыми двумя заданными датами. Синтаксис: = ДНЕЙ (конечная_дата, начальная_дата).
По теме: Как изменить название компании в офисных документах?
Основные функции времени
Подобно функциям даты, Excel также поддерживает различные функции времени – СЕЙЧАС (), ЧАС (), МИНУТА () и СЕКУНДА ().
Функции времени в Excel
Функции ДАТА и ВРЕМЯ
Функция ДАТА просто используется для сбора года, месяца и даты из разных ссылочных ячеек и отображения их в формате даты. Точно так же ВРЕМЯ – это функция для объединения часов, минут и секунд из разностных эталонных ячеек.
Пример функции даты и времени
Использование функции DATEDIF для вычитания даты в Excel
Помимо всех этих формальных функций даты и времени, Excel также поддерживает неформальную функцию DATEDIF. Согласно документу поддержки Microsoft, эта функция по-прежнему работает для поддержки старых книг, перенесенных в Excel из Lotus 1-2-3. Но он может давать неверные результаты в разных ситуациях, поэтому использовать его не рекомендуется.
Функция РАЗНДАТ помогает вычислить количество лет, месяцев или дат между любыми заданными двумя датами. Синтаксис следующий:
РАЗНДАТ (дата начала, дата окончания, единица)
Стоимость единицы может быть одной из следующих:
- Y – количество полных лет между начальной и конечной датами.
- M – количество полных месяцев
- D – количество дней
- MD – количество дней между начальной и конечной датами без учета лет и месяцев. Это значение обычно дает неверные результаты.
- YM – количество месяцев между датами начала и окончания, без учета дней и лет.
- YD – количество дней между двумя заданными датами без учета лет.
Примеры вычитания даты с использованием DATEDIF:
Использование функции РАЗНДАТ в Excel
Обратите внимание, что DATEDIF является вспомогательной функцией для целей миграции, поэтому при вводе функции в ячейку вы не увидите текст справки по формуле.
Источник записи: www.webnots.com