Excel если дата меньше сегодня
Сложить даты в Excel, вычесть даты, прибавить к дате дни, месяцы, года, посчитать стаж, возраст, время — все это можно сделать формулами. Как посчитать дату выхода сотрудника из отпуска или сколько дней осталось до отпуска, посчитать возраст, расчитать дату срока оплаты счета или окончания договора, посчитать рабочие дни и многие, многие другие действия с датами и временем можно проводить в Excel.
Все расчеты с датами описаны в нескольких статьях. Смотрите перечень статей в конце статьи в разделе "Другие статьи по этой теме".
Внимание!
Если формулы не будут работать, проверьте формат дат. Бывает, при копировании из других программ в Excel даты вставляются в текстовом формате. Тогда формулы не считают. Чтобы формулы считали, нужно изменить формат ЗНАЧЕНИЙ на числовой — это не формат ячейки. Как это сделать, читайте в статье " Преобразовать текст в дату Excel " .
Для подсчета дат нужно сделать таблицу в Excel и установить в ней формулы. Этим мы сейчас и займемся.
Как простыми способами прибавить к дате или вычесть из даты дни, месяци или года, смотрите в статье "Работа с датами в Excel".
Как посчитать разные периоды стажа, других разных дат и, затем, сложить их вместе, чтобы узнать общее количество дней, др., смотрите в статье «Как в Excel посчитать стаж ».
Для работы с датами, их можно записать по–разному, для разных формул. И формат ячеек с формулами должен быть определенный. Все это прописано в тексте – обращайте внимание.
1. Разница дат в Excel высчитывается так.
Столбец А – пишем дату начала периода, столбец В – пишем дату окончания периода. В столбце А и В устанавливаем формат ячейки «Дата» . Где найти эту функцию и как установить её, смотрите в статье «Число Excel. Формат ».
Столбец С – будет стоять формула и результат подсчета. Здесь будет записана дата в таком виде, как мы её пропишем в формуле.
В ячейке А6 пишем первую дату (начало периода).
В ячейке В6 пишем вторую дату (конец периода).
В ячейке С6 пишем формулу:
=РАЗНДАТ(A6;B6;"y")&"г."&РАЗНДАТ(A6;B6;"ym")&"мес."&РАЗНДАТ(A6;B6;"md")&"дн."
Эту формулу пишем вручную (можно здесь скопировать, заменить адреса ячеек), п.ч. в русскоязычной версии Microsoft Excel, она не видна в перечне функций, но работает.
Пояснения к формуле :
РАЗНДАТ – это название функции.
y – считает года
ym – считает месяца
md – считает дни
«г.», «мес.», «дн.» написанные между знаками & — так будут подписаны года, месяца и дни в итоговой дате. Можно между кавычками написать по-разному – полное слово или просто поставить точку. Смотрите пример. В ячейке С6 стоит формула, искомая дата.
А здесь, вместо букв «г.», «мес.», «дн.» просто поставили точку.
Если в ячейку В6 поставим текущую дату , то сможем узнать стаж по сегодняшнее число. Например, узнать стаж работника на предприятии. Каждый день стаж будет пересчитываться на текущую дату. Как установить текущую дату смотрите в статье «Текущая дата в Excel ».
2.
Первый способ.
В примере прибавим 9 месяцев.
В ячейке А13 пишем дату, к которой будем прибавлять 9 месяцев.
В ячейке D13 пишем формулу. Можно написать формулу в соседней ячейке, просто, в примере, она стоит там – в ячейке D13. =ДАТА(ГОД(A13);МЕСЯЦ(A13)+9;ДЕНЬ(A13))
В формуле все три адреса ячейки одинаковые, т.к. дата записана в одной ячейке. Здесь все верно. Получилось. Т аким же образом можно прибавить года, изменив чуть формулу. Смотрите пример.
= ДАТА(ГОД(A13)+2;МЕСЯЦ(A13);ДЕНЬ(A13)) Или прибавить сразу всё – года, месяцы, дни. По такой формуле можно рассчитать срок службы, срок окончания договора, проекта, отпуска, срок оплаты счета, многое другое.
Второй способ.
Пишем формулу с помощью функции «ДАТА».
В ячейке А13 пишем дату, к которой будем прибавлять. В примере прибавим 3 месяца.
В следующей ячейке пишем число месяцев, которое будем прибавлять к дате. В примере – это ячейка G13.
В ячейке Н13 будет стоять результат, здесь пишем формулу. Нажимаем на эту ячейку, делаем её активной. Заходим на закладку «Формулы» в раздел «Библиотека функций» и выбираем функцию «Дата и время».
Или можно открыть мастер функций кнопкой «Вставить функцию» (на закладке «Формулы») и выбрать категорию «Дата и время».
Теперь выбираем из списка функцию «ДАТА». Выходит диалоговое окно. В нем пишем:
«Год» — год нашей даты.
«Месяц» — пишем месяц нашей даты числом, ставим плюс и пишем адрес ячейки, в которой стоит число месяцев, которое нужно прибавить к дате.
«День» — пишем день нашей даты.
Получилось так.
Нажимаем «ОК». Получилась такая формула. =ДАТА(1979;11+G13;1)
Смотрим в таблице результат.
Такой расчет даты удобен тем, что всегда можно изменить цифру месяца, не меняя формулы. Например, фирма не успевает сделать заказ вовремя, сроки окончания работы продлеваются. Меняем цифру в ячейке G 13 и формула расчитывает новую дату. По такому принципу можно прибавлять, отнимать по отдельности и вместе года, месяцы и дни.
Например, прибавим к дате 1 год, 2 месяца и 11 дней. Пишем года, месяцы и дни, которые будем прибавлять, в разных ячейках.
Диалоговое окно заполним так.
Получилась такая формула и такой результат.
Если изменим цифру дней с 11 на 25, то получится такая дата.
Как ввести адрес ячейки быстро смотрите в статье «Сложение, вычитание, умножение, деление в Excel ».
В Excel есть три функции, которые выбирают из даты или только число, или только месяц, или год. Это функции «ГОД», «МЕСЯЦ», «ДЕНЬ».
Делаем активной ячейку В19. На закладке «Формулы» в разделе «Библиотека функций» нажимаем на функцию «Дата и время». В появившемся окне выбираем нужную функцию. Например, мы выбрали функцию «ГОД». В появившемся диалоговом окне указали адрес ячейки с датой (А19). Нажали «ОК». Получилось так. Смотрите ячейку В19. Вместо полной даты стоит только год.
Другими соответствующими функциями выбрали месяц (ячейка В20) и день (ячейка В21). Формат в этих ячейках можно установить другой.
Конечно, можно изменить формат даты в ячейке или написать свой формат. Как создать свой формат, смотрите в статье «Как создать пользовательский формат в Excel» .
В Excel есть функции, которыми можно сложить данные из таблицы за конкретный день, т.д. Этот способ пригодится при проведении анализа в таблице, т.д. Подробнее о таком способе, читайте в статье "Функция "СУММЕСЛИ" в Excel ".
В таблице Excel можно сравнить даты из разных столбцов и выделить разные даты. Как это сделать, смотрите в статье " Как сравнить два столбца в Excel на совпадения " .
Если, для работы нужно изменить формат даты на текстовый, то смотрите "Преобразовать дату в текст Excel".
В Excel можно считать не только даты, но и время. Как высчитывать время, читайте в статье "Как посчитать время в Excel" .
Как посчитать возраст, смотрите в статье "
Количество дней между двумя датами
Это полезная формула, если вам нужно экспортировать данные электронной таблицы с датами в другую программу, такую как приложение базы данных или метки времени. Формула для этого расчета проста: просто вычтите дату окончания с даты начала.
Теперь Магнум хочет знать, сколько он будет зарабатывать, если он работает в те же даты, но уходит в выходные.
Преобразование текстовых дат в фактические даты
Импорт дат — лучший вариант, но иногда даже импортированные даты появляются в виде текста. Вставьте апостроф перед каждой датой и преобразуйте дату в текстовую запись. Появится экран «Аргументы функций».
Теперь все, что вам нужно сделать, это форматировать ячейки в зависимости от формата даты, который вы предпочитаете. Выберите Дата из категории.
Он имеет следующие аргументы. Год — представляет год даты. Наконечник. Чтобы избежать путаницы, всегда поставляйте четырехзначные годы. Месяц — целое число, представляющее месяц года, от 1 до 12. День — целое число, соответствующее дню месяца, от 1 до.
Предположим, у вас есть список дат на вашем листе, и теперь вы хотите добавить или вычесть несколько дней или месяцев для корректировки даты в соответствии с вашими потребностями. Чтобы добавить или вычесть комбинацию дней, месяцев и лет на дату, вы можете использовать следующую формулу.
В открывшемся диалоговом окне «Помощник формулы» укажите адрес ячейки даты в поле «Время по дате» и введите количество лет, которое вы добавите в поле «Число». Если вам нужно добавить несколько лет, например, 3 года, введите 3 в поле «Число». Выделите диапазон, затем в группе номеров «Домашняя страница» выберите «Краткая дата» в раскрывающемся списке. Выделите диапазон; затем из группы номеров «Домашняя вкладка» выберите «Общие» в раскрывающемся списке.
Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).
Для подсчета чисел или дат, удовлетворяющих одному условию (например, "равно", "больше", "меньше", "больше или равно" или "меньше или равно"), используйте функцию СЧЁТЕСЛИ . Для подсчета числа или даты, которые попадают в диапазон (например, больше 9000 и не менее 22500), можно использовать функцию СЧЁТЕСЛИМН . Кроме того, вы можете использовать СУММПРОИЗВ.
Во многих фирмах отдельное внимание уделяется датам, выпадающим после определенного пройденного периода. С помощью условного форматирования можно легко составить отчет «После периода» на котором выделены пройденные даты.
Как сделать подсвечивание цветом ячеек с датами пройденного срока в Excel
Пример представлен ниже на рисунке в виде отчета, в котором даты за более чем 90 дней от текущей даты выделенные другим цветом заливки.
Чтобы составить аналогичный отчет с таким же автоматическим форматированием ячеек по условию выполните следующее:
- Выделите целевой диапазон ячеек (в данном примере A3:A8) и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило». В результате чего появится окно для внесения всех необходимых настроек инструмента:
- В появившемся окне из верхней части где находится список опций выберите пункт: «Использовать формулу для определения форматируемых ячеек». Данная опция позволяет нам использовать собственные формулы для составления сложны правил условного форматирования. Формула должна содержать логическое выражение и соответственно возвращать логическое значение для каждой ячейки из выделенного диапазона. Если будет возвращено – ИНСТИНА, тогда к этой ячейке будет применятся правило и присваивается новый формат, который предварительно настроен этим же инструментом.
- В полю ввода формул введите логическое выражение представленное на этом шаге. Данная формула проверяет значение ячеек: будет ли их дата выпадать после 90 дней, пройденных от сегодняшнего дня. Отсчитывается от даты, указанной в целевой ячейке A3 выделенного просматриваемого диапазона. Если да (ИСТИНА) – сразу же применяется условное форматирование.
А в результате выделились все даты актуальность которых превышает 90 дней.
Из этого обзора вы узнаете несколько быстрых и простых способов подсчета, сколько дней проходит между двумя датами в Excel.
Вам интересно, сколько составляет разница в днях, месяцах и годах между двумя датами? Может быть, вам нужно знать количество дней между сегодняшним днем и какой-либо точкой в прошлом или будущем?
Какова бы ни была ваша проблема, один из приведенных ниже примеров в Excel наверняка подскажет вам решение.
Простой калькулятор дней.
Если вы ищете быстрый ответ, просто укажите две даты в соответствующих ячейках, и наш калькулятор покажет вам, сколько суток между ними:
Хотите узнать формулу, которая рассчитала эти результаты? Это так же просто, как =B3-B2:)
Обратите внимание, что нужно четко знать, что именно вы хотите подсчитать: «дни включительно» или «дни между».
Кроме того, прежде чем выполнять операции с датой-временем, нужно установить правильный формат в ячейках вашей таблицы. Чаще всего Эксель сам подбирает нужный формат представления данных, но все же лучше подстраховаться.
Для этого выберите клетку или диапазон ячеек с числами, которые вы хотите представить как даты, и нажмите комбинацию клавиш Ctrl + 1, чтобы открыть диалоговое окно «Формат ячеек». На вкладке «Числовые форматы» в пункте «Дата», выберите подходящий для вас внешний вид в разделе «Тип» и нажмите кнопку «ОК».
Далее вы найдете подробное объяснение того, как работает эта формула, и узнаете несколько других способов вычисления.
Как рассчитать разницу в днях?
Самый простой способ рассчитать дни между датами в Эксель - вычесть одну из другой:
= Более новая - Более старая
Как мы уже сказали ранее, чтобы узнать, сколько времени пройдет в периоде между ячейками В3 и B2, используйте следующую формулу:
где В2 - более ранняя, а B3 - более.
Результатом подсчета является целое число, которое представляет количество дней внутри временного интервала.
Однако, будьте здесь внимательны: при подобном подсчете отработанных дней в том случае, если человек работал и в первый, и в последний день, то 1 день будет «потерян». Добавьте в вашу формулу +1 день.
Как работает эта формула?
Как вы, вероятно, знаете, Microsoft Excel хранит даты в виде порядковых номеров, начинающихся с 1 января 1900 года, которому соответствует номер 1. В этой системе 2 января 1900 года сохраняется как число 2, 3 января 1900 года как 3 и так далее. Таким образом, вычитая одну дату из другой, вы фактически вычитаете целые числа, представляющие их.
В нашем примере формула в C5 на самом деле вычитает 43892 (числовое значение 2 марта 2020г.) из 43900 (числовое значение 10 марта 2020г.) и возвращает результат 8 дней.
Положительный момент этого метода в том, что он отлично работает во всех случаях, независимо от того, какая дата старше, а какая новее. Если вы вычитаете более позднюю дату из более ранней, то формула просто возвращает разницу в виде отрицательного числа.
Рассчитать разницу между датами в Excel с помощью РАЗНДАТ (DATEDIF).
Другой способ — как в Экселе посчитать дни в заданном временном интервале — использование функции РАЗНДАТ (в английском варианте – DATEDIF), которая специально разработана для расчета разности во времени и представления её в различных единицах: дни, месяцы и годы.
А вот ее синтаксис:
дата_нач и дата_кон - две временных отметки, разницу между которыми необходимо рассчитать,
аргумент – указывает, как представить результат; может иметь одно из следующих значений:
- Y Число полных лет.
- M Число полных месяцев.
- D Число дней.
- MD Разница в днях (значения месяца и года не учитываются).
- YM Разница в месяцах (значения дня и года не учитываются).
- YD Разница в днях, игнорируя значения года.
Чтобы получить количество дней между двумя датами, укажите момент начала в первом аргументе, срок окончания во втором, и «d» в последнем третьем:
РАЗНДАТ(дата_нач; дата_кон; "d")
DATEDIF (start_date, end_date, "d")
Примечание. РАЗНДАТ (DATEDIF) - недокументированная функция, то есть ее нет в мастере функций в Excel. Вы не сможете ее вставить при помощи кнопки fx или меню Вставка — Функция. Чтобы создать формулу РАЗНДАТ в таблице Экселя, вам нужно вписать ее название руками и ввести все аргументы вручную, подсказок ввода не будет.
В ней вы можете указывать исходные данные различными способами, такими как:
- Ссылки на ячейки. Например, следующая формула вернет количество дней между A3 и B3:
Текстовые выражения. Excel понимает даты в различных форматах, например, «2 март 2020», «20.03.2020», «20/03/2020», «2020/03/20», «20-03-2020», «2020-03-20» и т.д.
Вот примеры расчета количества дней во временных промежутках:
Естественно, эти значения могут быть записаны и в ячейках, а формула на них будет ссылаться.
- Порядковые номера. Поскольку Microsoft Excel хранит каждую дату как порядковый номер, начинающийся с 1 января 1900 года, вы можете поместить числа, соответствующие датам, непосредственно в формулу. Хотя этот метод полностью поддерживается, все же он не является надежным, поскольку нумерация различается в разных компьютерных системах (Windows и MacOS).
В системе дат 1900 года следующая формула является еще одним способом вычисления месяцев по условиям из предыдущего примера (2 марта 2020 года и 10 марта 2020 года):
- Результаты выполнения других функций. Например, следующая формула подсчитывает, срок между сегодня (18 марта 2020г.) и 9 мая 2020 года.
1. Разница в днях.
В нашем примере формула расчета в D3 выглядит следующим образом:
Обратите также внимание, что при простом вычитании дат, которые также содержат время, мы получаем в результате дробное число. Так произошло в С5 и С6. Что делать с такими дробями – поговорим чуть позже. А вот время здесь просто игнорируется. В этом вы можете убедиться в диапазоне D5:I6.
Аналогичный результат можно получить формулами:
Время, то есть дробная часть, будет отброшено.
Кстати, если нужно подсчитать только рабочие дни, то следует использовать выражение
Более подробно о расчете рабочих дней читайте здесь.
2. Разница в полных месяцах.
Обращаемся к ячейке Е3:
Обратите внимание, выражение вернет количество полных месяцев. В нашем примере 13 месяцев и 7 дней «округлены» ровно до 13. И последний день интервала, как мы уже говорили, эта функция не учитывает. В частности,
возвращает результат 0, так как последний день не включается в расчет. Вы это видите в ячейке E7. Это также будет важно при расчете стажа и отработанного времени. Учитывайте эту особенность и просто добавляйте 1 дополнительный день!
Формула может быть заменена альтернативным выражением:
3. Разница в полных годах.
Формула в ячейке F3 вернет количество полных лет:
Опять же, считается только полный год, остальное отбрасывается.
Ну и альтернативный способ расчета —
4. Разница в полных месяцах без учета лет.
Используем в I3 аргумент "YM".
Второй параметр и конечная точка у нас – 10.04.2020 г. Поскольку годы мы здесь игнорируем, то год первого параметра также становится 2020. И мы теперь сравниваем 10.04.2020 и 2.03.2020г.
Результат – 1 полный месяц.
А вот если начало, предположим, будет 01.09.2015г.? Ведь ее нельзя модифицировать до 01.09.2020, поскольку тогда функция выдаст ошибку. И программа ее меняет на 01.09.2019. Результат здесь – 7 полных месяцев.
Я очень сомневаюсь, что кто-то будет проводить подобный расчет. Но он нам будет нужен, чтобы подсчитать количество дней, месяцев и лет между двумя точками. Об этом мы поговорим чуть ниже.
Ну и альтернативный расчет –
5. Разница в днях без учета месяцев и лет.
Здесь немного сложнее. Итак, в G3 пишем:
Разберем, как получен результат в G3. Поскольку для нас не важны месяц и год, мы в начальной дате меняем их на текущие. То есть вместо 02.03.2019 рассматриваем 02.04.2020. Сравниваем с 10.04.2020 и получаем 8 дней. Есть ли в таком результате смысл? Вряд ли. Поэтому и не рекомендуется применение этого аргумента как отдельного расчета.
Быть может, поэтому РАЗНДАТ и нет в списке официальных функций Excel.
6. Разница в днях без учета лет.
То же самое, что и в предыдущем аргументе. Считаем количество суток между двумя точками без учета лет.
Для начального момента — 02.03.2019 и конечного – 10.04.2020, разница в 39 дней выглядит явной ошибкой. Аналогично – между 01.09.2015 и 10.04.2020 совсем не 222 дня.
Следовательно, использование этого аргумента также не рекомендуем.
А зачем же всё это нужно? Ответ читайте в следующем разделе.
Как посчитать разницу в днях, месяцах и годах.
Чтобы подсчитать, сколько полных лет, месяцев и дней составляет разница "от" и "до" с использованием одной формулы, вы просто объединяете три функции в одно выражение:
Если вы не хотите отображать нулевые значения лет, месяцев и дней, то выражение можно изменить, добавив условие ЕСЛИ:
А вот еще один более сложный, но зато более универсальный вариант расчета разницы в днях, месяцах и годах с учетом склонений и падежей:
Выглядит угрожающе, но зато работает.
Подсчет с помощью функции ДНИ
У пользователей Excel 2013 и 2016 есть еще один удивительно простой способ посчитать количество дней во временном интервале — функция ДНИ.
Обратите внимание, что по сравнению с РАЗНДАТ, функция ДНИ требует указания дат в обратном порядке.
Итак, наше выражение принимает вид:
Как и обычное вычитание, оно возвращает разницу в виде положительного или отрицательного числа, в зависимости от того, больше или меньше срок окончания, чем начало:
Думаю, в большинстве случаев можно использовать простое вычитание. Возможно, при этом только нужно будет изменить формат ячейки, убрав дробную часть.
Как рассчитать количество дней между сегодня и другой датой.
Для этого расчета вы можете использовать любую из формул, описанных выше, и применить функцию СЕГОДНЯ (TODAY в английском варианте) в качестве одного из аргументов.
Чтобы рассчитать количество прошедших дней с определенного момента, то есть между прошлым и сегодняшним днем:
=СЕГОДНЯ() – Дата_в_прошлом
Для подсчета количества дней, оставшихся до наступления какого-то события, то есть между будущим временем и сегодняшним днем:
=Дата_в_будущем - СЕГОДНЯ()
В качестве примера, давайте посчитаем разницу между текущим моментом времени и более ранним - в A4 (сегодня 18 марта 2020г.):
А теперь давайте выясним, сколько осталось от сегодняшнего дня (18.03.2020г.) до более позднего срока:
Ну а если не менять выражение по сравнению с предыдущим расчетом, то просто получите отрицательное количество дней.
Мастер даты и времени - простой способ создавать формулы разницы дат в Excel
Как показано в первой части этого руководства, функция РАЗНДАТ (DATEDIF) — довольно универсальная, подходящая для самых разных целей. Однако есть один существенный недостаток — она не документирована Microsoft, то есть вы не найдете РАЗНДАТ в списке функций и не увидите всплывающих подсказок для аргументов, когда начнете вводить формулу в ячейку. Чтобы иметь возможность использовать функцию РАЗНДАТ в своих таблицах, вы должны запомнить ее синтаксис и ввести все аргументы вручную, что может занять много времени и привести к ошибкам, особенно у начинающих.
Надстройка Ultimate Suite радикально меняет это положение, поскольку там имеется мастер даты и времени, который может мгновенно создать практически любую формулу разницы дат. Если вы не уверены, какую формулу использовать, позвольте мастеру даты и времени вычислить количество дней между двумя датами за вас Вот каким образом это можно сделать:
- Выберите ячейку, в которую вы хотите вставить формулу.
- Перейдите на вкладку Ablebits Tools и нажмите кнопку Мастер даты и времени (Date & Time Wizard).
- Появится диалоговое окно мастера даты и времени, где вы переходите на вкладку «Разница (Difference)» и вводите необходимые данные для расчёта:
- Щелкните поле Дата 1 и выберите ячейку, содержащую первую дату.
- Щелкните поле Дата 2 и укажите ячейку со второй датой.
- Выберите желаемую единицу или комбинацию единиц времени из раскрывающегося меню «Разница в (Difference in)» . При этом программа позволяет предварительно просмотреть результат в поле и формулу в ячейке.
Инструмент может рассчитать разницу между двумя датами в 15 форматах:
- Годы
- Месяцы
- Недели
- Дни
- Годы + месяцы
- Годы + недели
- Годы + дни
- Месяцы + недели
- Месяцы + дни
- Недели + дни
- Годы + месяцы + недели
- Годы + месяцы + дни
- Годы + недели + дни
- Месяцы + недели + дни
- Годы + месяцы + недели + дни
- Если вас устраивает то, что вы увидели, нажмите кнопку «Вставить формулу (InsertFormula)», в противном случае попробуйте другие единицы измерения.
После того, как формула вставлена в выбранную ячейку, вы можете скопировать ее вниз по столбцу, как обычно дважды щелкнув или перетащив маркер заполнения.
К сожалению, метки даты и времени вставляются по-английски. Однако, поскольку надстройка формирует для вас не текст, а формулу расчета (что вы и видите на скриншоте), то откорректировать формулу и пользоваться в дальнейшем ее русской версией не слишком сложно.
Да и вряд ли вы самостоятельно напишете такую сложную конструкцию 😊
Для наиболее удобного представления результатов доступно еще несколько дополнительных опций:
- Исключить годы и / или месяцы из расчетов. (Годы и / или месяцы просто будут отброшены).
- Показывать или не показывать текстовые метки времени, такие как дни, месяцы, недели и годы .
- Показывать или не показывать нулевые единицы (0 месяцев, например) .
- Возвращает результаты как отрицательные значения, если Дата 1 (дата начала) больше, чем Дата 2 (дата окончания). Думаю, вы сталкивались с ситуацией, когда попытка вычесть из более ранней даты более позднюю приводила к ошибке. Здесь вы просто получите отрицательную разность.
Более подробная информация доступна здесь.
Преимущества использования мастера формул даты и времени
Помимо скорости и простоты, Мастер даты и времени предоставляет еще несколько преимуществ:
- В отличие от обычной формулы РАЗНДАТ, для формулы, созданной мастером, не важно, какая из двух дат меньше, а какая больше. Разница всегда рассчитывается идеально, даже если Дата 1 (дата начала) больше, чем Дата 2 (дата окончания).
- Мастер поддерживает все возможные единицы измерения (дни, недели, месяцы и годы) и позволяет вам выбирать подходящий вариант из 11 различных комбинаций этих единиц.
- Формулы, которые мастер создает для вас, являются обычными формулами Excel, поэтому вы можете редактировать, копировать или перемещать их как обычно. Вы также можете поделиться своими таблицами с другими людьми, и все формулы останутся на своих местах, даже если у кого-то нет Ultimate Suite в их Excel.
Если вам интересно протестировать этот калькулятор дат, а также открыть для себя еще 60 надстроек для упрощения работы и экономии времени в Excel, то можете загрузить пробную версию Ultimate Suite. Если вам понравились инструменты и вы решили купить лицензию, не пропустите это специальное предложение для читателей нашего сайта.
Думаю, теперь операции с датами стали для вас намного проще и понятнее. Если вы не нашли ответ на интересующий вас вопрос, ознакомьтесь с дополнительными материалами, ссылки на которые вы видите чуть ниже. Или напишите нам в комментариях – постараемся ответить.
Также рекомендуем статьи о работе с датами:
Как вывести месяц из даты - На примерах мы покажем, как получить месяц из даты в таблицах Excel, преобразовать число в его название и наоборот, а также многое другое. Думаю, вы уже знаете, что дата в…
Функция Excel СЕГОДНЯ — синтаксис и основные применения - Функция СЕГОДНЯ в Excel делает именно то, на что указывает ее имя - возвращает сегодняшнюю дату. Синтаксис ее настолько прост, насколько это вообще возможно - у нее вообще нет аргументов. Всякий…
Как быстро вставить сегодняшнюю дату в Excel? - Это руководство показывает различные способы ввода дат в Excel. Узнайте, как вставить сегодняшнюю дату и время в виде статической метки времени или динамических значений, как автоматически заполнять столбец или строку…
Количество рабочих дней между двумя датами в Excel - Довольно распространенная задача: определить количество рабочих дней в период между двумя датами – это частный случай расчета числа дней, который мы уже рассматривали ранее. Тем не менее, в Excel для…
8 способов — как посчитать возраст в Excel по дате рождения. - В Excel нет специальной функции, чтобы посчитать возраст, однако существует несколько различных способов его определения на основе даты рождения. Вы узнаете несколько приемов, как рассчитать его в количестве полных лет,…
Как преобразовать текст в дату в Excel или извлечь ее из сложной фразы? - Сейчас вы научитесь использовать функции Excel для преобразования текста в дату. Вы также узнаете, как это сделать без использования формул. Поскольку Excel - не единственное приложение, которое вы используете, иногда…
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
В этой статье описаны синтаксис формулы и использование функции СЕГОДНЯ в Microsoft Excel.
Описание
Возвращает текущую дату в числовом формате. Числовой формат даты — это код даты и времени, с помощью которого в Microsoft Excel производятся вычисления над датами и промежутками времени. Если до ввода этой функции для ячейки был задан формат Общий, результат будет отформатирован как Дата. Если должно отображаться число, выберите для ячейки Общий или Числовой формат.
Функция СЕГОДНЯ полезна, если на листе требуется отображать текущую дату независимо от времени открытия книги. Она также используется для вычисления интервалов. Например, если известно, что кто-либо родился в 1963 году, узнать возраст этого человека можно с помощью следующей функции.
= ГОД( СЕГОДНЯ())-1963
В этой формуле функция СЕГОДНЯ используется в качестве аргумента функции ГОД для получения текущего года, из которого вычитается 1963. Полученное значение и есть возраст человека.
Примечание: Если функция СЕГОДНЯ не обновляет дату, как ожидается, может потребоваться изменение параметров, управляющих пересчетом книги или листа. На вкладке Файл выберите команду Параметры, а затем в категории Формулы в разделе Параметры вычислений выберите вариант Автоматически.
Синтаксис
У функции СЕГОДНЯ нет аргументов.
Примечание: В приложении Excel даты хранятся в виде последовательных чисел, что позволяет использовать их в вычислениях. По умолчанию дате 1 января 1900 года соответствует номер 1, а 1 января 2008 года — 39448, так как интервал между этими датами составляет 39 447 дней.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Возвращает текущую дату.
Возвращает текущую дату плюс 5 дней. Например, если текущая дата — 01.01.2012, формула возвращает значение 06.01.2012.
Возвращает количество дней с текущей даты до 1.01.2030. Помните о том, что для правильного отображения результатов ячейка должна иметь формат "Общий" или "Числовой".
Возвращает текущее число месяца (1–31).
Возвращает текущий месяц (1–12). Например, если текущий месяц — май, формула возвращает значение 5.
Excel IF функция
Функция ЕСЛИ - одна из самых простых и полезных функций в книге Excel. Он выполняет простой логический тест, который зависит от результата сравнения, и возвращает одно значение, если результат ИСТИНА, или другое значение, если результат ЛОЖЬ.
Синтаксис:
Синтаксис функции ЕСЛИ в Excel:
Аргументы:
- logical_test : Необходимые. Это условие, которое вы хотите проверить.
- value_if_true : Необязательный. Конкретное значение, которое вы хотите вернуть, если результат логического_теста - ИСТИНА.
- value_if_false : Необязательный. Значение, которое вы хотите вернуть, если результат логического_теста ЛОЖЬ.
Заметки:
1. Если значение_если_истина опущено:
2. Если значение_если_ложь опущено:
Вернуть:
Проверка на конкретное условие, возвращает соответствующее значение, которое вы указали для ИСТИНА или ЛОЖЬ.
Примеры:
Пример 1: Использование простой функции ЕСЛИ для чисел
Например, предположим, что вы хотите протестировать список значений, если значение больше определенного значения 100, отображается текст «Хорошо», если нет, возвращается текст «Плохо».
Введите приведенную ниже формулу, и вы получите результат, который вам нужен.
Пример 2: Использование функции ЕСЛИ для текстовых значений
Случай 1: функция ЕСЛИ для текстовых значений без учета регистра:
Здесь у меня есть таблица со списком задач и статусом завершения, теперь я хочу знать, какие задачи нужно продолжить, а какие нет. Когда текст в столбце C будет заполнен, будет отображаться «Нет», в противном случае будет возвращено «Да».
Примените следующую формулу, теперь ячейка вернет «Нет», если текст в столбце C отображается как «завершено», независимо от того, в верхнем или нижнем регистре; если другой текст в столбце C, будет возвращено «Да». Смотрите скриншот:
Случай 2: функция ЕСЛИ для текстовых значений с учетом регистра:
Чтобы проверить текстовые значения с учетом регистра, вы должны объединить функцию ЕСЛИ с функцией ТОЧНЫЙ, примените формулу ниже, тогда будет распознан только текст с точным соответствием, и вы получите следующий результат, который хотите:
Случай 3: функция ЕСЛИ для текстовых значений с частичным соответствием:
Иногда вам нужно проверить значения ячеек на основе частичного текста, в этом случае вы должны использовать функции ЕСЛИ, ЕЧИСЛО и ПОИСК вместе.
Например, если вы хотите проверить ячейки, содержащие текст «comp», а затем вернуть соответствующие значения, примените формулу ниже. И вы получите результат, как показано на скриншоте ниже:
Ноты:
- 1. Функция ПОИСК применяется к тексту без учета регистра, если вы хотите проверить текст с учетом регистра, вам следует заменить функцию ПОИСК функцией НАЙТИ. Как это: =IF(ISNUMBER(FIND("comp",C2)), "No", "Yes")
- 2. Текстовые значения в качестве параметров в формулах ЕСЛИ, вы должны заключить их в «двойные кавычки».
Пример 3: Использование функции ЕСЛИ для значений даты
Случай 1: функция ЕСЛИ для дат для сравнения дат с определенной датой:
Если вы хотите сравнить даты, чтобы проверить, больше ли они или меньше определенной даты, функция ЕСЛИ также может оказать вам услугу. Поскольку функция ЕСЛИ не может распознать формат даты, вам следует объединить с ней функцию ДАТАЗНАЧ.
Примените эту формулу, когда дата больше 4 апреля 15 г., будет возвращено «Да», в противном случае формула вернет текст «Нет», см. Снимок экрана:
Внимание: В приведенной выше формуле вы можете использовать ссылку на ячейку напрямую, без использования функции DATEVALUE. Как это: =IF(D4>$D$1, "Yes", "No") .
Случай 2: функция ЕСЛИ для дат для проверки дат больше или меньше 30 дней:
Если вы хотите определить даты, которые больше или меньше 30 дней от текущей даты, вы можете объединить функцию СЕГОДНЯ с функцией ЕСЛИ.
Пожалуйста, введите эту формулу:
Определите дату старше 30 дней: =IF(TODAY()-C4>30,"Older date","")
Определите дату, превышающую 30 дней: =IF(C4-TODAY()>30, "Future date", "")
Примечание: Если вы хотите поместить оба результата в один столбец, вам нужно использовать вложенную функцию IF следующим образом:
Пример 4: Использование функции ЕСЛИ с функциями И, ИЛИ вместе
Мы часто используем сочетание функций ЕСЛИ, И, ИЛИ в Excel.
Случай 1. Использование функции ЕСЛИ с функциями И для проверки выполнения всех условий:
Я хочу проверить, выполняются ли все установленные мной условия, например: B4 - красный, C4 - маленький и D4> 200. Если все условия TURE, отметьте результат как «Да»; Если любое из условий - ЛОЖЬ, верните «Нет».
Пожалуйста, примените эту формулу, и вы получите результат, как показано на следующем скриншоте:
Случай 2: Использование функции ЕСЛИ с функциями ИЛИ для проверки выполнения любого из условий:
Вы также можете использовать функции ЕСЛИ и ИЛИ, чтобы проверить, выполняется ли какое-либо из условий, например, я хочу определить, содержит ли ячейка в столбце B текст «Синий» или «Красный», если какой-либо текст в столбце B, Отображается Да, в противном случае возвращается Нет.
Здесь вы должны применить эту формулу, и будет показан результат ниже:
Случай 3: Использование функции ЕСЛИ вместе с функциями И и ИЛИ:
В этом примере я объединю функцию ЕСЛИ с функциями И и ИЛИ одновременно. Предположим, вы должны проверить следующие условия:
- Condition 1: Column B = “Red” and Column D > 300;
- Condition 2: Column B = “Blue” and Column D > 300.
Если выполняется одно из вышеуказанных условий, возвращается Match, в противном случае - No.
Воспользуйтесь этой формулой, и вы получите следующий результат, который вам нужен:
Пример 5: Использование вложенной функции ЕСЛИ
Функция ЕСЛИ используется для проверки условия и возврата одного значения, если условие выполняется, и другого значения, если оно не выполняется. Но иногда вам нужно проверять более одного условия одновременно и возвращать разные значения, вы можете использовать Nested IF для решения этой задачи.
Вложенный оператор IF, который объединяет несколько условий IF, это означает размещение оператора IF внутри другого оператора IF и повторение этого процесса несколько раз.
Синтаксис функции вложенного ЕСЛИ в Excel:
Внимание: В Excel 2007 и более поздних версиях вы можете вложить до 64 функций ЕСЛИ в одну формулу, а в Excel 2003 и более ранних версиях можно использовать только 7 вложенных функций ЕСЛИ.
Случай 1. Вложенная функция ЕСЛИ для проверки нескольких условий:
Классическое использование функции вложенного ЕСЛИ - присвоение каждому учащемуся буквенной оценки на основе их оценок. Например, у вас есть таблица с учащимися и их оценками за экзамены, теперь вы хотите классифицировать оценки по следующим условиям:
Пожалуйста, примените эту формулу, и вы получите следующий результат: если оценка больше или равна 90, оценка будет «отлично», если оценка больше или равна 80, оценка будет «хорошо», если оценка больше или равно 60, оценка - «Средний», в противном случае - «Плохо».
Объяснение приведенной выше формулы:
Случай 2: Вложенная функция ЕСЛИ для расчета цены на основе количества:
Функцию вложенного ЕСЛИ также можно использовать для расчета цены продукта на основе количества.
Например, вы хотите предоставить покупателям ценовой разрыв в зависимости от количества, большего количества, которое они покупают, большей скидки, которую они получат, как показано на скриншоте ниже.
Поскольку общая цена равна количеству, умножьте цену, поэтому вы должны умножить указанное количество на значение, возвращаемое вложенными If. Пожалуйста, используйте эту формулу:
Внимание: Вы также можете использовать ссылки на ячейки для замены статических ценовых чисел, при изменении исходных данных вам не нужно обновлять формулу, используйте эту формулу: =D2*IF(D2>=101, B6, IF(D2>=50, B5, IF(D2>=25, B4, IF( D2>=11, B3, IF(D2>=1, B2, "")))))
Советы: Используя функцию ЕСЛИ для построения теста, вы можете использовать следующие логические операторы:
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и т. Д.) И экономия 80% времени для вас.
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
Чтобы просмотреть более подробные сведения о функции, щелкните ее название в первом столбце.
Примечание: Маркер версии обозначает версию Excel, в которой она впервые появилась. В более ранних версиях эта функция отсутствует. Например, маркер версии 2013 означает, что данная функция доступна в выпуске Excel 2013 и всех последующих версиях.
Возвращает заданную дату в числовом формате.
Вычисляет количество дней, месяцев или лет между двумя датами. Эта функция полезна в формулах расчета возраста.
Преобразует дату из текстового формата в числовой.
Преобразует дату в числовом формате в день месяца.
ДНИ
Возвращает количество дней между двумя датами.
Вычисляет количество дней между двумя датами на основе 360-дневного года.
Возвращает дату в числовом формате, отстоящую на заданное число месяцев вперед или назад от начальной даты.
Возвращает дату в числовом формате для последнего дня месяца, отстоящего вперед или назад на заданное число месяцев.
Преобразует дату в числовом формате в часы.
НОМНЕДЕЛИ.ISO
Возвращает номер недели по ISO для заданной даты.
Преобразует дату в числовом формате в минуты.
Преобразует дату в числовом формате в месяцы.
Возвращает количество полных рабочих дней между двумя датами.
ЧИСТРАБДНИ.МЕЖД
Возвращает количество полных рабочих дней в интервале между двумя датами, руководствуясь параметрами, указывающими выходные дни и их количество.
Возвращает текущую дату и время в числовом формате.
Преобразует дату в числовом формате в секунды.
Возвращает заданное время в числовом формате.
Преобразует время из текстового формата в числовой.
Возвращает текущую дату в числовом формате.
Преобразует дату в числовом формате в день недели.
Преобразует дату в числовом формате в число, которое указывает, на какую неделю года приходится дата.
Возвращает дату в числовом формате, отстоящую вперед или назад на заданное количество рабочих дней.
РАБДЕНЬ.МЕЖД
Возвращает числовое значение даты, предшествующей заданному количеству рабочих дней или следующей за ними, руководствуясь при этом параметрами, указывающими выходные дни и их количество.
Преобразует дату в числовом формате в год.
Возвращает долю года, которую составляет количество дней между начальной и конечной датами.
Важно: Вычисляемые результаты формул и некоторые функции листа Excel могут несколько отличаться на компьютерах под управлением Windows с архитектурой x86 или x86-64 и компьютерах под управлением Windows RT с архитектурой ARM. Подробнее об этих различиях.
Читайте также: