Как вытащить месяц и год из даты в excel

9

Как в Excel из даты вытянуть день, месяц и год отдельно.

Функция МЕСЯЦ() — извлекает номер месяца из даты, в числовом формате. Месяц возвращается как число от 1 до 12.

Функция ГОД() — извлекает значение года из даты. Год может быть целым числом от 1900 до 9999

Чтобы получить месяц в текстовом формате «Январь, Февраль, Март» необходимо использовать функцию ТЕКСТ, и задать нужный формат отображения месяца:

• Значение это ссылка на ячейку с датой;
• Формат для отображения обязательно ставить в «кавычки» , и в зависимости от количества букв «М» отображаться будет по разному:

Как вытащить месяц и год из даты в excel

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

Извлечь только месяц и год из числа с формулами

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

1. Введите формулу: = ТЕКСТ (A2; «ммм-гггг») в пустую ячейку помимо ваших данных, например, C2, см. снимок экрана:

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

Ноты:

1. Кроме приведенной выше формулы, вы также можете применить эту формулу: = ТЕКСТ (A2, «ммм») & «-» & ТЕКСТ (A2, «гггг») .

2. В приведенных выше формулах A2 указывает ячейку даты, которую вы хотите использовать, и разделитель « — »Используется для разделения месяца и года, вы можете изменить его на любые другие необходимые вам разделители.

Отформатируйте выбранные даты как другие форматы даты, как вам нравится

Работы С Нами Kutools for Excel‘s Применить форматирование даты вы можете быстро преобразовать нормальный формат даты в любое другое форматирование по вашему усмотрению. Нажмите, чтобы загрузить Kutools for Excel!

Преобразование даты в формат месяца и года с помощью Kutools for Excel

Если у вас есть Kutools for Excel, используя его Применить форматирование даты Утилита вы можете преобразовать дату в любое другое форматирование даты по своему усмотрению, не применяя никаких формул.

После установки Kutools for Excel, пожалуйста, сделайте это 🙁 Бесплатная загрузка Kutools для Excel сейчас )

1. Выделите ячейки даты, которые вы хотите преобразовать в формат месяца и года.

2. Нажмите Кутулс > Формат > Применить форматирование даты, см. снимок экрана:

3. В Применить форматирование даты В диалоговом окне укажите один формат даты, который вы хотите, в этом примере я выберу один из форматов месяца и года, см. снимок экрана:

Tips: Вы также можете выбрать Мар-01 в формате месяца и года.

4. Затем нажмите Ok or Применить , дата преобразована в формат месяца и года, см. скриншоты:

Извлеките только месяц и год с даты с помощью Kutools for Excel

Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!

Извлечение из даты в Excel

Чтобы получить реальную дату из числа дня или «n-го числа года», вы можете использовать функцию ДАТА.

В показанном примере формула в C5:

Функция построения даты берется из отдельных значений года, месяца и дня. Один из его трюков — это способность выполнять сверку с целью исправления дат, когда дни и месяцы находятся вне диапазона.

Например, ДАТА возвращает 9 апреля 2016 года со следующими аргументами:

В январе нет 100-го дня, поэтому ДАТА просто продвигается на 100 дней вперед с 1 января, а цифры возвращают правильную дату.

Формула на этой странице использует эту механику. В этом случае предполагается, что в этом году 2015 год, поэтому 2015 год жестко закодирован в течение года, а 1 используется в течение месяца. Значение дня выводится из столбца B, а функция ДАТА рассчитывает дату, как объяснено выше.

Если у вас есть дата в формате Юлиан, например 10015, где используется формат «дддгг», вы можете изменить формулу следующим образом:

Здесь мы используем ПРАВСИМВ, чтобы извлечь 2 символа справа за год, и ЛЕВСИМВ, чтобы извлечь 3 символа слева в течение дня. Месяц представлен как 1, как в первом примере.

Получить день с даты

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

Функция ДЕНЬ принимает только один аргумент — дату, из которой вы хотите извлечь день. В этом примере формула:

B4 содержит значение даты для 5 января 2016 года. Функция ДЕНЬ возвращает число 5, представляющее компонент дня даты.

Обратите внимание: вы можете использовать ДЕНЬ для извлечения дня из дня, введенного как текст:

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

Получить имя дня с даты

Если вам необходимо получить имя дня (например, понедельник, вторник и т. д.) С даты, есть несколько вариантов в зависимости от ваших потребностей.

Если вы хотите отображать имя дня, вам не нужна формула — вы можете использовать формат персонализированного номера. Выберите дату, затем перейдите к Форматирование ячеек (Ctrl + 1 или Cmd + 1)> Custom и введите один из этих настраиваемых форматов:

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

Если вы хотите преобразовать значение даты в текстовое значение, вы можете использовать функцию ТЕКСТ с настраиваемым числовым форматом, например, «ддд». Формула выглядит так:

Функция ТЕКСТ преобразует значения в текст с использованием формата чисел, который вы предоставляете. Обратите внимание, что в преобразовании теряется дата, остается только текст названия дня.

Если вы не хотите использовать вспомогательную таблицу, вы также можете отобразить номер дня недели в название месяца непосредственно в функции ВЫБОР следующим образом:

В этом случае введите имена дней недели, которые вы хотите вернуть (сокращенно или нет), в качестве значений в ВЫБОР после первого аргумента. ДЕНЬНЕД будет извлекать номер буднего дня, и ВЫБОР будет использовать это число для возврата n-го значения в списке. Это работает, потому что ДЕНЬНЕД возвращает число 1-7, соответствующее заданному дню недели. С настройками по умолчанию, воскресенье = 1 и суббота = 7. Смотрите ДЕНЬНЕД для получения дополнительной информации.

ВЫБОР — это больше работы по настройке, но она также более гибкая, так как позволяет привязать дату к любым требуемым значениям (т. е. Вы можете использовать значения, которые являются пользовательскими, сокращенными, не сокращенными, разными языками и т. д.).

Получить дни до даты

Чтобы вычислить дни до определенной даты, вы можете использовать вычитание и функцию СЕГОДНЯ. В обобщенной версии вышеприведенной формулы дата — это более поздняя дата — день, на который вы рассчитываете, — а функция СЕГОДНЯ используется для создания текущей даты.

В примере D5 содержит эту формулу:

В Excel даты — это просто серийные номера. В стандартной системе дат для окон, основанной на 1900 году, где 1 января 1900 года является номером 1. Даты действительны до 9999, что является серийным номером 2 958 465. Это означает, что 1 января 2050 года серийный номер 54789.

В примере датой является 9 марта 2016 года, который является серийным номером 42 438. Так:

Это означает, что до 1 января 2050 года, когда отсчитывается с 9 марта 2016 года, существует 13 033 дня.

Примечание: вам не нужно использовать функцию СЕГОДНЯ. Во втором примере формула в D6:

В третьем примере одна и та же базовая формула используется вместе с оператором конкатенации (&) для встраивания вычисленных дней в простое текстовое сообщение:

Поскольку между 10 декабря 2014 года и 25 декабря 2014 года существует 15 дней, результатом является следующее сообщение:

Осталось всего 15 дней!

Получить дни между датами

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

В примере формула в ячейке D6 возвращает 365, поскольку между 1/1/1999 и 1/1/2000 есть 365 дней.

Даты в Excel — это просто серийные номера, которые начинаются 1 января 1900 года.

В примере формула в ячейке D6 просто вычитает числовое значение 1/1/1999 (36161) из числового значения 1/1/2000 (36526), чтобы получить результат 365. Шаги выглядят так:

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

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

Обратите внимание, что СЕГОДНЯ будет пересчитываться на постоянной основе. Если вы откроете книгу позднее, значение, используемое для СЕГОДНЯ, обновится, и вы получите новый результат.

