Какая из формул выводит дату последующего денька. Как отыскать и выделить неверное значение и формат даты в Excel

Если ячейки содержат неверный формат значений – это может привести к неверным вычислениям в формулах. К примеру, заместо типа значения «Дата», ячейка содержит тип значения «Текст». При подготовке огромных размеров данных следует выполнить проверку всех типов значений на соответствие определенному формату. К примеру, таблица наполнялась данными из различных источников где в различный метод фиксировалась дата. С таковой таблицей недозволено делать разных вычислений. Зрительно трудно увидеть где в неверном формате введена некорректная дата исходя из убеждений программки Excel.

Поиск формата текста заместо даты в Excel

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

Поиск и выделение цветом ячеек с неверным форматом отображения значений:

Как видно на рисунке все даты в формате текст выделились цветом:

В критериях форматирования мы употребляли ординарную функцию =ЕТЕКСТ(), у которой всего лишь 1 аргумент – ссылка на проверяемую ячейку. Арес ссылки в аргументе функции ЕТЕКСТ должен быть относительным, потому что будет проверятся любая ячейка выделенного спектра. Если текущая проверяемая ячейка содержит текст (а не дату) – это неверное значение Excel. Тогда функция ЕТЕКСТ возвращает значение ИСТИНА и к данной нам ячейке сходу же присваивается новейший формат (зеленоватая заливка). Заглавие функции ЕТЕКСТ следует читать как сокращение от 2-ух слов: Если ТЕКСТ

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

