Excel как прибавить месяц к дате

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

Функция ДАТА объединяет три числа и делает из них дату. Аргументы у нее следующие: (год;месяц;день) . Поставим в А1 курсив и наберем формулу:

В результате, формат ячейки будет изменен и в ней будет проставлено, например, 10 июля 2017 года.

Указывать аргументы можно по-разному. Если выбрать 117 год, то Эксель это также поймет как 2017, поскольку отсчет начинается с 1900 года, а 1900+117=2017. День и месяц могут быть как положительными, так и отрицательными числами. Например, если с начала июля отнять 5 дней, то получится 25 июня.

А если отнять с начала 2017 года 2 месяца, то будет октябрь 2016.

Кроме того последние два аргумента не обязательно должны быть в пределах от 1 до 12 и от 1 до 31. Если значения будут больше, то получится вот что: 35-31=4 — это день, 13-12=1 и плюс 31 день с предыдущего расчета, выходит второй месяц. Ну и 12 месяцев — это год. Значит из 35.13.2017 будет 4 февраля 2018-го.

Использовать функцию ДАТА можно, если у Вас все данные находятся в разных ячейках и к ним применен общий формат. В этом случае, она объединит все числа. Поставьте равно, пропишите функцию и выделяйте по очереди, сначала год ( С3 ), потом месяц ( В3 ) и день ( А3 ). Теперь в D3 указан нужный день, и ячейка отформатирована должным образом.

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

Каждому дню, начиная с 01.01.1900, соответствует определенное число, отсчет начинается с единицы. Поэтому, если Вы наберете 30.01.1900 и вместо даты для ячейки выберите общий формат, то в ней будет поставлено 30.

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

Она берет год из D3 и прибавляет к нему 2, к месяцу прибавится 1, а дню — 5. Потом полученное значение отображается в Е3 . Так можно рассчитывать день годовщины работы или окончание контракта.

Если Вам дали документ, а день, месяц и год в нем отображаются в виде набора цифр ( А7 ), тогда нашу функцию можно применить, чтобы преобразовать число в дату. Для этого необходимо использовать в качестве аргументов дополнительные функции: ПРАВСИМВ , ПСТР и ЛЕВСИМВ .

Если число имеет вид: 10072017 (ДДММГГГГ), то формула выглядит так:

Первый аргумент — год — это 4 правых символа из А7 (2017). Потом берем месяц — вытягиваем из А7 два числа, начиная с третьего (07). А день — это 2 цифры слева (10).

Когда число записано так: 20170710, то есть ГГГГММДД, нужно использовать вот такую формулу:

Сначала достаем из А8 4 символа слева, потом номер месяца и справа 2 цифры, обозначающие день.

На этом все. Мы рассмотрели основные примеры, для чего можно использовать функцию ДАТА в Экселе. Про другие функции даты и времени в Excel, можете прочесть, перейдя по ссылке.

[expert_bq id=»1570″]Чтобы получить только время A1-INT A1 , в русской версии Excel А1-ЦЕЛОЕ А1 или MOO A1;1 , в русской версии Excel OCTAT A1;1. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Excel ошибочно предполагает, что 1900 год был високосным годом (Добавим, он был последним годом XIX века, а не первым XX). Это означает, что внутренняя система дат Excel считает, что существовал день 29 февраля 1900 года, тогда как его не было! Самое невероятное — Microsoft сделала это намеренно, по крайней мере, они так утверждают.

Трюк №38. Трюки с возможностями даты и времени в Excel — MS Excel

Если в вашем местоположении используется другой формат даты – например, в Google Листах с прямым порядком байтов (ГГГГ/ММ/ДД) или с прямым порядком байтов (ДД/ММ/ГГГГ) Google Sheets может быть настроен для отображения даты в правильном формате, регулируя региональные настройки.

Трюк №38. Трюки с возможностями даты и времени в Excel

Чтобы узнать числовое значение даты или времени, отформатируйте ячейку, содержащую значение, форматом Общий (General). Например, у даты и времени (в английской версии) 3/July/2002 3:00:00 РМ числовое значение 37440.625, где число после десятичной точки представляет время, а 37440 — последовательное значение для 3 июля 2002 года.

Сложение за пределами 24 часов