Функция ДНИ, введенная в Excel 2013, также вычисляет дни между двумя датами.

Получайте дни между датами, игнорируя годы

Для вычисления дней между двумя датами, игнорируя значения года, используйте функцию РАЗНДАТ. В показанном примере формула в D5:

Функция РАЗНДАТ может обрабатывать различные расчеты «разницы дат» для вычисления разницы между двумя датами в годах, месяцах и днях. РАЗНДАТ принимает 3 аргумента: начальную дату, конечную дату и единицу измерения, которая контролирует, какой результат будет возвращен.

В этом случае мы хотим, чтобы дни игнорировались годами, поэтому мы поставляем «yd» для единицы.

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

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

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

В показанном примере формула в ячейке B5:

Функция ДЕНЬ возвращает значение дня для даты. В примере показано, что значение дня для даты в B5 (5 июня 2016 года) равно 5. Поскольку даты — это только порядковые номера в Excel, мы можем вычесть 5, а затем добавить 1, чтобы получить значение даты на 1 июня 2016 года.

Получить первый день предыдущего месяца

Чтобы получить первый день предыдущего месяца для данной даты, вы можете использовать простую формулу, основанную на функции КОНМЕСЯЦА.

В показанном примере формула в ячейке B5:

Функция КОНМЕСЯЦА возвращает последний день месяца, основанный на заданной дате. Второй аргумент — это месяцы, в которых указывается, сколько месяцев в будущем или в прошлом нужно переместить перед возвратом последнего дня. Возвращаясь на 2 месяца, затем добавляя один день, мы можем рассчитать первый день предыдущего месяца с любой заданной даты.

В показанном примере месяцев указываются как -2, что заставляет КОНМЕСЯЦА возвращаться 4/30/2015. Затем добавляется 1 день для получения 5/1/2015.

Получить последний день месяца

Самый простой способ подсчитать последний день месяца — использовать функцию КОНМЕСЯЦА, которая предназначена для этой цели.

В показанном примере формула в ячейке B5:

Второй аргумент (месяцы) функции КОНМЕСЯЦА позволяет вам получить последний день месяца в будущем или в прошлом месяце. Когда вы используете ноль в течение месяцев, КОНМЕСЯЦА вернет последний день месяца в том же месяце.

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

Чтобы получить последний день следующего месяца, используйте:

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

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

Получить последний рабочий день в месяц

Чтобы получить последний рабочий день в месяце, вы можете использовать функцию РАБДЕНЬ вместе с функцией КОНМЕСЯЦА.

В примере формула в C5:

Работая изнутри, функция КОНМЕСЯЦА получает последний день месяца любой даты. К этому результату мы добавляем 1, что приводит к первому дню следующего месяца.

Эта дата переходит в функцию РАБДЕНЬ как «дата начала», а также -1 для «дней». Функция РАБДЕНЬ автоматически отступает на 1 день, принимая во внимание любые выходные. В результате будет последний рабочий день месяца.

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

= РАБДЕНЬ(КОНМЕСЯЦА(B5;0) + 1; -1; праздничные дни)

Функция РАБДЕНЬ предполагает, что выходные — это суббота и воскресенье. Если вам необходимо настроить выходные дни, вы можете использовать функцию РАБДЕНЬ.МЕЖД.

Получить n-ый день года

Если вы хотите получить день года с даты (например, n-го числа года), вы можете использовать формулу, которая использует функции ДАТА и ГОД вместе.

Например, с датой «1 июня 2016 года» в ячейке B4 следующая формула вернет 153:

То есть 1 июня 2016 года — 153-й день этого года.

Эта формула использует тот факт, что даты — это просто порядковые номера в Excel. Он определяет последний день предыдущего года и вычитает его, начиная с исходной даты B4. Результат — n-й день года.

Обратите внимание, что аргумент день в ДАТА предоставляется как ноль. Хорошей особенностью функции ДАТА является то, что она может обрабатывать значения ДЕНЬ, которые находятся «вне диапазона», и соответствующим образом корректировать результат. Когда мы даем ДАТА год, 1 месяц и 0 дней, функция ДАТА возвращает последний день предыдущего года.

Чтобы настроить формулу для возврата n-го дня года для текущей даты, просто используйте функцию СЕГОДНЯ для даты:

Получить день недели

Получить последний день недели

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

Формулы, в которых используется понятие «день недели», основаны на идее, что каждый день недели имеет определенное число. По умолчанию Excel назначает 1 на воскресенье и 7 на субботу.

В примере формула B6 является датой 1/16/2015, а формула в C6:

Число 7 (числовой аргумент в функции ОСТАТ) представляет день недели (день), который вы хотите. Только помните, что 1 — это воскресенье, а 7 — суббота. Так принято в США.

Чтобы решить эту формулу, Excel сначала вычитает день (7 в данном случае) из даты, а затем передает результат в функцию ОСТАТ в качестве числа. ОСТАТ возвращает остаток от деления этого числа на 7, которое затем вычитается из даты.

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

Примечание. Если дата имеет тот же день недели, дата будет возвращена.

Получить следующий день недели

Если вы хотите найти следующий конкретный день недели (т.е. следующую среду, пятницу или понедельник) с определенной датой начала, вы можете использовать общую формулу, которая использует функцию ДЕНЬНЕД с чем-то, называемым «день недели» (день). С помощью этой формулы вы можете делать такие вещи:

• Получить следующую пятницу
• Получить следующую субботу
• Получить следующий вторник

Формулы, в которых используется понятие «день недели», основаны на идее, что каждый день недели имеет определенное число. По умолчанию Excel назначает 1 на воскресенье и 7 на субботу. Таким образом, функция ДЕНЬНЕД при заданной дате возвращает число от 1 до 7. В двух словах эта формула сначала переводит дату вперед на 7 дней, а затем возвращается к правильной дате, вычитая результат вычисления, который использует функцию ДЕНЬНЕД.

В примере формула B6 является датой 1/16/2015, а формула в C6:

Число 2 представляет день недели (день), который вы хотите, в данном случае, понедельник.

Чтобы решить эту формулу, Excel сначала добавляет 7 к начальной дате в C6, а затем уменьшает B6 + 7-2 до 42025 (порядковый номер в системе даты Excel, который представляет 1/21/2015). Затем Excel вычисляет день недели для этой даты (4) и вычитает, что из исходной даты начала + 7 (42027 = 1/23/2015), чтобы получить окончательное значение даты 42013 = 1/9/2015

Если вы хотите получить следующий день недели с текущей даты, вы можете использовать функцию СЕГОДНЯ, например:

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

Получить n-й день недели в месяце

= Дата-ДЕНЬ(дата) + 1 + n * 7-ДЕНЬНЕД(дата-ДЕНЬ (дата) + 8-день)

Чтобы получить n-й день недели в месяце (т. е. первый вторник, третий вторник, четвертый четверг и т. д.), вы можете использовать формулу, основанную на функциях ДЕНЬ и НЕДЕЛЯ.

В показанном примере формула в E5:

Во-первых, эта формула определяет «дату начала». «Первое число месяца» рассчитывается с помощью функции ДЕНЬ:

Затем n * 7 дней до первого числа месяца, чтобы получить дату начала n недель с первого числа месяца. В общей форме формулы n представляет «n-ое».

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

В ДЕНЬНЕД снова вычисляется первое число месяца, затем добавляются 8 дней и вычитается значение для день (день недели).

День недели сокращенно «день» в общей форме формулы. Это число от 1 (воскресенье) до 7 (суббота), которое может быть изменено по мере необходимости.

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

Получить последний день недели за месяц

= КОНМЕСЯЦА(дата; 0) + 1-ДЕНЬНЕД (КОНМЕСЯЦА(дата; 0) + 1-день)