ЕНЕТЕКСТ – если не текст (функция так же дозволяет стремительно отыскать дату в тексте Excel);
ЕЧИСЛО – если число (дозволяет стремительно отыскивать неверный формат чисел в Excel);
ЕОШ – если ошибка;
ЕОШИБКА – если ошибка;
ЕСЛИОШИБКА – если ошибка (это не логическая функция, но ее просто улучшить под данную задачку);
ЕПУСТО – если пусто;
ЕЛОГИЧ – если логическое значение;
ЕНД – если труднодоступное значение (#Н/Д);
ЕНЕЧЁТ – если нечетное значение;
ЕЧЁТ – если четное значение;
ЕССЫЛКА – если ссылка;
ЕФОРМУЛА – если формула.

При желании сможете проверить все функции в действии экспериментальным методом.

Одной из увлекательных функций Microsoft Excel является СЕГОДНЯ . При помощи этого оператора делается ввод в ячейку текущей даты. Но его можно также использовать и с иными формулами в комплексе. Разглядим главные индивидуальности функции СЕГОДНЯ , аспекты ее работы и взаимодействия с иными операторами.

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

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

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

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

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

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

Метод 1: введение функции вручную

Данный оператор не имеет аргумента. Синтаксис его достаточно прост и смотрится последующим образом:

Метод 2: применение Мастера функций

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

Метод 3: изменение формата ячейки

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

Для того, чтоб поглядеть значение формата отдельной ячейки либо области на листе, необходимо выделить подходящий спектр и находясь во вкладке «Основная» посмотреть, какое значение установлено в специальной форме формата на ленте в блоке инструментов «Число» .

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

Интересно почитать: Формула даты в excel сегодня

Не считая того, в окне форматирования также можно поменять представление нынешней даты. По дефлоту установлен формат по шаблону «дд.мм.гггг» . Выделяя разные варианты значений в поле «Тип» , которое размещено в правой части окна форматирования, можно изменять наружный вид отображения даты в ячейке. Опосля конфигураций не запамятовывайте нажимать на клавишу «OK» .

Метод 4: внедрение СЕГОДНЯ в комплексе с иными формулами

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

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

Для внедрения формулы нажимаем на клавишу ENTER .

Сейчас в ячейке при правильной настройке пересчета формул документа повсевременно будет отображаться животрепещущий возраст человека, который был рожден в 1965 году. Аналогичное выражение можно применить и для хоть какого другого года рождения либо для вычисления годовщины действия.

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

Если необходимо повсевременно иметь на виду дату на три денька вспять, то формула будет смотреться так:

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

Подобная операция для показа номера животрепещущего месяца будет смотреться последующим образом:

Функция ДАТА() , британский вариант DATE(), в озвращает целое число, представляющее определенную дату. Формула =ДАТА(2011;02;28) возвратит число 40602. Если до ввода данной нам формулы формат ячейки был задан как Общий, то итог будет отформатирован как дата, т.е. 28.02.2011.

Синтаксис функции

ДАТА (год ;месяц ;денек )

Год — аргумент, который может иметь от одной до 4 цифр.
Месяц — положительное либо отрицательное целое число в спектре от 1 (январь) до 12 (декабрь), представляющее месяц года.
Денек — положительное либо отрицательное целое число в спектре от 1 до 31, представляющее денек месяца.

В EXCEL даты хранятся в виде последовательности чисел (1, 2, 3, . ), что дозволяет делать над ними вычисления. По дефлоту денек 1 января 1900 г. имеет номер 1, а 28 февраля 2011 г. — номер 40602, потому что интервал меж этими датами составляет 40 602 денька. О том как EXCEL хранит дату и время, читайте эту .

Примеры

Для того, чтоб прибавить к дате 28.02.2011, содержащейся в ячейке А1 , к примеру, 5 лет, можно применять последующую формулу:
=ДАТА(ГОД(A1)+5;МЕСЯЦ(A1);ДЕНЬ(A1))
получим итог 28.02.2016

Для того, чтоб прибавить к дате 28.02.2011, к примеру, 15 месяцев, можно применять последующую формулу:
=ДАТА(ГОД(A1);МЕСЯЦ(A1)+15;ДЕНЬ(A1)) либо формулу =ДАТАМЕС(A1;15)
получим итог 28.05.2012

Примечание . При прибавлении месяцев меж ДАТАМЕС() и ДАТА() существует разница. Прибавим к 30.01.2009 один месяц:

=ДАТАМЕС(«30.01.2009»;1) возвратит 28.02.2009, т.к. 30 февраля не существует, то функция возвратит крайний денек месяца, т.е. 28.02.2009 («излишние» 2 денька будут отброшены);
=ДАТА(ГОД(«30.01.2009»);МЕСЯЦ(«30.01.2009»)+1;ДЕНЬ(«30.01.2009»)) возвратит 02.03.2009: «излишние» 2 денька (29 и 30 февраля) будут прибавлены к дате.

Если записать формулу =ДАТА(2008;3;) , то формула возвратит 29.02.2008. Т.е. опуская крайний аргумент Денек мы тем задаем его равным 0. Потому формула возвращает

Excel works!

Количество рабочих дней меж датами в Excel

Еще одна увлекательная функция из серии дата-время — это ЧИСТРАБДНИ(), с ее помощью может быть посчитать Количество рабочих дней меж датами. Но есть и почти всем неведомая сторона данной нам функции: можно считать количество дней с учетом праздничков. Даты праздничков можно задать вручную. Это весьма комфортно. А так же разглядим функцию РАБДЕНЬ(), она поможет отсчитать кол-во рабочих дней от исходной даты. Подробнее о их снутри статьи.

Как считать количество рабочих дней меж датами?

Чтоб подсчитать количество дней меж 2-мя датами, можно отнять из большей даты наименьшую, ведь даты и время в Excel — это просто числа в подходящем формате. Чтоб подсчитать это количество рабочих дней, существует особая очень нужная функция =ЧИСТРАБДНИ(). Как ей воспользоваться?

Функция ЧИСТРАБДНИ. Реквизиты и пример

Начнем, как постоянно, с состава функции и ее реквизитов.

Состав реквизитов можно узреть на исходной картинке

нач_дата — дата, с которой начинаем расчет.

кон_дата — дата, до которой необходимо произвести расчет.

празднички — необязательный реквизит. Перечень дат, который нужно исключить из рабочих дней календаря. Реквизит может состоять из спектра дат либо массива. Как воспользоваться можно поглядеть ниже.

Интересно почитать: Ошибка в формуле в excel

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

Функция РАБДЕНЬ. Реквизиты и пример

Нач_дата — дата, с которой начинаем расчет.

число_дней — количество дней, которые должны пройти с исходной даты

празднички — таковой же реквизит как и ЧИСТРАБДНИ

Учет праздничков — сокрытая супер возможность данной нам формулы, весьма советуем ее применять:

Как считать рабочие деньки с учетом праздничков?

Для обоих функций, как вы увидели, есть реквизит ‘Празднички’. В нем можно указать, какие даты будут считаться праздничками (соответственно, не беря во внимание выходные). К примеру так:

chistrabdni2

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

Excel дата в формуле

В данной нам статье представлены 7 решений для прибавления либо вычитания дней, недель, месяцев, лет либо композиции лет / месяцев / дней к дате в Excel. Видео Демо-версия

Добавьте либо вычтите деньки до даты при помощи формулы

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

1. Изберите пустую ячейку, в которую вы расположите итог расчета, введите формулу = A2 + 10, и нажмите Enter ключ.
Внимание: Чтоб отнять 10 дней из даты, используйте эту формулу = A2–10.

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

Сейчас вы добавили либо вычли такое же количество дней из этих дат массово. Смотрите снимок экрана:

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

Трудно и мучительно запоминать длинноватые сложные формулы в Excel? Kutools для Excel Формула Ассистент перечисляет более нередко применяемые формулы, которые посодействуют для вас стремительно вычислить и решить трудности в Excel, гласит Добавить годы / месяцы / недельки / деньки к дате, Добавить часы / минутки / секунды к дате, Сумма абсолютных значений, Найдите более распространенное значение, И т.д. Полнофункциональная бесплатная 30-дневная пробная версия!

Kutools for Excel — Включает наиболее 300 комфортных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now

Добавить либо отнять деньки из нескольких дат без формулы

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

1. Введите количество дней, которое вы добавите либо вычтете к датам, в пустой ячейке, к примеру 10, а потом скопируйте его. Смотрите снимок экрана:

2. Изберите даты, которые вы добавите либо вычтете, щелкните правой клавишей мыши и изберите Особая вставка > Особая вставка в контекстном меню. Смотрите снимок экрана:

3. В диалоговом окне «Особая вставка» установите флаг Добавить or вычитать нужные для вас характеристики и щелкните OK клавиша. Смотрите снимок экрана:

4. Сейчас число добавляется либо вычитается к датам, и даты показываются в виде 5-значных чисел. Не снимая выделения с этих 5-значных чисел, нажмите Основная > Формат номера коробка> Куцее свидание чтоб опять конвертировать их в даты.

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

Приближается сезон увольнений, как и раньше медлительно работать?
Office Tab увеличивает темп вашей работы!

Увеличение эффективности работы на 80% за счет одновременного просмотра и редактирования нескольких документов
Уменьшите количество кликов на тыщи любой денек, попрощайтесь с рукою мыши
Калоритные вкладки делают мучительную работу веселее, делают живое и колоритное рабочее место.
Выбор из 90,000 300+ высокоэффективных людей и XNUMX+ узнаваемых компаний

Помогите для вас работать резвее и просто выделиться из толпы! 1 секунда для переключения меж десятками открытых документов! Читать дальше Бесплатная пробная версия

Добавить либо отнять недельки до даты в Excel

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

1. Изберите пустую ячейку, в которую вы расположите итог расчета, введите формулу = A2 + 4 * 7, и нажмите Enter ключ.
Внимание: Чтоб отнять 4 недельки из даты, используйте эту формулу = A2-4 * 7.

Kutools for Excel

Выделяться из толпы

Попрощайтесь с страшными VBA и формулами!

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

Интересно почитать: Как поставить плюс в excel без формулы

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

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

Для прибавления либо вычитания месяцев из дат недозволено добавлять либо вычитать 30 из дат, поэтому что месяцы содержат различное количество дней в году, некие содержат 30 дней, некие содержат 31 денек, а некие содержат 28 либо 29 дней. Как следует, нам необходимо применить функцию EDATE, чтоб совладать с данной нам ситуацией.

1. Изберите пустую ячейку, в которую вы расположите итог расчета, введите формулу = ДАТА (A2,3), и перетащите маркер автозаполнения данной нам ячейки формулы, чтоб применить эту формулу к остальным ячейкам при необходимости.
Внимание: Чтоб отнять 3 месяца из даты, используйте эту формулу = ДАТА (A2; -3).

2. Видите ли, функция EDATE возвращает 5-значные числа. Оставьте избранными эти 5-значные числа и нажмите Основная > Формат номера коробка> Куцее свидание чтоб конвертировать их в даты вспять. Смотрите снимок экрана:

Сейчас вы увидите, что к датам было добавлено либо вычтено такое же количество месяцев. Смотрите снимок экрана:

Добавить либо отнять годы на текущий момент в Excel

К примеру, вы добавите 6 лет к пакету дат в Excel, вы сможете создать последующее:

1. Изберите пустую ячейку, в которую вы расположите итог расчета, введите формулу = ДАТА (ГОД (A2) + 6; МЕСЯЦ (A2), ДЕНЬ (A2)), и нажмите Enter ключ.
Внимание: Чтоб отнять 6 лет из даты, примените эту формулу = ДАТА (ГОД (A2) -6; МЕСЯЦ (A2), ДЕНЬ (A2)).

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

Добавьте либо вычтите комбинацию лет, месяца и дней до даты в Excel

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

1. Изберите пустую ячейку, в которую вы расположите итог расчета, введите формулу =DATE(YEAR(A2)+3,MONTH(A2)+5,DAY(A2)+15), и нажмите Enter ключ. Смотрите снимок экрана:
Внимание: Чтоб отнять сумму лет, месяцев и дней совместно, используйте эту формулу =DATE(YEAR(A2)-3,MONTH(A2)-5,DAY(A2)-15).

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

Добавьте либо вычтите деньки, недельки, месяцы либо годы на нынешний денек при помощи Kutools for Excel

Если у вас установлен Kutools for Excel, вы отыщите его Формула Ассистент перечисляет более нередко применяемые формулы, которые посодействуют для вас просто создавать вычисления в Excel, включая добавление дней, недель, месяцев либо лет к дате. Пожалуйста, сделайте последующее:

1. Изберите ячейку, в которую вы расположите итог расчета, и нажмите Kutools > Формула Ассистент > Добавить годы к дате.
Чаевые: Изберите пригодную формулу из Формула Ассистент раскрывающийся перечень в зависимости от ваших потребностей. К примеру, изберите Добавить недельки к дате для прибавления / вычитания обозначенных недель к дате.

2. В открывшемся диалоговом окне Ассистент по формулам укажите адресок ячейки даты в поле Дата Время поле и введите количество лет, которое вы добавите в поле Число пт.
Чаевые: В нашем случае мы добавим 5 лет с даты, потому набираем 5 в Число коробка. Если для вас необходимо отнять пару лет, к примеру 3 года, введите -3 в Число пт.

3, Нажмите Ok клавишу, чтоб применить формулу Добавить годы к дате. По мере необходимости перетащите маркер наполнения, чтоб применить эту формулу к другому спектру.

Kutools для Excel Формула Ассистент освободит юзеров Excel от запоминания сложных формул и перечислит более нередко применяемые формулы, чтоб посодействовать для вас стремительно вычислять и решать трудности в Excel, гласит Добавить годы / месяцы / недельки / деньки к дате, Добавить часы / минутки / секунды к дате, И т.д. Получите бесплатную пробную версию!

Демонстрация: добавление либо вычитание дней, недель, месяцев либо лет на текущий момент в Excel

300 инструментов посодействуют для вас выделиться из толпы уже на данный момент

с Kutools для Excel — никогда не волнуйся о сокращении работы

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