Функции для сотворения и отображения дат и времени в Excel

Мы продолжаем учить функции группы Дата и время в Excel. Если Вы не читали наш прошедший урок по функциям, рекомендуем к нему обратиться. В этом уроке мы познакомимся с 4-мя весьма полезными функциями, которые разрешают не попросту показывать, да и создавать даты и время в Excel.

СЕГОДНЯ()

Не имеет аргументов и возвращает порядковое число текущей даты, которая берется из системного времени Вашего компа. Если функция применяется к ячейке с форматом Общий, то к результату будет использован формат Дата.

ТДАТА()

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

Результаты функций СЕГОДНЯ() и ТДАТА меняются при любом пересчете рабочего листа Excel.

В качестве примера можно привести формулу, которая вычисляет текущее время в Excel:

=ТДАТА()-СЕГОДНЯ() – в этом случае формула возвратит десятичное число (долю суток). Чтоб узреть итог, как на рисунке ниже, нужно к ячейке A1 применить формат времени.

Воспринимает три неотклонимых аргумента (год, месяц и денек) и возвращает порядковый номер даты. Если до ввода данной функции ячейка имела формат Общий, то итог будет отформатирован как дата.

Значение аргумента Денек (либо Месяц) быть может, как положительным, так и отрицательным числом. Если величина аргумента превосходит количество дней в текущем месяце (количество месяцев в году), то происходит повышение даты:

Если же величина аргумента является отрицательным числом, то происходит уменьшение даты:

Аргументы функции ДАТА могут содержать математические операции, также остальные функции Excel. К примеру, чтоб прибавить к определенной дате 1 год 5 месяцев и 17 дней, можно пользоваться последующей формулой:

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

ВРЕМЯ()

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

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

Принципиально отметить, что функция ВРЕМЯ не может возвращать значение, превышающее 23:59:59. При достижении этого значения функция обнуляется.

Функции ДАТА и ВРЕМЯ можно употреблять вместе:

В данном примере к ячейке D1 использован формат даты и времени.

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

Функция Excel СЕГОДНЯ — синтаксис и главные внедрения

Функция СЕГОДНЯ в Excel делает конкретно то, на что показывает ее имя — возвращает нынешнюю дату.

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

=СЕГОДНЯ()

Вы сможете отформатировать возвращаемое значение в любом интегрированном или случайном формате. Скажем, таковым образом:

3 вещи, которые вы должны знать о использовании СЕГОДНЯ() в Эксель:

1. Она изменчивая. Это значит, что она повсевременно обновляется при любом открытии или изменении рабочего листа.
2. Если итог не обновляется сам собой, то быстрее всего, автоматический пересчет просто отключен в вашей книжке. Чтоб опять активировать его, перейдите на вкладку Файл – Характеристики — Формулы — «Характеристики вычислений» и изберите «Автоматом». Кандидатура — просто нажмите F9.
3. Желаете вставить текущую дату и время? Возьмите ТДАТА().

Как вставить нынешнюю дату?

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

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

Для наилучшей ясности вы сможете соединить её с объяснительным текстом:

=СЦЕПИТЬ(«Сегодня — «;ТЕКСТ(СЕГОДНЯ(); «dd mmmm, yyyy»))

Так как в Excel время хранится в виде чисел, объединение текста с формулой «в лоб» приведет к абракадабре, скажем, «Сегодня — 42965». Избегая этого, мы вкладываем ее в функцию ТЕКСТ для отображения в подходящем формате.

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

• Для вставки текущей даты: Ctrl +;
• Вставить время: Ctrl + Shift +;
• Для ввода дат и времени : Ctrl +; потом пробел, а потом Ctrl + Shift +;

Примечание. Чтоб это сработало, при загрузке Excel у вас обязана быть включена британская раскладка клавиатуры. Почему так – загадка Microsoft…

Как употреблять функцию СЕГОДНЯ в Excel – примеры.

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

Добавить (отнять) деньки от текущего момента.

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

Добавляя 7 дней, сделайте так:

Вычитаем 7 дней:

Исключаем выходные (субботу и воскресенье) из ваших расчетов, вложите СЕГОДНЯ() в РАБДЕНЬ(), которая работает лишь с будними деньками.

Добавляем 7 рабочих дней:

Вычитаем 7 рабочих дней:

Последующий снимок экрана указывает результаты:

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

Сколько дней до или опосля определенного момента?

Подсчитывая, сколько дней осталось до пришествия какого-то действия, вычтите нынешнее из грядущего:

Ее можно передать конкретно в выражение в формате, понятном для Эксель, или при помощи ДАТА(), или в качестве ссылки на ячейку.

Узнаем, сколько осталось до 31 декабря 2020 года, примените один из методов:

=A2-СЕГОДНЯ()

=ДАТА(2020;12;31)-СЕГОДНЯ()

=»31/12/2020″-СЕГОДНЯ()

Они все молвят нам, что на момент написания (19 апреля 2020 года) до конца 2020 года оставалось 256 суток:

Посчитаем, сколько прошло суток с определенной даты. Вычтем из нее уже прошедшую:

Узнаем, сколько дней прошло с 1 января 2020 года. Есть несколько вариантов:

=СЕГОДНЯ()-A2

=СЕГОДНЯ()-ДАТА(2020;1;1)

=СЕГОДНЯ()-«1/1/2020»

Высчитать число месяцев до или опосля определенного момента.

Для расчета сколько месяцев меж 2-мя датами, применяется РАЗНДАТ() (DATEDIF в британском варианте):

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

Указав интересующий нас срок в ячейке A4 то наши вычисления можно упростить.

Подсчитываем месяцы с определенной точки в прошедшем:

Месяцы до определенной отметки в будущем :

Направьте внимание, что тут рассчитывается количество полных месяцев.

Высчитать годы до и опосля определенного действия.

Советы для вычисления лет подобны тем, которые дискуссировались в приведенном выше примере. Разница в том, что вы используете параметр «у», для вывода числа полных лет:

Годы, прошедшие опосля какого-то действия:

Годы до грядущего действия:

Записав время не в само выражение, а в ячейку A4, получим:

Полных лет опосля:

Полных лет до:

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

Потому, если расчет приходится на денек рождения, целенаправлено добавить 1 к первому аргументу. По другому станете на 1 год молодее 🙂

Для получения доборной инфы о РАЗНДАТ() см. Как посчитать, сколько дней меж датами .

Считаем возраст.

Зная чей-то год рождения, вы сможете отнять этот год из текущего, и выяснить возраст человека:

Если человек родился в 1991году, можно посчитать так:

=ГОД(СЕГОДНЯ())-1991

Вы сможете ввести год рождения в отдельной ячейке и потом сослаться на неё:

Вы сможете узреть несколько остальных примеров расчета возраста в этом уроке.

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

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

Чтоб отыскать ближайшую прошедшую, поначалу «отфильтруйте» все, превосходящие текущую, а потом обработайте функцией МАКС(), и получите самую большую посреди оставшихся.

С примерами в ячейках от A2 до A10 это будет смотреться последующим образом:

Чтоб отыскать точку наиблежайшего грядущего, обусловьте сначала все те, которые еще не наступили, а потом с помощью МИН() получите меньшее посреди их:

Получить хоть какой денек, ближний к нынешнему.

Определяя ближний к нам денек в смешанном перечне прошедших и будущих событий, используйте традиционную комбинацию ИНДЕКС+ПОИСКПОЗ с несколькими переменами:

ИНДЕКС( спектр; ПОИСКПОЗ(МИН (ABS ( спектр — СЕГОДНЯ ())); ABS ( спектр — СЕГОДНЯ ()); 0))

• МИН(ABS( спектр — СЕГОДНЯ())). Поначалу вы вычитаете животрепещущую дату из каждой позиции в списке. Потом функция ABS() возвращает различия в виде абсолютных значений без учета их знака. МИН находит малое значение, которое потом применяется в качестве аспекта поиска.
• ABS(спектр — СЕГОДНЯ()). Вы вычитаете ее из каждой позиции в перечне и возвращаете массив абсолютных значений. В этом массиве ИНДЕКС+ПОИСКПОЗ отыскивает аспект поиска.

В этом примере выражение воспринимает последующий вид:

=ИНДЕКС($A$2:$A$10; ПОИСКПОЗ(МИН(ABS($A$2:$A$10 — СЕГОДНЯ())); ABS($A$2:$A$10 — СЕГОДНЯ()); 0))

На снимке экрана ниже показан итог:

Примечание. Все три примера для получения наиблежайшей даты являются формулами массива, потому их нужно вводить, нажав Ctrl + Shift + Enter .

Выделяем цветом животрепещущее время в таблице.

При работе с длинноватым перечнем событий или разработке собственного календаря в Эксель быть может целесообразным выделение цветом текущего момента. Для этого сделайте правило условного форматирования с сиим выражением:

Где B1 — самая левая верхняя ячейка области, к которой применяется правило.

Итог может смотреться приблизительно так:

Направьте внимание, что символ $ дозволяет закрашивать весь столбец. Нужна лишь 1-ая строчка? Запишите просто — B1.

Подробные шаги по настройке правила условного форматирования можно отыскать тут: Как применить условное форматирование в Excel .

Итак, мы с вами разглядели главные методы внедрения функции СЕГОДНЯ() в Excel. Если мы что-то упустили – пишите в комментах.

Может быть, для вас также будет любопытно:

Как вывести месяц из даты — На примерах мы покажем, как получить месяц из даты в таблицах Excel, конвертировать число в его заглавие и напротив, также почти все другое. Думаю, вы уже понимаете, что дата в…
Как стремительно вставить нынешнюю дату в Excel? — Это управление указывает разные методы ввода дат в Excel. Узнайте, как вставить нынешнюю дату и время в виде статической метки времени либо динамических значений, как автоматом заполнять столбец либо строчку…
Количество рабочих дней меж 2-мя датами в Excel — Достаточно всераспространенная задачка: найти количество рабочих дней в период меж 2-мя датами – это личный вариант расчета числа дней, который мы уже разглядывали ранее. Тем не наименее, в Excel для…
Как посчитать количество дней меж датами в Excel? Примеры и советы. — Из этого обзора вы узнаете несколько стремительных и обычных методов подсчета, сколько дней проходит меж 2-мя датами в Excel. Для вас любопытно, сколько составляет разница в деньках, месяцах и годах меж…
8 методов — как посчитать возраст в Excel по дате рождения. — В Excel нет специальной функции, чтоб посчитать возраст, но существует несколько разных методов его определения на базе даты рождения. Вы узнаете несколько приемов, как высчитать его в количестве полных лет,…
Как конвертировать текст в дату в Excel либо извлечь ее из сложной фразы? — На данный момент вы научитесь употреблять функции Excel для преобразования текста в дату. Вы также узнаете, как это создать без использования формул. Так как Excel — не единственное приложение, которое вы используете, время от времени…

Расчет количества дней меж датами в Эксель

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

Расчет разности дат

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

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

1. Для начала выделяем мотивированной спектр ячеек, воспользовавшись зажатой левой клавишей мыши.

• по дефлоту мы должны оказаться во вкладке “Число” (если это не так, переключаемся в нее);
• в перечне “Числовые форматы” находим позицию “Дата” и кликаем на нее;
• в поле “Тип” избираем подходящий эталон представления данных, в согласовании с которым они будут отображаться на листе;
• жмем OK.

Способ 1: внедрение формулы вычитания

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

1. Вносим две даты, меж которыми будем определять разницу в деньках, в две различные ячейки отформатированного ранее спектра.
2. Сейчас выделим ячейку, в какой планируем показать итог нашего вычисления.
   ВАЖНО: Количество дней обозначается обычным целым числом, как следует, ячейка для его вывода обязана иметь хороший от “Дата” формат, а конкретно – “Общий“. В неприятном случае приобретенный итог будет отображаться в формате даты, что не дозволит оценить правильность проведенных вычислений. Для определения текущего формата ячейки довольно посмотреть на соответственное поле, расположенное в разделе инструментов “Число” на ленте вкладки “Основная“.