Чтобы получить последний день недели за месяц (то есть в прошлую субботу, последнюю пятницу, последний понедельник и т. Д.), Вы можете использовать формулу, основанную на функциях КОНМЕСЯЦА и ДЕНЬНЕД.

В показанном примере формула в D5:

Во-первых, эта формула определяет первый день следующего месяца * после * данной даты. Он делает это, используя КОНМЕСЯЦА, чтобы получить последний день месяца, а затем добавить один день:

Затем формула вычисляет количество дней, требуемых для «возврата назад» к последнему запрошенному буднему дню в предшествующем месяце (т. е. Месяце исходной даты):

В ДЕНЬНЕД КОНМЕСЯЦА снова используется для получения первого дня следующего месяца. С этой даты вычитается значение для дня недели, и результат вводится в ДЕНЬНЕД, который возвращает количество дней для отката.

И наконец, дни отката вычитаются из первого числа следующего месяца, что дает окончательный результат.

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

Получить первый понедельник до любой даты

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

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

Чтобы рассчитать эту корректировку динамически для любой даты, вы можете использовать тот факт, что понедельник — это второй день недели в схеме по умолчанию Excel. Просто вычислите день недели даты на 2 дня раньше и используйте этот результат для значения корректировки. Итак, при допущении даты 4 июня 2015 года (четверг) в ячейке A1 формула решается следующим образом:

Если вы считаете, что настройка дня недели запутанна и не интуитивна, вы используете альтернативную формулу, которая использует функцию ВЫБОР с жестко заданными значениями регулировки:

В этой версии используется ДЕНЬНЕД, чтобы получить индекс для дня недели (с понедельника как 1), а затем ВЫБОР возвращает правильное количество дней для возврата.

Если вам нужно найти понедельник до сегодняшнего дня, просто используйте функцию СЕГОДНЯ на дату:

Получить месяц

Получить месяц с даты

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

Функция МЕСЯЦ принимает только один аргумент — дату, из которой вы хотите извлечь месяц. В этом примере формула:

B4 содержит значение даты на 5 января 2016 года. Функция МЕСЯЦ возвращает число 1, соответствующее месяцу (январю) даты.

Обратите внимание, что вы можете использовать МЕСЯЦ для извлечения месяца со дня, введенного как текст:

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

Получить название месяца с даты

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

Если вы хотите отображать название месяца, вам не нужна формула — вы можете использовать формат персонализированного номера. Выберите дату и перейдите к Форматирование ячеек (Ctrl + 1 или Cmd +1), затем выберите Custom и введите один из этих настраиваемых форматов:

Excel отобразит только название месяца, но оно оставит неизменным значение даты.

Если вы хотите преобразовать значение даты в текстовое значение, вы можете использовать функцию ТЕКСТ с настраиваемым числовым форматом, например «ммм».

Функция ТЕКСТ преобразует значения в текст с использованием формата чисел, который вы предоставляете. Обратите внимание, что дата теряется при преобразовании: остается только текст для названия месяца.

Если вы хотите максимальной гибкости, вы можете «сопоставить» название месяца с именем месяца непосредственно в функции ВЫБОР следующим образом:

Введите названия месяца, которые хотите вернуть (сокращенно или нет), в качестве значений в ВЫБОР после первого аргумента, который вводится как МЕСЯЦ (дата). МЕСЯЦ выведет номер месяца, и ВЫБОР будет использовать это число для возврата n-го значения в списке. Это работает, потому что МЕСЯЦ возвращает число 1-12, соответствующее имени месяца.

ВЫБОР — это больше работы по настройке, но она также более гибкая, так как позволяет сопоставлять дату с любыми требуемыми значениями (т. е. Вы можете использовать значения, которые являются обычаями, аббревиатурами, аббревиатурами на другом языке и т. д. ).

Получать месяцы между датами

Чтобы рассчитать месяцы между двумя датами как целые числа, вы можете использовать функцию РАЗНДАТ.

В показанном примере формула в D5:

Обратите внимание, что РАЗНДАТ автоматически округляется вниз.

Функция РАЗНДАТ — это функция «совместимости», которая приходит из Лотос 1-2-3. По неизвестным причинам это документировано в Excel 2000, но вы можете использовать его в своих формулах во всех версиях Excel с того времени.

РАЗНДАТ принимает 3 аргумента: дата начала, дата окончания и единица измерения. В этом случае мы хотим месяцы, поэтому мы поставляем «м» для единицы.

РАЗНДАТ автоматически вычисляет и возвращает число в течение месяцев, округленное вниз.

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

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

Получить год с даты

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

Функция ГОД принимает только один аргумент — дату, из которой вы хотите извлечь год. В этом примере формула:

B4 содержит значение даты для 5 января 2016 года. Функция ГОД возвращает число 2016, представляющее год даты.

Обратите внимание, что вы можете использовать ГОД для извлечения года из дня, введенного как текст:

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

Извлечение даты с даты и времени

Excel обрабатывает даты и время, используя схему, в которой даты — это серийные номера, а время — дробные значения. Например, 1 июня 2000 г. 12:00 PM представлено в Excel как число 36678,5, где 36678 — это дата, а 0,5 — время.

Если у вас есть даты со значениями времени, и вы хотите извлечь только часть даты, вы можете использовать формулу, которая использует функцию ЦЕЛОЕ. Итак, если A1 содержит дату и время, 1 июня 2000 г. 12:00, формула, приведенная ниже, возвращает только часть даты (36678):

Временная часть значения (дробная часть) отбрасывается. Вы также можете использовать функцию ОТБР с тем же результатом:

Заметки:

1. При использовании любого из приведенных выше методов убедитесь, что вы используете формат даты для результата, который не включает время. В противном случае вы увидите время, отображаемое как 12:00 AM.
2. Для дат и времени (которые должны быть положительными в Excel) нет разницы в использовании ЦЕЛОЕ и ОТБР для извлечения целого числа. Но ЦЕЛОЕ фактически округляет числа до ближайшего целого, что делает разницу, когда значения отрицательные.

Извлечение времени с даты и времени

Excel обрабатывает даты и время, используя схему, в которой даты — это серийные номера, а время — дробные значения. Например, 1 июня 2000 г. 12:00 PM представлено в Excel как число 36678,5, где 36678 — это дата, а .5 — время.

Если у вас есть даты со значениями времени, и вы хотите извлечь только часть времени (дробную часть), вы можете использовать формулу, которая использует функцию ОСТАТ. Если предположить, что A1 содержит дату и время 1 июня 2000 г. 12:00, формула, приведенная ниже, вернет только часть времени (0,5):

Функция ОСТАТ возвращает остаток от деления. Первый аргумент — это число, а второй — делитель. Вот несколько примеров:

Таким образом, если вы используете ОСТАТ с делителем 1, результат будет дробной частью числа, если таковой имеется, потому что каждое целое число может быть равномерно разделено само по себе. Например:

Короче говоря, = ОСТАТ(число, 1) возвращает только дробную часть числа, отбрасывая целую часть, поэтому это удобный способ извлечь время из даты и времени.

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

Получить возраст от дня рождения

Если вам необходимо рассчитать возраст человека с даты его рождения, вы можете сделать это с помощью функций ДОЛЯГОДА, ЦЕЛОЕ и СЕГОДНЯ. В общей версии приведенной выше формулы, дата рождения — день рождения человека с года, а СЕГОДНЯ — дата, на которую рассчитывается возраст. Поскольку эта формула использует функцию СЕГОДНЯ, она будет продолжать рассчитывать и правильный возраст в будущем.

В этом примере активная ячейка содержит эту формулу:

ДОЛЯГОДА вычисляет десятичное число, представляющее долю года между двумя датами. Чтобы определить долю года в десятичном значении, Excel использует целые дни между двумя датами. Это просто в Excel, потому что все даты — это просто серийные номера.

