Как работать с мастером формул даты и времени

Работа со значениями, связанными со временем, требует глубокого понимания того, как функции ДАТА, РАЗНДАТ и ВРЕМЯ работают в Excel. Vs подробно рассмотрели ранее, как это можно сделать при помощи формул: 8 способов как посчитать возраст в Excel по дате рождения и Разница между датами в Excel. Альтернативный вариант позволяет нам обойтись без формул.  

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

Запустите надстройку и выберите нужное действие.

Сначала выберите ячейку, в которой вы хотите получить итоговую формулу. Затем запустите надстройку, щелкнув значок мастера формул даты и времени на вкладке Ablebits Tools в группе Date&Time.

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

Вы увидите окно надстройки с четырьмя возможными режимами вверху: Добавить (Add), Вычесть (Subtract) , Разница (Difference) и Возраст (Age). Выберите операцию, которую хотите выполнить, щелкнув по ней:

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

В зависимости от вашей задачи нажмите Добавить или Вычесть в верхней части панели мастера формул даты и времени:

1Выберите или введите дату начала - день, из которого вы хотите вычесть или добавить единицы даты и времени.

2Вы можете выбрать дату в календаре, щелкнув значок стрелки «Развернуть» в поле даты:

3Кроме того, вы можете вставить ссылку на ячейку в поле с помощью значка « Выбрать диапазон» .

Для начала вы даже можете использовать ячейки со стандартными функциями Excel СЕЙЧАС и СЕГОДНЯ.

Примечание. Если вы оставите поле пустым или ячейка, которую вы в нём укажете, будет пуста, то программа будет использовать самую раннюю дату, используемую в Excel, в качестве основы для вычислений: 1/1/1900.

4 Чтобы добавить или вычесть годы (Years), месяцы (Months), недели (Weeks) или дни (Days), щелкните соответствующее поле и используйте появившийся там значок «Выбрать диапазон». Или же вы просто можете ввести число вручную. Можно использовать все поля сразу или заполнить только одно из них.

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

5  Щелкните Показать поля времени (Show time fields), чтобы также ввести единицы времени:

Как и в случае с полями даты, описанными выше, можно кликнуть нужное поле и использовать значок Выбрать диапазон, чтобы указать ячейку с необходимым значением на листе Excel. Или просто вводите числа вручную. При необходимости вы можете использовать все поля сразу.

6 Область результатов формулы (Formula result) позволяет сразу увидеть полученную в результате вычислений дату или время. Она также покажет ошибку, если какое-либо из полей введены данные в нечисловом формате, например к текст. 

Вы можете скопировать детали результата, щелкнув правой кнопкой мыши поле предварительного просмотра.

Выбрав аргументы, нажмите кнопку «Вставить формулу (Insert Formula)» . Надстройка вставит полученную формулу в таблицу Excel и установит формат даты для этой ячейки. Затем вы можете скопировать формулу, если захотите применить ее к другим ячейкам:

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

Как найти разницу между двумя датами.

Щелкните раздел « Разница (Difference)» в верхней части панели надстройки:

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

2 Щелкните значок стрелки, чтобы открыть календарь и выбрать дату из него.

3 Используйте значок «Выбрать диапазон», чтобы указать на ячейку на листе.

4 Выберите единицы измерения, в которых нужно выразить подсчитанную разницу. Откройте раскрывающийся список « Разница в (Difference in)» и выберите вид, в котором вы хотите увидеть результат. Вы видите все возможные варианты, от простого подсчета дней, месяцев, недель и лет до комбинаций единиц, например «Г + М + Д». Кроме того, вы можете получить результат в днях плюс единицы времени (часы, минуты, секунды): «Д + ч + м» и «Д + ч + м + с».

5 Если вы хотите опустить годы в расчетах и ​​показать разницу только в месяцах и днях, выберите «Исключить годы (Exclude years)». Чтобы увидеть разницу в днях и пропустить подсчет месяцев, отметьте поле «Исключить месяцы (Exclude months)» .

Допустим, вы пытаетесь сравнить «01.01.2015» и «01.03.2017». Если вы решите отображать разницу в месяцах, вы получите «26 месяцев». Если вы выберете «Y + M» для результата, вы увидите «2 года 2 месяца». Чтобы получить разницу в месяцах без лет, отметьте « Исключить годы», и вы получите «2 месяца».

6 Используйте дополнительные параметры для определения деталей вычисляемых единиц:

• • Установите флажок Добавить текстовые метки, чтобы увидеть описание чисел в результате. Например, «5 недель 3 дня». Если опция метки не выбрана, вы увидите числа, разделенные системными разделителями, например, «5/3».
  • Если результат разницы дат может иметь нулевые значения, например «1 неделя 0 дней», вы можете скрыть их, установив флажок «Не показывать нулевые единицы (Do not show zero units)» и вместо этого получить «1 неделя».
  • Функция РАЗНДАТ в Excel вернет ошибку, если дата 1 больше даты 2. Мастер дат по умолчанию игнорирует порядок дат, но вы можете использовать опцию Negative result для отображения отрицательного результата, если Дата 1> Дата2 .

    Например: вы пытаетесь увидеть количество дней между двумя датами в Excel, 15.01.2018 и 29.12.2017. Выберите эту опцию, чтобы получить «-17 дней», если 15.01.2018 выбрано как Дата 1. Если этот параметр не отмечен, вы получите разницу в «17 дней» независимо от того, какую дату вы выберете первой. 

  • Вы можете установить флажок Более компактная формула (More compact formula), если хотите, чтобы формула была короче. Если вы установите этот флажок, ваш результат будет выглядеть так: «1 month(s) and 3 day(s) ». Если вы не установите этот флажок, формула будет различать единственное и множественное число, и вы получите «1 month and 3 day».

Щелкните  Вставить формулу (Insert formula),  чтобы увидеть результат в Excel и использовать его для дальнейших вычислений.

Как рассчитать возраст в Excel.

Перейдите на четвертую вкладку, чтобы рассчитать возраст в Excel:

1 Укажите дату рождения: выберите ячейку, которая ее содержит, или введите дату вручную.

2 Вы также можете щелкнуть значок стрелки «Развернуть» и выбрать дату в раскрывающемся календаре.

3 Если у вас есть таблица дат на вашем листе, используйте значок «Выбрать диапазон»,  чтобы указать ячейку с первой датой, или просто щелкните поле и выберите дату на листе.

4 Используйте поле « Возраст от даты (Age at the date of)», чтобы выбрать способ подсчета возраста:

1. • Считать для  сегодняшней даты .
     Эта опция рассчитает текущий возраст с даты рождения. Другими словами, он покажет возраст человека сравнив его день рождения с сегодняшним днем.
   • Рассчитайте возраст на  определенную дату .
     Этот параметр позволяет получить формулу Excel для расчета возраста между двумя датами: датой рождения и любой датой, указанной в поле. Вы можете ввести вторую дату в системном формате, найти ее в календаре с помощью  значка стрелки «Развернуть» или выбрать дату на  листе Excel с помощью значка « Выбрать диапазон» .

5 Выберите способ отображения результата. Вы можете преобразовать возраст человека в  дни, недели, месяцы, годы или получить точное число в  годах + месяцах + днях . В  раскрывающемся списке « Возраст (Age)» выберите наиболее подходящий вариант .