Схожий формат можно применять для получения общего количества минут или секунд, Чтобы узнать полное количество минут, когда время равно 24:00, отформатируйте ячейку как [м] ([m]), и вы получите 1440. Чтобы получить общее количество секунд, используйте пользовательский формат [с] ([s]) и вы получите 86400.

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

Если вы хотите использовать фактические значения времени в других вычислениях, помните о следующих «магических» числах: 60 — 60 минут или 60 секунд; 3600 — 60 секунд * 60 минут; 24 — 24 часа; 1440 — 60 минут * 24 часа; 86400 — 24 часа * 60 минут * 60 секунд.

Вооружившись этими магическими числами и предыдущей информацией, намного проще манипулировать временем и датами. Взглянем на следующие примеры и посмотрим, что они обозначают (предполагаем, что время записано в ячейке А1). Если у вас есть число 5.50 и вы хотите получить 5:30 или 5:30 a.m., используйте следующую формулу: =А1/24. Необходимо указать подходящий формат!

Если время должно выглядеть как 17:30 или 5:30 p.m., используйте следующую формулу: =(А1/24)+0.5 .

Чтобы получить противоположное значение, то есть десятичное время из настоящего времени, воспользуйтесь формулой =А1*24 .

Если в ячейке содержится настоящая дата и настоящее время (например, 22/Jan/03 15:36), а вы хотите получить только дату, используйте следующую формулу: =INT(A1) , в русской версии Excel =ЦЕЛОЕ(А1) .

Чтобы получить только время: =A1-INT(A1) , в русской версии Excel =А1-ЦЕЛОЕ(А1) или =MOO(A1;1) , в русской версии Excel =OCTAT(A1;1) . И вновь необходим подходящий формат.

Чтобы найти разность между двумя датами, воспользуйтесь формулой =DATEDIF(A1;A2;»d») , где А1 — это более ранняя дата. Получим количество дней между двумя датами. В качестве результата можно также указать «m» или «у», то есть месяцы или годы. (В действительности функция DATEDIF в Excel 97 не документирована и является функцией Lotus 123.)

Если более ранние дата или время неизвестны, помогут функции МИН (MIN) и МАКС (МАХ). Например, чтобы наверняка получить правильный результат, можно воспользоваться такой функцией: =DATEDIF(MIN(Al;A2); MAX(Al,A2),»d») , в русской версии Excel: =DATEDIF(MИН(Al;A2);MAKC(A1;A2);»d») .

Иначе это ограничение можно обойти двумя способами, гарантировав положительный результат: =MAX(A1;A2)-MIN(A1;A2) , в русской версии Excel =МАКС(А1;А2)-МИН(А1;А2) или =A1-A2+IF(A1>A2,1) , в русской версии Excel =А1-А2+ЕСЛИ(А1>А2;1) .

Можно также приказать Excel прибавить любое количество дней, месяцев или лет к любой дате: =DATE(YEAR(A1)+value1;MONTH(Al)+value2;DAY(Al)+value3) , в русской версии Excel =ДАТА(ГОД(А1)+value1;,МЕСЯЦ(А1)+value2;ДЕНЬ(А1)+value3) .

Чтобы добавить один месяц к дате в ячейке А1, воспользуйтесь формулой =DATE(YEAR(A1);MONTH(A1)+1;DAY(AD) , в русской версии Excel =ДАТА(ГОД(А1);МЕСЯЦ(А1)+1;ДЕНЬ(А1)) .

Настоящие даты и время

Ошибка даты?

Excel ошибочно предполагает, что 1900 год был високосным годом (Добавим, он был последним годом XIX века, а не первым XX). Это означает, что внутренняя система дат Excel считает, что существовал день 29 февраля 1900 года, тогда как его не было! Самое невероятное — Microsoft сделала это намеренно, по крайней мере, они так утверждают.

[expert_bq id=»1570″]Чтобы получить только время A1-INT A1 , в русской версии Excel А1-ЦЕЛОЕ А1 или MOO A1;1 , в русской версии Excel OCTAT A1;1. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Формат даты необходимо поменять на текстовый, т.к. по умолчанию EXCEL дата представляет собой пятизначное число. Делается это при помощи формулы =ТЕКСТ(B3;»ДД.ММ.ГГГГ»). В дальнейшем колонку «Переменные» можно скрыть.

EXCEL: Загружаем курсы валют с сайта ЦБ

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