Таким образом, в этом случае дата рождения предоставляется в качестве даты начала из ячейки D4, а сегодняшняя дата предоставляется в качестве конечной даты, с любезного разрешения функции СЕГОДНЯ.

Результатом ДОЛЯГОДА для Майкла Чанга, здесь в феврале 2016 года, является что-то вроде этого:

Затем функция ЦЕЛОЕ берет на себя и округляет это число до целочисленного значения, которое является числом 14. Хотя Майклу почти 15 лет, ему все еще только 14 по годам, так что это правильный результат.

Если вы хотите рассчитать возраст человека на определенную дату, просто замените функцию СЕГОДНЯ на эту дату или ссылку на ячейку на эту дату.

Чтобы проверить день рождения и вернуть «Несовершеннолетний» или «Взрослый», вы можете обернуть формулу возраста в функции ЕСЛИ следующим образом:

= ЕСЛИ(ЦЕЛОЕ(ДОЛЯГОДА(A1; СЕГОДНЯ())) <18; «Несовершеннолетний»; «Взрослый»)

Вы можете заменить 18 любым возрастом, имеющим смысл для вашей ситуации.

Чтобы рассчитать возраст на определенную дату, учитывая дату рождения, вы можете использовать функцию ДАТА вместо функции СЕГОДНЯ следующим образом:

Где A1 содержит дату рождения.

Получайте дни, часы и минуты между датами

Чтобы вычислить и отобразить дни, часы и минуты между двумя датами, вы можете использовать функцию ТЕКСТ с небольшой помощью функции ЦЕЛОЕ.

В показанном примере формула в D5:

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

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

Значение для дней вычисляется с помощью функции ЦЕЛОЕ, которая просто удаляет целую часть конечной даты минус дата начала:

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

Чтобы включить секунды, вы можете расширить формат пользовательских чисел следующим образом:

Получить финансовый квартал с даты

Если вы хотите рассчитать финансовый квартал от даты, а финансовый квартал начинается в месяце, отличном от января, вы можете использовать формулу, основанную на функции ВЫБОР.

Примечание: если вам просто нужно вычислить «нормальный» квартал на основе четвертичной системы, которая начинается в январе, вы можете использовать эту более простую формулу.

В показанном примере формула в ячейке D5:

Эта формула возвращает число 1-4, которое соответствует четвертичной системе, которая начинается в апреле и заканчивается в марте.

Функция выбора использует первый аргумент для «выбора» оставшихся элементов. Например, в схеме, где 1 = маленький, 2 = средний и 3 = большой, эта формула «отобразит» число 2 на «среднее».

В случае фискальных кварталов мы можем использовать эту же идею для сопоставления любого входящего месяца (1-12) с одним из 4 квартальных значений. Нам просто нужно использовать функцию МЕСЯЦ, чтобы получить номер месяца в качестве первого аргумента, а затем предоставить 12 номеров (по одному на каждый месяц года), которые тщательно упорядочены, чтобы отразить желаемый финансовый год:

Если вы хотите, чтобы номер квартала включал «К» со значением года, вы можете объединить его:

Возвращает значения типа: «К1 — 2016», «К2 — 2016» и т. д.

Получить процент завершения года

Если вам необходимо вычислить процент, оставшийся в году, на основе данной даты, вы делаете это с помощью формулы, основанной на функции ДОЛЯГОДА.

В примере формула в C5, которая вычисляет процент оставшегося года, выглядит так:

Функция ДОЛЯГОДА принимает две даты, дату начала и дату окончания и вычисляет время между ними в дробных годах. В этом случае мы используем конечную дату как есть, но генерируем начальную дату, равную первому дню того же года, с этим битом кода:

Это извлекает значение года из даты в B5 и вставляет его в функцию ДАТА, а также 1 для месяца и дня.

В результате функция ДОЛЯГОДА возвращает десятичное значение, соответствующее доле года между двумя датами.

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

Чтобы вычислить процент оставшегося года, просто скорректируйте формулу, чтобы вычесть дробный год из 1. В примере D5 содержит эту формулу:

Получить квартал от даты

Если вы хотите рассчитать квартал (то есть 1,2,3,4) от даты, вы можете использовать функцию ОКРУГЛВВЕРХ вместе с МЕСЯЦ.

В показанном примере формула в ячейке C5:

В этом случае формула сначала извлекает месяц как число от 1 до 12, а затем делит это число на 3. Затем результат округляется до ближайшего целого числа с помощью функции ОКРУГЛВВЕРХ.

Функция ОКРУГЛВВЕРХ работает так же, как функция ОКРУГЛ, за исключением того, что ОКРУГЛВВЕРХ всегда округляет цифры от 1 до 9 до заданного количества цифр.

Если вы хотите, чтобы номер четверти включал «Ч», вы можете конкатенировать. Формула в D5:

Результатом является буква «Ч», добавленная к номеру квартала.

Получить ту же дату в следующем месяце

Чтобы получить ту же дату в следующем месяце с определенной даты, вы можете использовать функцию ДАТАМЕС.

В показанном примере формула в ячейке B5:

ДАТАМЕС может получить «ту же дату» в будущем или в прошлом, исходя из количества предоставленных месяцев. Когда 1 дается месяцами, ДАТАМЕС получает ту же дату в следующем месяце.

Чтобы получить ту же дату за предыдущий месяц, используйте -1:

Получить ту же дату в следующем году

Чтобы получить ту же дату в следующем году с определенной даты, вы можете использовать функцию ДАТАМЕС.

В показанном примере формула в ячейке B5:

ДАТАМЕС может получить «ту же дату» в будущем или в прошлом, исходя из количества предоставленных месяцев. Когда 12 дается на месяцы, ДАТАМЕС получает ту же дату в следующем году.

Чтобы получить ту же дату за предыдущий месяц, используйте -12:

Получить дату начала в соответствии с установленной датой

Чтобы рассчитать требуемую дату начала в соответствии с установленной датой запуска / запуска / завершения, за исключением нерабочих дней, таких как выходные и праздничные дни, вы можете использовать функцию РАБДЕНЬ.

В показанном примере формула в D6:

Начиная с даты окончания Пт, 06-Янв-2015, функция РАБДЕНЬ отсчитывает назад 10 рабочих дней (исключая выходные и праздничные дни) и возвращает Вт, 18-дек-2014. Это необходимая дата начала активности, для которой требуется 10 рабочих дней.

Функция РАБДЕНЬ принимает три аргумента: дата начала, дни и (необязательно) праздники. Затем он добавляет дни к дате, автоматически исключая выходные и праздничные дни (если они предусмотрены).

В этом случае мы хотим считать назад от даты в будущем (дату запуска) на определенное количество рабочих дней. Решением является использование отрицательного значения для дней.

При отрицательном значении дня РАБДЕНЬ вычитает дни с даты начала, принимая во внимание выходные и праздничные дни.

Обратите внимание, что праздники не являются обязательными. Результат в D5 показывает разницу, когда отпуска не предусмотрены.

Если в вашем расписании пользовательские выходные дни (то есть что-то отличается от субботы и воскресенья), используйте функцию РАБДЕНЬ.МЕЖД вместо РАБДЕНЬ.

Получить номер недели с даты

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

Функция НОМНЕДЕЛИ принимает два аргумента, дату и, необязательно, аргумент с именем Тип возвращаемого значения, который управляет схемой, используемой для вычисления номера недели.

В этом примере формула:

B4 содержит значение даты для 5 января 2016 года. Функция ДЕНЬ возвращает число 2, представляющее число вычисляемой недели.

Обратите внимание, что по умолчанию функция НОМНЕДЕЛИ использует схему, где неделя 1 начинается 1 января, а неделя 2 начинается в следующее воскресенье (когда аргумент Тип возвращаемого значения опущен или поставлен как 1).

