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 Еще. Меньше
С помощью функции ДАТА в Excel можно объединить три отдельных значения в дату.
Функция ДАТА возвращает порядковый номер определенной даты.
Синтаксис: ДАТА(год;месяц;день)
Аргументы функции ДАТА описаны ниже.
Год — обязательный аргумент. Значение аргумента год может содержать от одной до четырех цифр. Excel интерпретирует аргумент год в соответствии с используемой системой дат, используемой на вашем компьютере. По умолчанию в Microsoft Excel для Windows используется система дат 1900, то есть первой датой считается 1 января 1900 г.
Совет: Во избежание непредвиденных результатов используйте в качестве значения аргумента год четыре цифры. Например, значение "07" может интерпретироваться как "1907" или "2007". Четырехзначное обозначение года предотвращает возникновение путаницы.
Если аргумент год находится в диапазоне от 0 (нуль) до 1899 (включительно), Excel для вычисления года прибавляет это значение к числу 1900. Например, функция ДАТА(108;1;2) возвращает 2 января 2008 (1900+108) года.
Если аргумент год находится в диапазоне от 1900 до 9999 (включительно), Excel использует это значение как год. Например, функция ДАТА(2008;1;2) возвращает 2 января 2008 года.
Месяц — обязательный аргумент. Положительное или отрицательное целое число в диапазоне от 1 (январь) до 12 (декабрь), представляющее месяц года.
Если значение аргумента месяц больше 12, аргумент месяц добавляет число месяцев к первому месяцу указанного года. Например, функция ДАТА(2008;14;2) возвращает число, соответствующее 2 февраля 2009 года.
Если значение аргумента месяц меньше 1, аргумент месяц вычитает значение этого числа месяцев, увеличенное на 1, из первого месяца указанного года. Например, функция ДАТА(2008;-3;2) возвращает число, соответствующее 2 сентября 2007 года.
День — обязательный аргумент. Положительное или отрицательное целое число в диапазоне от 1 до 31, представляющее день месяца.
Если значение аргумента день больше числа дней в указанном месяце, аргумент день добавляет это число дней к первому дню месяца. Например, функция ДАТА(2008;1;35) возвращает число, соответствующее 4 февраля 2008 года.
Если значение аргумента день меньше 1, аргумент день вычитает значение этого числа дней, увеличенное на 1, из первого дня указанного месяца. Например, функция ДАТА(2008;1;-15) возвращает число, соответствующее 16 декабря 2007 года.
Примечание: В Excel даты хранятся в виде порядковых номеров, что позволяет использовать их в вычислениях. Дате 1 января 1900 года соответствует номер 1, а 1 января 2008 года — 39448, так как интервал между этими датами составляет 39 447 дней. Чтобы вывести правильную дату, нужно изменить формат ячейки.
Синтаксис: ДАТА(год;месяц;день)
Например, =ДАТА(C2;A2;B2) объединяет значение года из ячейки C2, месяца из ячейки A2 и дня из ячейки B2 и помещает их в одну ячейку в виде даты. В примере ниже показан результат в ячейке D2.
Нужно вставить даты, не используя формулу? Никаких проблем! Вы можете вставить в ячейку текущую дату и время или обновляемую дату. Вы также можете автоматически заполнить ячейки листа данными.
Щелкните правой кнопкой мыши ячейки, которые вы хотите изменить. На компьютере Mac щелкните ячейки, удерживая нажатой клавишу CTRL.
На вкладке Главная щелкните Формат > Формат ячеек или нажмите клавиши CTRL+1 (COMMAND+1 на компьютере Mac).
3. Выберите нужный язык (местоположение) и формат даты.
Дополнительные сведения о форматировании дат см. в статье Пользовательское форматирование даты.
Функцию ДАТА можно использовать для формирования даты на основе даты в другой ячейке. Например, с помощью функций ГОД, МЕСЯЦ и ДЕНЬ можно создать дату годовщины, основанную на другой ячейке. Допустим, что сотрудник начал работать 01.10.2016. С помощью функции ДАТА можно рассчитать пятилетний юбилей его работы.
Функция ДАТА формирует дату.
=ДАТА(ГОД(C2)+5;МЕСЯЦ(C2);ДЕНЬ(C2))
Функция ГОД получает значение "2012" из ячейки C2.
К этому значению прибавляется пять лет ("+5"), после чего в ячейке D2 выводится "2017".
Функция МЕСЯЦ получает значение "3" из ячейки C2 и добавляет его в ячейку D2 в качестве значения месяца.
Функция ДЕНЬ получает значение "14" из ячейки C2 и добавляет его в ячейку D2 в качестве значения дня.
Если открыть файл, полученный из другой программы, Excel попытается распознать даты в данных. Но иногда это не удается сделать. Возможно, это связано с тем, что числа не похожи на обычные даты или отформатированы как текст. В таком случае их можно преобразовать в даты с помощью функции ДАТА. Например, на приведенном ниже рисунке ячейка C2 содержит дату, которая имеет формат ГГГГММДД. Она также отформатирована как текст. Для ее преобразования в дату функция ДАТА используется в сочетании с функциями ЛЕВСИМВ, ПСТР и ПРАВСИМВ.
Функция ДАТА формирует дату.
=ДАТА(ЛЕВСИМВ(C2;4);ПСТР(C2;5;2);ПРАВСИМВ(C2;2))
Функция ЛЕВСИМВ получает первые 4 знака слева в ячейке C2. При этом значение "2014" становится годом преобразованной даты в ячейке D2.
Функция ПСТР ссылается на ячейку C2. Она получает 2 знака справа от 5-го знака. При этом значение "03" становится месяцем преобразованной даты в ячейке D2. "0" не включается в результат из-за формата Дата, заданного для ячейки D2.
Функция ПРАВСИМВ получает первые 2 знака в ячейке C2 от правого края и добавляет "14" в ячейку D2 в качестве значения дня.
Чтобы увеличить или уменьшить дату на определенное количество дней, просто добавьте его к значению либо ссылке на ячейку с датой или вычтите его.
В приведенном ниже примере ячейка A5 содержит дату, которую нужно увеличить или уменьшить на 7 дней (значение в ячейке C5).
Функция ищет дату в строке вне зависимости от того в какой части текста она находится, формат даты при этом может быть практически любым.
Описание работы
Часто необходимо извлечь дату из ячейки, которая содержит длинный текст. Задача может усложнится тем, что дата может быть как в начале, в конце или середине текста. Еще сложнее дело будет обстоять, что дата в тексте имеет разные форматы.
Извлечь в данном случае дату из текста стандартными методами крайне сложно.
С помощью функции на VBA =ДАТАИЗСТРОКИ это сделать очень просто. Функция найдет дату в тексте вне зависимости от формата ее записи и расположения ее в строке. Также распознаются даты на русском и английском языке.
Пример 1
Извлечение даты из текста, когда она расположена в начале строки. Это простой случай, здесь можно воспользоваться и сочетанием стандартных текстовых функций.
Пример 2
Аналогичный пример, но дата расположена в конце строки.
Пример 3
Извлечение даты из любой части текста и особенности функции. Обойтись стандартными функциями тут практически невозможно. Алгоритм функции из надстройки справится с эти очень просто.
Синтаксис функции
Функция =ДАТАИЗСТРОКИ(ТЕКСТ;[En]) имеет два аргумента:
- ТЕКСТ - Текст, из которого необходимо извлечь дату
- [En] - Необязательный параметр. Если дата содержит наименование месяца на английском языке, укажите данный параметр равный 1. По умолчанию равно 0.
Пример 4
Извлечение даты на английском языке из текста
Надстройка для Excel содержит большой набор полезных функций, с помощью которых вы значительно сократите время и увеличите скорость работы с программой.
Как определить, находится ли дата между двумя датами или выходными в Excel?
Иногда вам может потребоваться определить или проверить, находится ли дата между двумя датами или выходными в Excel. Эта статья покажет вам следующие хитрые способы проверить, находится ли дата между двумя датами или выходными в Excel.
Quickly determine if a date falls between two given dates:
The Select Specific Cells utility of Kutools for Excel can help you quickly determine if dates in a specific range are falling between two given dates. Find them and select them easily. Download the full feature 60-day free trail of Kutools for Excel now!
Определите, находится ли дата между двумя датами в Excel
Предположим, вам нужно определить, попадают ли даты в столбце A между 7 и 1. пожалуйста, сделайте следующее:
1. В пустой ячейке с надписью «Ячейка B2» скопируйте и вставьте в нее приведенную ниже формулу и нажмите Enter ключ.
Внимание: Эта формула проверяет, находится ли дата между 7 и 1. Если дата попадает в этот период, она вернет дату; если дата не попадает в этот период, он вернет текст НЕПРАВДА.
2. Продолжая выбирать ячейку результата, перетащите маркер заполнения вниз, чтобы получить все результаты. Смотрите скриншот:
Теперь вы можете определить, попадает ли дата в указанный диапазон дат или нет.
Определите, находится ли дата между двумя датами в Excel с помощью Kutools for Excel
Здесь вам порекомендуют потрясающий инструмент - Выбрать определенные ячейки полезности Kutools for Excel. Этот инструмент поможет вам не только узнать все даты, которые попадают между двумя заданными датами, но и сразу выбрать все соответствующие ячейки дат.
Перед его применением необходимо сначала скачайте и установите.
1. Выберите диапазон дат, по которым вы хотите определить, попадают ли они между двумя датами, а затем щелкните Кутулс > Выберите > Выбрать определенные ячейки. Смотрите скриншот:
2. в Выбрать определенные ячейки в диалоговом окне выберите Ячейка вариант в Тип выбора раздел, а затем укажите Больше и Менее даты и, наконец, нажмите OK кнопку.
Вы можете видеть, что ячейки даты, которые находятся между двумя датами, выбираются немедленно. Смотрите скриншот:
Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Определите, приходится ли дата на выходные с формулами и кодом VBA
Вы можете определить, приходится ли дата в столбце A на выходные, выполнив следующие действия:
Метод А. Использование формулы для проверки того, приходится ли свидание на выходные.
1. В пустой ячейке скопируйте и вставьте в нее приведенную ниже формулу и нажмите Enter ключ.
=IF(OR(WEEKDAY(A2)=1,WEEKDAY(A2)=7),A2,FALSE)
Эта формула определяет, приходится ли свидание на выходные или нет. Если дата выпадает на выходные, она вернет дату; если дата не выпадает на выходные, она вернет текст НЕПРАВДА.
2. Продолжайте выбирать ячейку результата, затем перетащите маркер заполнения вниз, чтобы получить все результаты.
Метод Б. Использование пользовательской функции для проверки того, приходится ли свидание на выходные.
1. нажмите ALT + F11 вместе, чтобы открыть окно Microsoft Visual Basic для приложений.
2. В окне Microsoft Visual Basic для приложений щелкните Вставить >> Модулии вставьте следующий макрос в окно модуля.
3. Нажмите одновременно клавиши Alt + Q, чтобы закрыть окно Microsoft Visual Basic для приложений.
4. В пустой ячейке введите формулу в строку формул и нажмите кнопку Enter ключ.
=IsWeekend(A2)
Если он возвращает текст Правда, дата в ячейке A2 - выходной; и если он возвращает текст Ложь, дата в ячейке A2 не приходится на выходные.
Определите, приходится ли свидание на выходные, с помощью замечательного инструмента
На самом деле вы можете преобразовать все даты в название дня недели, а затем проверить выходные дни на основе субботы или воскресенья. Здесь Применить формат даты полезности Kutools for Excel может помочь вам легко решить проблему.
Перед его применением необходимо сначала скачайте и установите.
1. Выберите диапазон дат и нажмите Кутулс > Формат > Применить форматирование даты. Смотрите скриншот:
2. в Применить форматирование даты диалоговое окно, выберите среда в Форматирование даты поле, а затем щелкните OK кнопку.
Теперь выбранные даты сразу конвертируются в дни недели. Вы можете определить, приходится ли свидание на выходные или нет, напрямую по его содержанию. Смотрите скриншот:
Заметки:
- Преобразованные результаты работали непосредственно с исходными данными;
- Эта утилита поддерживает расстегиватьCtrl + Z».
Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Статьи по теме:
Преобразование даты в день недели, месяц, название года или число в Excel
Говорит, что вы вводите дату в одну ячейку, и отображается как 12/13/2015. Есть ли способ показать только месяц или день недели или текст названия месяца или дня недели, например, декабрь или воскресенье? Методы, описанные в этой статье, могут помочь вам легко преобразовать или отформатировать любые даты, чтобы в Excel отображалось только название дня недели или месяца.
Быстро конвертируйте дату рождения в возраст в Excel
Например, вы получаете ряд различных данных о дате рождения в Excel, и вам нужно преобразовать эти даты рождения, чтобы отобразить их точное значение возраста в Excel, как бы вы хотели выяснить? В этой статье перечислены несколько советов, как легко преобразовать дату рождения в возраст в Excel.
Сравните даты, если они больше другой даты в Excel
Предположим, у вас есть список дат и вы хотите сравнить эти даты с указанной датой, чтобы узнать дату, которая больше указанной даты в списке, что бы вы сделали? В этой статье мы покажем вам методы сравнения дат, если они больше другой даты в Excel.
Сумма значений между двумя диапазонами дат в Excel
Когда на вашем листе есть два списка, один - это список дат, а другой - список значений. И вы хотите суммировать значения только между двумя диапазонами дат, например, суммировать значения между 3/4/2014 и 5/10/2014, как вы можете быстро их вычислить? Методы, описанные в этой статье, окажут вам услугу.
Добавить дни к дате, включая выходные и праздничные дни или исключая их, в Excel
В этой статье говорится о добавлении дней к заданной дате, исключая выходные и праздничные дни, что означает добавление рабочих дней (с понедельника по пятницу) только в Excel.
Определите, находится ли дата между двумя датами или выходными с помощью Kutools for Excel
Kutools for Excel включает более 300 удобных инструментов Excel. Бесплатная пробная версия без ограничений в течение 60 дней. Загрузите бесплатную пробную версию прямо сейчас!
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. Подробнее об этих различиях.
Гораздо более мощный и красивый вариант применения условного форматирования - это возможность проверять не значение выделенных ячеек, а заданную формулу.
В этом случае в Окне Условное форматирование при указании условий необходимо вместо значения выбрать - «формула», а в соседней графе ввести формулу.
Если заданная формула верна, то срабатывает нужный формат.В этом случае можно задавать более сложные проверки с использованием функций и, кроме того, проверять одни ячейки, а форматировать - другие.
Рассмотрим 2 примера.
1. Выделение цветом всей строки
Необходимо выделить цветом те строки таблицы, в которых в столбце «В» значения больше 5000.
1.Выделите таблицу, к которой будет применяться Условное форматирование
2. Выберите в меню Формат - Условное форматирование
3. В открывшемся Окне задайте условие:
- Выберите - «формула»
- введите формулу =$B2>5000
4. Выберите необходимый формат для выделения строки
Теперь зеленым цветом будут выделены все строки, в которых в столбце «В» стоит значение большее 5000.
Главный нюанс заключается в знаке доллара ($) перед буквой столбца в адресе - он фиксирует столбец, оставляянезафиксированной ссылку на строку- проверяемые значения берутся из столбца указанного в формуле, по очереди из каждой последующей строки.
2. Проверка дат и сроков
Поскольку даты в Excel представляют собой те же числа (один день = 1), то можно легко использовать условное форматирование для проверки сроков оплат или выполнения задач.
Например, необходимо выделить даты просроченных оплат красным цветом, а тех, что предстоят в ближайшую неделю – желтым.
1. Выделите таблицу, к которой будет применяться Условное форматирование
2. Выберите в меню Формат - Условное форматирование
3. Задайте условие 1:
- выберите - «формула»
- введите формулу =B2
4. Нажмите «А также >>» для ввода второго условия
5. Задайте условие 2:
- выберите - «формула»
- введите формулу =(B2-$D$1) < 7 , (разница между датами меньше 7 дней)
- выберите необходимый формат
Внимание! В формуле адрес ячейки с текущей датой нужно зафиксировать, т.е. поставить знаки $ до и после буквы обозначающей столбец (в данном примереэто $D$).
Читайте также: