Функция если в excel с датами

Функция если в excel с датами

Функция DATE (ДАТА) возвращает числовое значение в формате даты, в виде конкретного дня, месяца и года.

Когда использовать функцию

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

Например, при учете данных по продажам и транзакциям, при учете сотрудников при приеме на работу и.т.д.

Часто значение даты является результатом какой-либо формулы или вычисления.

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

Значение формулы хранится в Excel в числовом формате. Например значение “42370” при использовании этой формулы вернёт значение “Январь 2016”. Таким образом, формула преобразует числовое значение в привычный формат даты.

Синтаксис

=DATE(year, month, day) – английская версия

=ДАТА(год; месяц; день) – русская версия

Аргументы

  • Year (Год) – значение года, которое важно отобразить в дате;
  • Month (Месяц) – значение месяца, которое важно отобразить в дате;
  • Day (День) – значение дня, которое важно отобразить в дате.

Дополнительная информация

  • то, как отображаются значения формулы в ячейке зависит от того, в каком формате ячейка. Например:
  • Если ячейка имеет формат как General, то значение даты будет отображаться в привычном формате даты (день-месяц-год). Из текста выше вы помните что значение даты, обрабатываемое в Excel, хранится в числовом формате.
  • Чтобы получить значение даты в числовом формате, надо сменить формат ячейки с датой на Number.
  • Рекомендую использовать 4-х значное число при указании года, для того чтобы избежать проблем с отображением даты. Если значение года меньше 1900, система автоматически добавит к значению 1900. Например, если при указании года, вы введёте значение “10”, то Excel добавит к этому числу 1900 и отобразит год как “1910”.
  • Значение месяца может быть меньше 0 и не больше 12. Если вы укажете значение месяца более чем 12, то система автоматически округлит значение месяца кратное 12 в год и прибавит его к значению года. Например, если функция =DATE(2016,16,3) или =ДАТА(2016;16;3) , то она отобразит дату как “3 Апреля 2017 года”. Если функция равна =DATE(2016,-2,1) или =ДАТА(2016;-2;1) , то отразит дату как “1 Октября 2015 года”.
  • Значение дня может быть как отрицательным, так и положительным. Система, по такому же принципу как с месяцами вычтет или прибавит к значению месяца количество дней в формуле, отличающихся от календарных.
  • Примеры использования функции DATE (ДАТА) в Excel

    Пример 1: Получение числового значения даты, с использованием данных Года, Месяца, Дня

    Вы можете получить числовое значение даты с указанием Года, Месяца, Дня.

    В приведенном выше примере, функция DATE (ДАТА) использует три аргумента: значение “2016” в качестве значения года, значение “4” в качестве месяца и “1” в качестве значения дня.

    Читайте также:  Загрузка видео на ютюб

    Функция возвращает “42461”, когда ячейка отформатирована как “General” (Общие), и возвращает “4/1/16”, когда ячейка отформатирована как ‘Date’.

    Обратите внимание, что Excel хранит дату или время как числовое значение.

    Формат даты может меняться в зависимости от региональных настроек. Например, формат даты в России ДД-ММ-ГГГГ, в то время как в Великобритании это ММ-ДД-ГГГГ.

    Если в вашей таблице есть данные Года, Месяца и Дня в разных ячейках, то вы можете использовать их с помощью формулы, как это показано на примере ниже:


    Пример 2: Использование отрицательных значений в функции DATE (ДАТА)

    Вы можете использовать отрицательные значения при указании Месяца и Дня в ячейке. При использовании отрицательных значений при указании месяца, скажем, “-1”, Excel отнимет 1 месяц от Года.

    Например, если вы используете формулу =DATE(2017,-1,1) или =ДАТА(2017;-1;1) , она вернет дату 1 ноября 2016 года. Вместе с тем, вы можете использовать любое отрицательное число в значении Месяца.

    Та же логика работает, когда вы используете значение месяца более чем “12”. В этом случае, Excel прибавляет к значению Года столько месяцев, на сколько исходное значение Месяца больше 12.

    Например, если вы используете формулу =DATE(2016,14,1) или =ДАТА(2016;14;1) , она вернет дату 1 февраля 2017 года.

    Примечание: Формат даты указанный в приведенных выше примерах, основан на настройках моей системы. Она может варьироваться в зависимости от настроек вашей системы.

    Кроме того, вы можете также использовать отрицательные значения Дня, или те что больше, чем “31” – функция DATE (ДАТА) будет корректировать дату по логике описанной выше.

    Для работы с датами в Excel в разделе с функциями определена категория «Дата и время». Рассмотрим наиболее распространенные функции в этой категории.

    Как Excel обрабатывает время

    Программа Excel «воспринимает» дату и время как обычное число. Электронная таблица преобразует подобные данные, приравнивая сутки к единице. В результате значение времени представляет собой долю от единицы. К примеру, 12.00 – это 0,5.

    Значение даты электронная таблица преобразует в число, равное количеству дней от 1 января 1900 года (так решили разработчики) до заданной даты. Например, при преобразовании даты 13.04.1987 получается число 31880. То есть от 1.01.1900 прошло 31 880 дней.

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

    Пример функции ДАТА

    Построение значение даты, составляя его из отдельных элементов-чисел.

    Читайте также:  Роутер d link des 1005d

    Синтаксис: год; месяц, день.

    Все аргументы обязательные. Их можно задать числами или ссылками на ячейки с соответствующими числовыми данными: для года – от 1900 до 9999; для месяца – от 1 до 12; для дня – от 1 до 31.

    Если для аргумента «День» задать большее число (чем количество дней в указанном месяце), то лишние дни перейдут на следующий месяц. Например, указав для декабря 32 дня, получим в результате 1 января.

    Пример использования функции:

    Зададим большее количество дней для июня:

    Примеры использования в качестве аргументов ссылок на ячейки:

    Функция РАЗНДАТ в Excel

    Возвращает разницу между двумя датами.

    • начальная дата;
    • конечная дата;
    • код, обозначающий единицы подсчета (дни, месяцы, годы и др.).

    Способы измерения интервалов между заданными датами:

    • для отображения результата в днях – «d»;
    • в месяцах – «m»;
    • в годах – «y»;
    • в месяцах без учета лет – «ym»;
    • в днях без учета месяцев и лет – «md»;
    • в днях без учета лет – «yd».

    В некоторых версиях Excel при использовании последних двух аргументов («md», «yd») функция может выдать ошибочное значение. Лучше применять альтернативные формулы.

    Примеры действия функции РАЗНДАТ:

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

    Функция ГОД в Excel

    Возвращает год как целое число (от 1900 до 9999), который соответствует заданной дате. В структуре функции только один аргумент – дата в числовом формате. Аргумент должен быть введен посредством функции ДАТА или представлять результат вычисления других формул.

    Пример использования функции ГОД:

    Функция МЕСЯЦ в Excel: пример

    Возвращает месяц как целое число (от 1 до 12) для заданной в числовом формате даты. Аргумент – дата месяца, который необходимо отобразить, в числовом формате. Даты в текстовом формате функция обрабатывает неправильно.

    Примеры использования функции МЕСЯЦ:

    Примеры функций ДЕНЬ, ДЕНЬНЕД и НОМНЕДЕЛИ в Excel

    Возвращает день как целое число (от 1 до 31) для заданной в числовом формате даты. Аргумент – дата дня, который нужно найти, в числовом формате.

    Чтобы вернуть порядковый номер дня недели для указанной даты, можно применить функцию ДЕНЬНЕД:

    По умолчанию функция считает воскресенье первым днем недели.

    Для отображения порядкового номера недели для указанной даты применяется функция НОМНЕДЕЛИ:

    Дата 24.05.2015 приходится на 22 неделю в году. Неделя начинается с воскресенья (по умолчанию).

    В качестве второго аргумента указана цифра 2. Поэтому формула считает, что неделя начинается с понедельника (второй день недели).

    Для указания текущей даты используется функция СЕГОДНЯ (не имеет аргументов). Чтобы отобразить текущее время и дату, применяется функция ТДАТА ().

    Читайте также:  Как отключить услугу геолокация

    На первый взгляд может показаться, что функцию ЕСЛИ для работы с датами можно применять так же, как для числовых и текстовых значений, которые мы только что обсудили. К сожалению, это не так.

    Примеры работы функции ЕСЛИ с датами.

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

    Пример 1. Формула условия для дат с функцией ДАТАЗНАЧ (DATEVALUE)

    Иногда случается, что записать дату непосредственно в функцию ЕСЛИ, не ссылаясь ни на какую ячейку. В этом случае возникают некоторые сложности.

    В отличие от многих других функций Excel, ЕСЛИ не может распознавать даты и интерпретирует их как текст, как простые текстовые строки.

    Поэтому вы не можете выразить свое логическое условие просто как >«15.07.2019» или же >15.07.2019. Увы, ни один из приведенных вариантов не верен.

    Чтобы функция ЕСЛИ распознала дату в вашем логическом условии именно как дату, вы должны обернуть ее в функцию ДАТАЗНАЧ (в английском варианте – DATEVALUE).

    Полная формула ЕСЛИ может иметь следующую форму:

    =ЕСЛИ(B2

    Как показано на скриншоте, эта формула ЕСЛИ оценивает даты в столбце В и возвращает «Послупил», если дата поступления до 10 сентября. В противном случае формула возвращает «Ожидается».

    Пример 2. Формула условия для дат с функцией СЕГОДНЯ()

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

    Если вы основываете свое условие на текущей дате, то можете взять функцию СЕГОДНЯ (в английском варианте — TODAY) в качестве аргумента функции ЕСЛИ.

    К примеру, сегодня — 9 сентября 2019 года.

    В столбце C отметим товар, который уже поступил. В ячейке C2 запишем:

    =ЕСЛИ(B2

    Пример 3. Расширенные формулы ЕСЛИ для будущих и прошлых дат

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

    Выделим даты, отстоящие более чем на месяц от текущей, в прошлом. Укажем для них «Более месяца назад». Запишем это условие:

    =ЕСЛИ(СЕГОДНЯ()-B2>30,"Более месяца назад","")

    Если условие не выполнено, то в ячейку запишем пустую строку "".

    А для будущих дат, также отстоящих более чем на месяц, укажем «Ожидается».

    Если все результаты попробовать объединить в одном столбце, то придется составить выражение с несколькими вложенными функциями ЕСЛИ:

    =ЕСЛИ(СЕГОДНЯ()-B2>30,"Более месяца назад", ЕСЛИ(B2-СЕГОДНЯ()>30,"Ожидается",""))

    Ссылка на основную публикацию
    Функция overdrive в мониторе
    Технология компенсации времени отклика LCD-матрицы, известная как Overdrive (у каждого производителя она имеет свое фирменное название) обеспечивает существенное ускорение переключения...
    Формула рандома в паскале
    Здравствуйте, уважаемые читатели нашего сайта. Сегодня мы рассмотрим две полезные процедуры в паскале - Random и Randomize.И опять я начну...
    Формула расчета мощности конденсатора
    Реактивная мощность обусловлена способностью реактивных элементов накапливать и отдавать электрическую или магнитную энергию. Eмкостная нагрузка в цепи переменного тока за...
    Функция еош в excel
    Всем добрый день! Эта статья посвящается вопросу, как можно избавится от ошибки в результате вычисления, так как это делает функция...
    Adblock detector