При значении Тип возвращаемого значения 2 неделя 1 начинается 1 января, а неделя 2 начинается в следующий понедельник.

С номерами недель в неделю неделя 1 начинается в понедельник первой недели в году с четверга. Это означает, что первый день года для недель ISO — это всегда понедельник в период с 29 января по 4 января.

Начиная с Excel 2010 для Windows и Excel 2011 для Mac, вы можете сгенерировать номер недели ISO, используя 21 как Тип возвращаемого значения:

Excel месяц

​Смотрите также​ ТЕКСТ преобразует числовое​​ рисунке, следует помнить,​ ​ на номера месяцев.​ то функция вернет​​ ячейку, в формулу,​ которой стоит число​ отрицательное число. Формула​ настроить​Пусть в ячейке​ которое в результате​Скопировав пример на пустой​. В этой же​ копирование и вставку​ ячеек, содержащих введенные​=A1-15​
​Как соединить в​Можно извлечь с​​ значение, которое возвращает​ ​ что данную формулу​​ Например, вы можете​
​ соответствующее ему название​ ​ написать ноль перед​
​ количества месяцев. Числа​ будет такая. +А2+С6.​формат даты Excel​А2​ становится равным​
​ лист, вы можете​​ формуле функция​
​ для каждой ячейки​ формулы, результаты могут​.​ одно предложение слова​ помощью​
​ функция ДАТА в​
​ можно применять для​ умножить каждое число​ для каждого заданного​ числом, чтобы не​
​ в ячейке могут​
​ Смотрите таблицу. В​
​, чтобы формулы считали,​может находится дата.​
​2011​ ​ настроить его так,​
​МЕСЯЦ​ из примера.​ отображаться в Excel​
​В ячейке C1 введите​ и даты из​
​формул​ формат даты. Он​ любого месяца, чтобы​
​ на среднее количество​ ​ числа.​ включалась функция формулы,​​ стоять как положительные,​
​ ячейке С6 стоит​ смотрите в статье​ Для определения последнего​. Затем функция​
​ как вам нужно.​
​возвращает значение​
​Внимание! Чтобы пример правильно​ как​
​=A1+30​ ячеек, смотрите в​Excel из даты месяц,​ указано во втором​ получить дату последнего​
​ дней в месяце​Вариант2 автономный и более​ т.д. Смотрите об​ так и отрицательные.​ отрицательное число.​ «Преобразовать дату в​
​ понедельника месяца можно​ДАТА​1​6​ работал, его нужно​числа​
​.​ статье «Функция «СЦЕПИТЬ»​ год, день​ аргументе функции –​ дня, а не​ (30).​ удобный для использования​ этих приемах в​Прибавить год к дате​

Сложение или вычитание дат

​Если мы в​ текст Excel» тут.​ использовать формулу:​вычитает 12 из​2​, а функция​ вставить в ячейку​; в этом случае​В ячейке D1 введите​ в Excel».​. Как вставить текущую​ «дд.мм.гг»​ только для февраля.​Формула функции:​ формулы на разных​ статье «Как ввести​ Excel​ формуле напишем знак​

В этой статье

​В Excel можно​=КОНМЕСЯЦА($A2;0)-ОСТАТ(КОНМЕСЯЦА($A2;0)​

​ значения месяца, в​3​

​ дата 16.02.2011 может​=C1-15​О других способах​

Добавление и вычитание дней из даты

​ дату, смотрите в​Во второй части функция​ Иначе она выглядела​=ТЕКСТ(B2*30;»mmmm»)​ листах или рабочих​ отрицательное число в​.​ «минус» и укажем​ считать календарные или​+5​ результате чего значение​4​ — значение​Чтобы переключиться между просмотром​ отображаться как 40​.​ различных расчетов, вычислений​ статье «Текущая дата​ ТЕКСТ определяет день​

​ бы так:​​будет работать правильно, и​​ книга без применения​

​ Excel».​​Чтобы сильно не​​ адрес ячейки, в​

​ рабочие дни. Например,​​;7)​​ месяца становится равным​

​5​​9​​ результатов и просмотром​

​ 590.​Ячейки A1 и C1​ дат, смотрите в​ в Excel» здесь.​ недели для исходной​=ДАТА(ГОД(A2);3;0)​ в качестве результата​ внешних ссылок.​Время от времени возникает​ заморачиваться с формулами​

Добавление и вычитание месяцев из даты

​ котором стоит отрицательное​ к дате прибавить​Для определения последнего вторника​1​A​. Функция​

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

​B​ДАТА​ результаты, нажмите клавиши​ виде порядковых номеров,​ (08.02.10 и 10.03.10)​ по этой теме»​