• в итоговую ячейку вводим символ “=“;
• кликаем по ячейке с конечной датой (наиболее поздней);
• вводим с клавиатуры знак “–” (символ минус);
• кликаем по ячейке с исходной датой.

Способ 2: функция РАЗНДАТ

Последующий метод – это определение различия меж 2-мя датами с помощью функции РАЗНДАТ.

Функция имеет последующий синтаксис: =РАЗНДАТ(начальная_дата;конечная_дата;единица).

Если с 2-мя первыми аргументами все понятно – их сущность соответствует наименованию, то на 3-ем (“единица“) стоит тормознуть подробнее, потому что конкретно он дозволяет разнообразить итог использования функции, выбирая единицу расчета различия меж датами:

• “d” — количество дней меж датами;
• “m” — количество полных месяцев меж датами;
• “y” — количество полных лет меж датами;
• “YM” — разница меж датами в месяцах;
• “MD” — разница меж датами в деньках, без учета месяцев и лет;
• “YD” — разница меж датами в деньках, без учета лет.

В нашем случае покажем пример внедрение аргумента “YD”, потому что целью расчета является определение количества дней меж датами в границах года.

Основное отличие от обычного вычитания одной даты из иной – в порядке следования аргументов функции: в качестве первого аргумента выбирается исходная (наиболее ранешняя дата), а в качестве второго – конечная (наиболее поздняя). Опишем ниже метод вычислений.

1. Избираем мотивированную ячейку, проверяем ее формат по способу, описанному чуть повыше.
2. Описываемая функция отсутствует в Мастере функций, потому вводим ее в ячейку вручную, строго придерживаясь синтаксиса.
3. Нажимаем Enter и проверяем приобретенный итог. Как мы лицезреем, он аналогичен тому, который мы получили, используя ординарную формулу вычитания. Означает все изготовлено правильно.

Способ 3: функция ЧИСТРАБДНИ для определения количества рабочих дней

3-ий описываемый способ дозволяет вычислить количество дней с доп условием – исключением из него нерабочих (выходных и торжественных) дней.

В данном способе употребляется функция ЧИСТРАБДНИ со последующим синтаксисом: =ЧИСТРАБДНИ(нач_дата;кон_дата;[праздники]).

1-ые два аргумента подобны функции РАЗНДАТ. Крайний аргумент является опциональным, другими словами функция будет работать и без его указания. Но конкретно он дозволяет учитывать в аспектах вычисления нерабочие торжественные деньки, потому что по дефлоту из полного количества уберутся лишь субботы и воскресенья.

Приступаем к вычислениям:

1. Потому что описываемая функция находится в перечне Мастера функций, воспользуемся им для большей наглядности: встаем на ячейку, куда будем выводить итог и щелкаем по кнопочке “Вставить функцию” (fx), расположенной слева от строчки формул.
2. В открывшемся окне Мастера функций:
   • избираем категорию “Полный алфавитный список”;
   • находим и кликаем в перечне операторов по строке “ЧИСТРАБДНИ”;
   • жмем ОК.
3. Перед нами покажется окно аргументов функции. Тут можно ввести адреса ячеек вручную либо, находясь в поле для наполнения значений аргумента, кликнуть по подходящей ячейке на листе. Выбрав исходную и конечную дату, также указав торжественный денек (адресок ячейки), жмем ОК.
4. Представим, в сей раз мы заблаговременно не задали формат итоговой ячейки как “Общий”. Тогда мы получим итог, отображающийся в виде даты.
5. Как следует, нужно опять проверить формат по методу, описанному чуть повыше и поменять его на “Общий”.

Заключение

При помощи обрисованных выше способов можно довольно просто посчитать количество дней меж 2-мя датами, как с наличием доп критериев, так и без их. Формула вычитания и функция РАЗНДАТ отлично подходят для обычного подсчета дней, а функция ЧИСТРАБДНИ поможет с расчетом количества конкретно рабочих дней меж датами. Окончательный выбор зависит от поставленной перед юзером задачки.