​ формат даты (не​ ее аргументе, с​ дату на лету​Теперь будем преобразовывать обратно​

​Подобно тому как показано​​ номер месяца на​​ можно воспользоваться выше​

​ прибавится к дате.​​ рабочие дни без​​=КОНМЕСЯЦА($A2;0)-ОСТАТ(КОНМЕСЯЦА($A2;0)​

​ДЕНЬ​Дата​объединяет эти три​

​ CTRL+` (знак ударения)​​ над которыми можно​​ для февраля и​

​ в конце статей.​ ячейки), чтобы формулы​ помощью формата ячеек​

​ с помощью трех​ – имя месяца​ выше, в случае,​​ название и наоборот​​ перечисленными вариантами.​

​ Это уже правило​​ выходных и праздников.​+4​возвращает значение​09.06.2009​ значения в дату,​​ или на вкладке​​ выполнять вычисления. По​ марта, а ячейки​В Excel можно​ считали даты, смотрите​

​ «дддд» указанном во​ аргументов:​ в соответствующий ему​ когда необходимо «вытянуть»​ (например, 3 на​Первый способ.​ математики.​ Читайте в статье​;7)​9​

​Формула​ которая находится через​ Формулы в группе​ умолчанию дате 1 января​ B1 и D1 —​

​ округлять числа несколькими​​ в статье «Преобразовать​​ втором аргументе.​​Год – можно указать​​ порядковый номер в​​ название месяца из​​ «Март» или «Октябрь»​​Прибавить года в​​К дате прибавить месяц​

​ «Количество дней между​​Для определения последней пятницы​​, и к нему​​Описание (результат)​​ три года в​

​ Зависимости формул нажмите​​ 1900 г. соответствует число​​ даты (24.01.10 и​​ способами. Но, иногда,​​ дату в текст​​В новых версиях Excel​​ любое число только​ году.​ даты, необходимо воспользоваться​

Добавление и вычитание лет из даты

​ на 10).​ днях (365 дней​ в Excel.​ датами без праздников​

​ прибавляется 5, в​=ДАТА(ГОД(A2)+3,МЕСЯЦ(A2)+1,ДЕНЬ(A2)+5)​ будущем, —​ кнопку Показывать формулы.​ 1, а 1 января​ 23.02.10), когда необходимо​ нужно отключить округление​ Excel».​ предусмотрена функция КОНМЕСЯЦА,​ лишь в диапазоне​

​Пример1. Формула первая:​

​ помощью функции МЕСЯЦ.​

​Сначала рассмотрим, каким способом​ – 1 год,​

​Первый способ.​ в Excel» здесь.​=КОНМЕСЯЦА($A2;0)-ОСТАТ(КОНМЕСЯЦА($A2;0)​ результате получается​Добавление 3 лет, 1​09.06.12​Скопировав пример на пустой​ 2010 г. — 40 179, поскольку​

​ перевести средства для​ чисел. Как это​Рассмотрим еще несколько​ которая служит альтернативным​

​ от 1900 и​=МЕСЯЦ(ДАТАЗНАЧ(A2&»1″))​Если у вас есть​ изменить номер месяца​ 730 дней –​Чтобы прибавить или​Или прибавить, вычесть​+1​14​

​ месяца и 5​.​ лист, вы можете​ интервал между ними​

​ заданных дат платежей.​

​ сделать, читайте в​

​ решением для выше​

​Значение Декабрь1 Excel автоматически​

​ список с датами​

​ его название. А​

​ вычесть месяца, можно​

​ календарные дни. Смотрите​

​;7)​. Наконец, функция​

​Можно использовать похожую формулу​

​ настроить его так,​

​ составляет 40 178 дней.​

​Чтобы быстро прибавить к​

​ статье «Как убрать​

​работы с датами в​

​ описанной формулы:​

​Месяц – любое положительное​

​ преобразует в дату​ и нужно «вытянуть»​

​ после речь пойдет​

​Второй способ​ воспользоваться формулами для​

​В файле примера приведена​ДАТА​

Принцип работы формулы

​ 09.06.2009 (14.07.2012)​ для добавления месяцев​ как вам нужно.​Если результат отображается как​ дате определенное количество​ округление чисел в​Excel​

​Благодаря функции КОНМЕСЯЦА можно​ или отрицательное число.​​ дек.01. Далее функция​​ из них название​ о том, как​.​​ счета дней. Рассмотрели​​ посчитать возраст в​ универсальная формула для​объединяет эти три​=ДАТА(ГОД(A2)+1;МЕСЯЦ(A2)+7;ДЕНЬ(A2)+5)​ к дате. Например,​1​​ число, выберите ячейки​​ месяцев или вычесть​ Excel».​​.​​ определять дату для​​День – любое положительное​​ ДАТАЗНАЧ преобразует дату​​ месяцев, то функция​​ выполнить обратный процесс,​​Прибавить количество месяцев.​​ выше. Если нужно​​ Excel».​​ нахождения нужного дня​ значения (2011, 1​Добавление 1 года, 7​ используя данные из​2​​ B5 и C5​​ их из нее,​

​Предположим, что вы хотите​Извлечь месяц из даты​ текущего, предыдущего или​ или отрицательное число.​ в число после​ ИНДЕКС в приведенном​ то есть изменить​ Например, 1 год​​ прибавить один месяц​​Прибавить к дате дни​ по порядковому номеру​​ и 14) в​​ месяцев и 5​

Добавление к дате определенного количества дней, месяцев и лет

​ предыдущего примера, можно​3​ и выполните указанные​ можно использовать функцию​

​ добавить две недели​

​Excel.​ будущего месяца заполнив​Например, если в третьем​ с помощью функции​ выше виде выдаст​ название на соответствующий​ — 12 месяцев,​ (например, сентябрь), то​ в Excel.​ дня недели.​

​ дату, находящуюся через​

​ добавить 9 месяцев​4​

​ ниже действия.​ ДАТАМЕС.​ к сроку, установленному​Например, в ячейке​ лишь только 2​ аргументе указано число​ МЕСЯЦ мы получаем​ ошибку. Если вы​

​ ему порядковый номер.​ 3 года –​ прибавляем 30 дней.​Чтобы прибавить к​

​В качестве аргументов формулы​ один год, семь​ 09.06.2009 (14.01.2011)​ к дате 09.06.09​5​На вкладке​В функции​ в календарном плане​ А1 стоит дата​

​ аргумента:​ 0 как номер​ порядковый номер в​ хотите, чтобы формула​

​Первый способ, который пришел​

​ 36 месяцев, т.д.​

​ Если февраль –​

​ дате календарные дни,​

​ задаются: порядковый номер​

​ месяцев и пять​

​В формулах в примере​

​ с помощью формулы​

​ проекта, и определить​

​ — 12.06.2016. Нам​Нач_дата – исходная дата​ дня, Excel вычислит​ году для текущего​

​ работала правильно, необходимо​

​ мне на ум,​ Смотрите строку 14​ то, 28 или​ достаточно, написать это​

​ дня недели, день​ дней в будущем,​​ используются указанные ниже​​=ДАТА(ГОД(A2);МЕСЯЦ(A2)+9;ДЕНЬ(A2))​7​

​используются два значения​ новую дату окончания,​ нужно извлечь из​

​ или ссылка на​ дату последнего дня​

​ названия месяца. По​ при помощи функции​

​ это просто составить​ в таблице на​

Принцип работы формулы

​ 29 дней. Если​ число дней или​ недели, месяц и​ то есть​аргументы​

​. Эта формула возвращает​8​​Ячейки​​ (аргумента): начальная дата​ или выяснить по​ даты месяц.​​ нее.​​ из предыдущего месяца.​ сути формулу можно​ МЕСЯЦ «вытянуть» номер​ список ячеек с​ изображении ниже.​ нужно прибавить два​​ в ячейке, или​​ год. Универсальная формула​​14.01.2011​​(аргумент — это значение,​​ дату​​A​нажмите кнопку​ и количество месяцев,​ списку задач проекта,​Первый вариант​​Число_месяцев – порядковый номер​​ Для наглядного примера​ упростить:​ месяца из ячейки​ последовательными названиями месяцев,​​Третий способ.​​ месяца (сентябрь и​​ в формуле. У​​ представляет собой комбинацию​.​ передаваемое функции).​09.03.10​​B​​Формат​​ которые необходимо добавить​​ сколько времени потребуется​​.​​ месяца начиная от​ составим формулу с​=МЕСЯЦ(A2&»1″)​​ с датой. Формула​​ а затем использовать​​Пишем универсальную формулу.​​ октябрь), то прибавляем​ нас такая таблица.​ двух вышеуказанных формул​Первый день месяца можно​Формула для сложения дат​.​Дата​и выберите пункт​​ или вычесть. Чтобы​​ для выполнения одной​

Первый день месяца в MS EXCEL

​В ячейке В1​ исходной даты. Может​ числом 0, которая​Пример2. Формула вторая:​

​ будет выглядеть следующим​​ функцию:​​В ячейке В15 написали​ 61 день, т.д.​В ячейке А2 стоит​ (файл примера размещен​ определить с помощью​

​начальная_дата: дата или​

​Чтобы этот пример проще​

​Количество прибавляемых или вычитаемых​​Формат ячеек​​ вычесть месяцы, введите​​ задачи. Можно сложить​​ пишем такую формулу​ быть любое положительное​
​ возвращает 29 февраля​

​=ВПР(A2;$G$1:$H$12;2;0)​ образом:​=ИНДЕКС($A$2:$A$13;B2)​ такую формулу. =ДАТА(ГОД(A14)+1;МЕСЯЦ(A14);ДЕНЬ(A14))​​В ячейке В8 стоит​​ дата, к которой​​ в статье Первый​​ функции ДАТА() или​​ ссылка на ячейку,​​ было понять, скопируйте​ лет​.​

​ отрицательное число в​ или вычесть дни,​

​ (формат в ячейке​

​ или отрицательное число.​ 2000-го года:​Пример3. Формула третья:​

Последний вторник месяца в MS EXCEL

​=ИНДЕКС($A$2:$A$13;МЕСЯЦ(E2))​где $A$2:$A$13 – это,​Пояснение к формуле.​

​ формула =А8+61. А​​ будем прибавлять дни.​​ вторник месяца).​ КОНМЕСЯЦА().​ содержащую дату.​ его на пустой​

​09.06.09​​В диалоговом окне​​ качестве второго аргумента​

​ отталкиваясь от даты,​ – «ДАТА»). =МЕСЯЦ(А1)​

​ Примеры заполнения аргументов​​=ДАТА(2000;3;0)​​Наиболее оптимальным вариантом является​

​Еще один способ (возможно,​ конечно, диапазон с​

​Нам нужно было​​ в ячейке В9​​В ячейке В3​

​Решение обратной задачи: если​Если ячейка​добавляемый_год: количество добавляемых​ лист.​3​

​Формат ячеек​ (например, =ДАТАМЕС(«15.02.2010″;-5). Результатом​ с помощью простой​ Получится так.​ для функции КОНМЕСЯЦА,​В следующем примере формулы​ лаконичная формула =МЕСЯЦ(A2&»1»).​ самый удобный) -​ названиями месяцев, а​ прибавить к дате​

​ в формуле указали​ стоит формула =А2+2.​ дана дата, а​А2​ лет.​Копирование примера​02.09.09​откройте вкладку​ этой формулы будет​ формулы или использовать​

Работа с датами в Excel.

​Второй вариант.​ которая возвращает дату​​ вместо ввода статических​​ В ней не​ использовать функцию ТЕКСТ​ B2 — ячейка​ 1 год, п.э.​ адрес ячейки. Формула​ Здесь в формуле​​ нужно определить, какой​​содержит дату, то​добавляемые_месяцы: количество добавляемых​Выделите пример, приведенный в​​-5​
​Число​
​ дата​ функции листа, предназначенные​Чтобы в ячейке​ 29 февраля 2000-го​ значений в первые​ нужно резервировать названия​ (все, что делает​ с порядковым номером​ в формуле написали​ такая. =А8+С9​ сразу указали количество​ по счету это​ нижеследующие формулы вернут​​ месяцев.​​ этой статье. При​10.12.10​.​15.09.2009​
​ для работы с​ В1 месяц был​ года:​ 2 аргумента функции​ месяцев и присваивать​ функция ТЕКСТ, это​ в году, для​ число «1» после​Второй способ​ дней, которые нужно​ день недели в​
​ первый день месяца​добавляемые_дни: количество добавляемых​ работе в приложении​25​В поле​
​.​ датами в Excel.​
​ написан прописью, формулу​=КОНМЕСЯЦА(«01.01.2000»;1)​ ДАТА (год и​ им соответственные порядковые​ преобразует числовое значение​ которого мы ищем​ слова «ГОД».​​.​ прибавить к дате.​ текущем месяце (например,​
​ этой даты (см.​ дней.​ Excel Web App​Формула​Категория​Значение начальной даты можно​
​Добавление и вычитание дней​ напишем такую.​Если же мы укажем​ месяц), используется функция​ номера. К тому​ в текст с​ его название.​Если нужно прибавить​В Excel есть​Можно вместо цифры​ 01/03/2011 – первый​
​ файл примера):​В каждой формуле указанное​ копируйте и вставляйте​Описание (результат)​выберите пункт​ указать с помощью​ из даты​=ИНДЕКС(<"январь";"февраль";"март";"апрель";"май";"июнь";"июль";"август";​ во втором аргументе​ ГОД, которая возвращает​ же в ней​ соответствующим форматированием).​По схожему принципу с​ месяцы, то пишем​
​ специальная функция. Смотрим​ дней, указать адрес​ вторник), приведено в​=КОНМЕСЯЦА(A2;-1)+1​ количество лет, месяцев​ по одной ячейке​=ДАТА(ГОД(A2)+B2;МЕСЯЦ(A2);ДЕНЬ(A2))​Дата​ ссылки на ячейку,​Добавление и вычитание месяцев​»сентябрь»;»октябрь»;»ноябрь»;»декабрь»>;МЕСЯЦ(A1))​
​ отрицательное число, тогда​ годы с исходных​ используется всего лишь​Если у вас есть​ функцией ИНДЕКС работает​ в формуле число​ строки 10, 11,​ ячейки с количеством​ статье Первый вторник​
​=ДАТА(ГОД(A2);МЕСЯЦ(A2);1)​ и дней прибавляется​
​ из примера за​
​Добавление 3 лет к​и нажмите кнопку​ содержащую дату, или​ из даты​Получится так, смотрите​ функция возвращает нам​ дат. Аналогично используется​ одна функция.​ список с датами​ другая функция, которую​ после слова «Месяц».​ 12 в таблице.​ дней, которые нужно​ месяца (обратная задача).​Если​
​ к дате, содержащейся​ раз. Внимание! Не​ дате 09.06.09 (09.06.12)​ОК​ введя дату в​Добавление и вычитание лет​
​ ячейку В2.​​ дату последнего дня​
​ функция МЕСЯЦ возвращающая​Часто в процессе работы​ и нужно «вытянуть»​ вы можете использовать​
​Подробнее об этой​Делаем ячейку для​ прибавить. В ячейке​В Excel работа с​А2​ в ячейке A2.​ выделяйте заголовок строки​=ДАТА(ГОД(A3)+B3;МЕСЯЦ(A3);ДЕНЬ(A3))​. Значения в ячейках​
​ кавычках, например «15.02.2010».​ из даты​
​Извлечь год из даты​ для предыдущего месяца:​ порядковый номер месяца​ с датами возникает​ из них название​ — ВЫБОР. Разница​ формуле, что еще​ формулы активной. На​ В4 стоит такая​
​ датами​содержит дату, а​Например, в ячейке A5​ или столбца.​
​Вычитание 5 лет из​ должны отобразиться как​Предположим, нужно добавить 16​Добавление к дате определенного​Excel.​=КОНМЕСЯЦА(«01.03.2000»;-1)​ в году из​ необходимость динамического определения​
​ месяцев, просто введите:​ состоит в том,​​ можно в нее​
​ закладке «Формулы» в​ формула. =А2+С4. В​предполагает​B2​ (вторая формула) функция​
​Выделение примера в справке​
​ даты 02.09.09 (02.09.04)​ даты.​ месяцев к дате​ количества дней, месяцев​В ячейке С1​
​Разумеется, если мы указываем​​ исходной даты. К​
​ даты и последнего​=ТЕКСТ(E2;»mmmm»)​ что она не​ написать и где,​ разделе «Библиотека функций»​ ячейке С4 стоит​сложение, вычитание календарных или​число месяцев, то​
​ГОД​
​Нажмите клавиши CTRL+C.​​=ДАТА(ГОД(A4)+B4;МЕСЯЦ(A4);ДЕНЬ(A4))​Чтобы этот пример проще​
​ 16 октября 2009​
​ и лет​ пишем такую формулу.​ во втором аргументе​ этому номеру в​ дня недели в​где E2 – это,​
​ использует ячейки листа,​ смотрите в статье​ нажимаем на кнопку​ число дней, которое​
​ рабочих дней, посчитать​ формула​применяется к дате​Создайте пустую книгу или​Добавление 25 лет к​ было понять, скопируйте​ г.​
​Допустим, что выплата средств​ =ГОД(A1) Получится так.​ число 0 тогда​ формуле добавляется число​ месяце. Естественно можно​ конечно, ячейка с​ в которых хранятся​ «Дата в Excel.​ «Дата и время».​ нужно прибавить к​ разницу дат в​=ДАТА(ГОД(A2);МЕСЯЦ(A2)+B2;1)​ в ячейке A2​ лист.​ дате 10.12.10 (10.12.35)​ его на пустой​В ячейке A5 введите​
​ со счета производится​Извлечь число из даты​ получим необходимое значение​ 1. Благодаря чему​ просто посмотреть в​ датой.​ названия месяцев. Названия​ Формула».​ В появившемся окне​ дате (10).​ Excel, преобразовать дату​вернет первое число месяца,​

Функции и формулы для преобразования номера в название месяца

​ (09.06.2009) и возвращает​На листе выделите ячейку​В каждой из трех​ лист.​16.10.09​ 8 февраля 2010​ в Excel.​

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

Изменение порядкового номера месяца на его название в Excel

​ отстоящего от исходного​2009​ A1 и нажмите​ формул указанное количество​Копирование примера​.​ г. Необходимо перевести​

​ для текущего месяца:​ следующего месяца. Так​ для автоматизации некоторых​ с числами (1-12),​ в качестве аргумента​ не только считать​ «ДАТАМЕС».​ даты календарные дни,​

​ посчитать стаж, возраст​ на число месяцев​в качестве значения​ клавиши CTRL+V. При​ лет из столбца​Выделите пример, приведенный в​В ячейке B5 введите​ средства на счет,​ пишем такую формулу.​=КОНМЕСЯЦА(«01.02.2000»;0)​ как в третьем​ задач в Excel​ функция в приведенной​

​ функции.​ даты, но и​Диалоговое окно функции заполнили​ пишем формулы, как​,​ из ячейки​ года. Затем формула​ работе в Excel​ B добавляется к​ этой статье. При​=ДАТАМЕС(A5,16)​

​ чтобы они поступили​ =ДЕНЬ(A1) Получилось так.​Чтобы определить високосный год​ аргументе стоит число​ с помощью формул​ выше форме будет​Функция ВЫБОР принимает в​ вычислить дату по​ так.​ в сложении, только​

​посчитать календарные дни с​B2​ добавляет 1 к​ Web App повторите​ значению, полученному из​ работе в приложении​

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

​ за 15 календарных​Как узнать в​ или нет в​ 0, автоматически получаем​ нужно динамически получить​ работать неправильно. Вы​

​ качестве аргументов номер​ дню недели. Например,​«Нач_дата» – указали адрес​ ставим знак «минус».​ выходными, но без​(т.е. если​ значению года, в​ копирование и вставку​ даты в столбце​ Excel Web App​Функция использует значение в​ дней до указанной​Excel количество дней в​ Excel существует множество​ порядковый номер последнего​ дату последнего месяца​

​ должны помнить, что​

​ индекса (который определяет​ какое число будет​ ячейки с датой.​ Например, чтобы вычесть​ праздников​А2​ результате чего это​ для каждой ячейки​

​ A.​ копируйте и вставляйте​ ячейке A5 как​ даты. Кроме того,​ месяце​

​ решений. Вот несколько​

​ дня в текущем​ особенно в феврале​ число 1 для​

​ какой из элементов​ на третий вторник​ В строке «Число_месяцев»​ 2 дня из​, т.д.​содержит 05/08/1999, а​ значение становится равным​ из примера.​Например, в ячейке A6​ по одной ячейке​ дату.​ известно, что платежный​.​ из них:​ месяце, который нас​ и определить день​ Excelя это 1​ в списке значений​

​ июня месяца 2016​ поставили количество месяцев.​ даты, пишем такую​Внимание!​B2​2010​Внимание! Чтобы пример правильно​ функция​

​В ячейке C5 введите​

​ цикл счета составляет​Используем предыдущую таблицу​При работе с датами​

Как преобразовать название месяца в порядковый номер

​ интересует.​ недели. Ведь у​ января 1900 года,​ вернуть в качестве​ г. Эта функция​

​ Нажимаем «ОК». Получилась​

​ формулу =А2-2. Можно​

​В ячейках Excel,​– 3, то​. Функция​ работал, его нужно​ГОД​ раз. Внимание! Не​=ДАТАМЕС(«16.10.09»;16)​ 30 дней, и​ с датой 12.06.2016.​ особую сложность предоставляет​​ високосного года (366)​

​ результата) и список​

​ пригодится при составлении​

​ такая формула. =ДАТАМЕС(А8;2)​

​ в формуле указать​ где будет стоять​ результат будет =​МЕСЯЦ​ вставить в ячейку​применяется к дате​ выделяйте заголовок строки​.​ необходимо определить, когда​ В ячейке А2​

Как рассчитать последний день месяца в Excel

​ тот факт, что​На этом рисунке ниже​ месяц февраль имеет​ 2 января 1900​ значений. Интересующиеся подробностями,​ договора, т.д. Смотрите,​ Смотрите на изображении​ не число, а​ дата, должен быть​ 01/11/1999).​возвращает значение​ A1.​ в ячейке A2​ или столбца.​В этом случае функция​ следует перевести средства​ пишем такую формулу.​ раз в 4-е​ показано, как вычислить​ 29 дней, а​ года и так​

Сколько дней в феврале для любого года и любой даты?

​ конечно же, могут​ как настроить Excel​ в таблице строку​ адрес ячейки с​ установлен формат «Дата».​Тот же результат вернет​

​6​Чтобы переключиться между просмотром​ (09.06.09) и возвращает​Выделение примера в справке​ использует значение даты,​ для платежа в​=ДЕНЬ(КОНМЕСЯЦА(A1;0))​ года количество дней​ день недели и​ в обычном году​

​ скачать файл с​ для этого в​ 10.​ количеством дней, которые​

1. ​ Тогда дата будет​ формула​, и к этому​ результатов и просмотром​2009​
2. ​Нажмите клавиши CTRL+C.​ введенное непосредственно («16.10.2009»).​
3. ​ марте 2010 г.,​Получилось так.​

​ в году составляет​ дату последнего дня​ – 28.​ тот же месяц​ примерами.​ статье «Узнать дату​Если нужно вычесть​ нужно вычесть.​ отражаться правильно, и​=КОНМЕСЯЦА(A2;B2-1)+1​ значению добавляются 7​

​ формул, возвращающих эти​

​как год. Затем​Создайте пустую книгу или​В ячейках B5 и​ чтобы они поступили​В июне месяце 30​ 366, тогда как​ февраля для любого​Для начала рассмотрим, как​ — январь. Поэтому​Если в качестве списка​ в Excel по​ месяцы, то ставим​Если в ячейке​ формулы будут считать.​Последний день месяца определяется​ месяцев. Поскольку суммой​ результаты, нажмите клавиши​ формула добавляет 3​ лист.​ C5 должна отображаться​ за 15 дней​ дней.​ в обычных годах​ года:​ вычислить дату последнего​

​ ТЕКСТ(E2;»ММММ») возвращает «Январь»​

Какой день недели для последнего дня месяца?

​ значений указать названия​ дню недели, т.д.»​ количество месяцев со​ стоит отрицательное число​ В Excel есть​ в статье Последний​

​ 6 и 7​ CTRL+` (знак ударения)​ (значение в ячейке​На листе выделите ячейку​

1. ​ дата​ до этой даты.​Как прибавить к​ 365. Поэтому иногда​Формула состоит из двух​ дня февраля месяца​ для каждого номера​ месяцев (по очередности),​
2. ​ здесь.​ знаком «минус».​ и его нужно​ формат ячеек и​ день месяца.​ является 13, функция​ или на вкладке​ B2) к значению​

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

​ A1 и нажмите​16.02.11​В ячейке A1 введите​ дате дни, месяцы,​ приходится воспользоваться вышеописанными​

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

1. ​ вычесть, то в​ формат чисел. Как​Определим последний вторник месяца,​
2. ​ДАТА​ Формулы в группе​ года, результатом этого​ клавиши CTRL+V. При​.​08.02.10​ года, смотрите в​ примерами и формулами​ оператором амперсантом (&»​ чтобы определить високосный​

​Однако вы можете легко​

​ индекса — ячейку​ чпособов, как ввести​ месяцев можно поставить​ формуле ставим знак​ разобраться в форматах​ последнюю пятницу или​

​добавляет 1 год​

​ Зависимости формул нажмите​ действия является​ работе в Excel​Примечание:​.​ статье «Работа с​

​ для различных вычислений​

Как определить високосный год или нет в Excel?

​ — «&):​ год или нет:​ изменить последовательные числа​ с номером месяца,​ отрицательное число в​

​ адрес ячейки, в​ «+», чтобы вычесть​ чисел, как правильно​ последнюю среду.​ к значению года,​ кнопку Показывать формулы.​2012​ Web App повторите​ В зависимости от формата​В ячейке B1 введите​ датами в Excel».​ в Excel.​В первой части функция​