Как в Excel вставить текущую дату и время, как найти количество дней между датами (в т.ч. рабочих)
Доброго дня!
В некоторых случаях при работе с документами Excel очень нужно, чтобы проставлялась текущая дата: например, вы часто делаете отчеты по одной и той же форме (глаз со временем «замыливается» и их можно перепутать) ; или, скажем, у вас есть десяток-другой задач и вы каждый день по ним просматриваете как продвигается дело, и сколько дней осталось до завершения.
Для решения всех подобных «загвоздок» в Excel есть несколько специальных формул. Собственно, о них и будет сегодняшняя заметка.
Примечание: скриншоты в статье приведены из Excel 2019 (материал актуален также для версии 2016, 2013).
Если с вашими документами иногда работают посторонние люди, рекомендую некоторые ячейки защищать от изменения — https://ocomp.info/zashhita-yacheek-v-excel.html
Работа с датами в Excel
ШАГ 1: вставка текущей даты
ШАГ 2: сколько всего дней между двумя датами
Это еще один популярный вопрос.
Ко-во дней посчитано
Кстати, если у вас ячейки имеют формат дат — то можно поступить даже проще: просто вычесть из одной даты другую (как если бы это были числа). Причем, если у вас в одной из ячеек будет указано время (как в моем примере) — Excel посчитает вплоть до десятых.
ШАГ 3: определение кол-ва рабочих дней между датами
Более интересный вариант, когда нам нужно не просто определить кол-во дней, но и узнать сколько из них выходных, а сколько рабочих.
Кстати, если вам нужно подсветить ячейки красно-зеленым цветом (чтобы сразу же видеть те строки, где, например, горят сроки — рекомендую использовать инструмент «Условное форматирование» в разделе «Главная» ). Пример ниже.
Правила выделения ячеек
Примечание : для обновления текущей даты и времени — нажмите сочетание кнопок Ctrl+R.
Функции даты и времени (справка)
Чтобы просмотреть более подробные сведения о функции, щелкните ее название в первом столбце.
Примечание: Маркер версии обозначает версию Excel, в которой она впервые появилась. В более ранних версиях эта функция отсутствует. Например, маркер версии 2013 означает, что данная функция доступна в выпуске Excel 2013 и всех последующих версиях.
Возвращает заданную дату в числовом формате.
Вычисляет количество дней, месяцев или лет между двумя датами. Эта функция полезна в формулах расчета возраста.
Преобразует дату из текстового формата в числовой.
Преобразует дату в числовом формате в день месяца.
ДНИ
Возвращает количество дней между двумя датами.
Вычисляет количество дней между двумя датами на основе 360-дневного года.
Возвращает дату в числовом формате, отстоящую на заданное число месяцев вперед или назад от начальной даты.
Возвращает дату в числовом формате для последнего дня месяца, отстоящего вперед или назад на заданное число месяцев.
Преобразует дату в числовом формате в часы.
НОМНЕДЕЛИ.ISO
Возвращает номер недели по ISO для заданной даты.
Преобразует дату в числовом формате в минуты.
Преобразует дату в числовом формате в месяцы.
Возвращает количество полных рабочих дней между двумя датами.
ЧИСТРАБДНИ.МЕЖД
Возвращает количество полных рабочих дней в интервале между двумя датами, руководствуясь параметрами, указывающими выходные дни и их количество.
Возвращает текущую дату и время в числовом формате.
Преобразует дату в числовом формате в секунды.
Возвращает заданное время в числовом формате.
Преобразует время из текстового формата в числовой.
Возвращает текущую дату в числовом формате.
Преобразует дату в числовом формате в день недели.
Преобразует дату в числовом формате в число, которое указывает, на какую неделю года приходится дата.
Возвращает дату в числовом формате, отстоящую вперед или назад на заданное количество рабочих дней.
РАБДЕНЬ.МЕЖД
Возвращает числовое значение даты, предшествующей заданному количеству рабочих дней или следующей за ними, руководствуясь при этом параметрами, указывающими выходные дни и их количество.
Преобразует дату в числовом формате в год.
Возвращает долю года, которую составляет количество дней между начальной и конечной датами.
Важно: Вычисляемые результаты формул и некоторые функции листа Excel могут несколько отличаться на компьютерах под управлением Windows с архитектурой x86 или x86-64 и компьютерах под управлением Windows RT с архитектурой ARM. Подробнее об этих различиях.
Как быстро вставить сегодняшнюю дату в Excel?
Это руководство показывает различные способы ввода дат в Excel. Узнайте, как вставить сегодняшнюю дату и время в виде статической метки времени или динамических значений, как автоматически заполнять столбец или строку днями недели.
Есть несколько способов добавить даты в Excel в зависимости от того, что именно вы хотите сделать. Например, хотите ли вы ввести сегодняшнее число в какой-либо отчет или счет? Или вы хотите вставить дату, которая будет обновляться и всегда отображать сегодняшний день и время? Или, возможно, вы хотите автоматически заполнять рабочие дни или вводить случайные дни в свой рабочий лист?
Сейчас вы изучите все эти и еще несколько приемов.
Как ввести дату в Excel?
Вы можете записать ее в ячейку различными способами, например, 01.01.2020 или 1 январь 2020. Когда вы пишете что-то подобное в ячейке, программа понимает, что именно вы хотите сделать, и Microsoft Excel автоматически применяет формат даты к этой ячейке. Довольно часто программа форматирует вновь вставленное значение в соответствии с настройками по умолчанию в Windows, но иногда он может оставить все точно в таком же виде, как вы ввели.
Наиболее очевидным внешним признаком того, что Эксель распознал введенную вами дату, является ее выравнивание по правому краю, а не по левому, как это происходит с текстовыми значениями.
Если программе не удалось правильно распознать введенные вами данные, и вы видите их смещёнными влево, попробуйте записать в каком-либо другом виде, близком к формату коротких или длинных дат по умолчанию. Они отмечены звездочкой (*) в диалоговом окне Формат ячейки, и вы можете быстро получить к ним доступ на ленте (вкладка Главная > Число ).
При необходимости вы сможете легко изменить способ представления с помощью диалогового окна «Формат ячеек», которое открывается по сочетанию клавиш Ctrl + 1.
Примечание. Если введенное вами отображается в виде решёток (########), то скорее всего ячейка недостаточно широка, чтобы показать все целиком. Чтобы это исправить, дважды щелкните правую границу столбца, чтобы автоматически подогнать его ширину, или перетащите правую границу, чтобы установить нужную ширину. Но иногда такая картина бывает следствием ошибки.
Вставка текущей даты и времени.
В Microsoft Excel вы можете сделать это в виде статического или динамического значения.
Как вставить сегодняшнюю дату как статическую отметку.
Для начала давайте определим, что такое отметка времени. Отметка времени фиксирует «статическую точку», которая не изменится с течением времени или при пересчете электронной таблицы. Она навсегда зафиксирует тот момент, когда ее записали.
Скажу прямо, не все бывает гладко с этими быстрыми клавишами. Но по моим наблюдениям, если при загрузке файла у вас на клавиатуре был включен английский, то срабатывают комбинации клавиш на английском – какой бы язык бы потом не переключили для работы. То же самое – с русским.
Как сделать, чтобы дата оставалась актуальной?
Если вы хотите вставить текущую дату, которая всегда будет оставаться актуальной, используйте одну из следующих функций:
В отличие от нажатия специальных клавиш, функции ТДАТА и СЕГОДНЯ всегда возвращают актуальные данные.
А если нужно вставить текущее время?
Здесь рекомендации зависят от того, что вы далее собираетесь с этим делать. Если нужно просто показать время в таблице, то достаточно функции ТДАТА() и затем установить для этой ячейки формат «Время».
Если же далее на основе этого вы планируете производить какие-то вычисления, то тогда, возможно, вам будет лучше использовать формулу
В результате количество дней будет равно нулю, останется только время. Ну и формат времени все равно нужно применить.
При использовании формул имейте в виду, что:
Как поставить неизменную отметку времени автоматически формулами?
Допустим, у вас есть список товаров в столбце A, и, как только один из них будет отправлен заказчику, вы вводите «Да» в колонке «Доставка», то есть в столбце B. Как только «Да» появится там, вы хотите автоматически зафиксировать в колонке С время, когда это произошло. И менять его уже не нужно.
Для этого мы попробуем использовать вложенную функцию ИЛИ с циклическими ссылками во второй ее части:
Если вместо проверки какого-либо конкретного слова вы хотите, чтобы временная метка появлялась, когда вы хоть что-нибудь пишете в указанную ячейку (это может быть любое число, текст или дата), то немного изменим первую функцию ЕСЛИ для проверки непустой ячейки:
Примечание. Чтобы эта формула работала, вы должны разрешить циклические вычисления на своем рабочем листе (вкладка Файл – параметры – Формулы – Включить интерактивные вычисления). Также имейте в виду, что в основном не рекомендуется делать так, чтобы ячейка ссылалась сама на себя, то есть создавать циклические ссылки. И если вы решите использовать это решение в своих таблицах, то это на ваш страх и риск.
Как автоматически заполнять даты в Excel.
Если вам нужно заполнить какую-то часть вашей таблицы, то вы можете разрешить Эксель «автоматически датировать» столбец или строку, используя функцию автозаполнения.
Автозаполнение списка дат, которые увеличиваются на один день.
Чтобы заполнить столбец или строку возрастающим списком дат, каждая следующая из которых больше предыдущей на 1 день, вы используете функцию автозаполнения самым обычным способом:
Автозаполнение будних дней, месяцев или лет
Существует два способа быстрого заполнения выбранного диапазона днями недели, месяцами или годами.
Автоматическая вставка каждого 2-го, 3-го или N-го дня.
Как поставить сегодняшнюю дату на колонтитул?
Добавление различной информации на колонтитул – весьма полезная штука. Туда вы можете записать имя исполнителя, день составления документа, чтобы зафиксировать момент, когда использованные данные были актуальными.
Для этого вызовите меню колонтитулов из меню «Вставка». А затем вставьте те элементы, которые вам необходимы. Можете, кстати, и просто написать что-либо (фамилию, например). Все это будет распечатано на всех страницах вашего документа.
Данные при этом останутся статичными.
Я надеюсь, что эти рекомендации оказались полезными для вас. В любом случае, спасибо за чтение!
Формат даты в таблице Excel
Создание пользовательских форматов
Excel позволяет создать свой (пользовательский) формат ячейки. Многие знают об этом, но очень редко пользуются из-за кажущейся сложности. Однако это достаточно просто, главное понять основной принцип задания формата.
Для того, чтобы создать пользовательский формат необходимо открыть диалоговое окно Формат ячеек и перейти на вкладку Число. Можно также воспользоваться сочетанием клавиш Ctrl + 1.
В поле Тип вводится пользовательские форматы, варианты написания которых мы рассмотрим далее.
В поле Тип вы можете задать формат значения ячейки следующей строкой:
[цвет]”любой текст”КодФормата”любой текст”
Посмотрите простые примеры использования форматирования. В столбце А – значение без форматирования, в столбце B – с использованием пользовательского формата (применяемый формат в столбце С)
Какие цвета можно применять
В квадратных скобках можно указывать один из 8 цветов на выбор:
Синий, зеленый, красный, фиолетовый, желтый, белый, черный и голубой.
Числовые форматы
| Символ | Описание применения | Пример формата | До форматирования | После форматирования |
|---|---|---|---|---|
| # | Символ числа. Незначащие нули в начале или конце число не отображаются | ###### | 001234 | 1234 |
| 0 | Символ числа. Обязательное отображение незначащих нулей | 000000 | 1234 | 001234 |
| , | Используется в качестве разделителя целой и дробной части | ####,# | 1234,12 | 1234,1 |
| пробел | Используется в качестве разделителя разрядов | # ###,#0 | 1234,1 | 1 234,10 |
Форматы даты
| Формат | Описание применения | Пример отображения |
|---|---|---|
| М | Отображает числовое значение месяца | от 1 до 12 |
| ММ | Отображает числовое значение месяца в формате 00 | от 01 до 12 |
| МММ | Отображает сокращенное до 3-х букв значение месяца | от Янв до Дек |
| ММММ | Полное наименование месяца | Январь – Декабрь |
| МММММ | Отображает первую букву месяца | от Я до Д |
| Д | Выводит число даты | от 1 до 31 |
| ДД | Выводит число в формате 00 | от 01 до 31 |
| ДДД | Выводит день недели | от Пн до Вс |
| ДДДД | Выводит название недели целиком | Понедельник – Пятница |
| ГГ | Выводит последние 2 цифры года | от 00 до 99 |
| ГГГГ | Выводит год даты полностью | 1900 – 9999 |
Стоит обратить внимание, что форматы даты можно комбинировать между собой. Например, формат “ДД.ММ.ГГГГ” отформатирует дату в привычный нам вид 31.12.2017, а формат “ДД МММ” преобразует дату в вид 31 Дек.
Как вводить даты и время в Excel
Если иметь ввиду российские региональные настройки, то Excel позволяет вводить дату очень разными способами – и понимает их все:
С использованием дефисов
С использованием дроби
Внешний вид (отображение) даты в ячейке может быть очень разным (с годом или без, месяц числом или словом и т.д.) и задается через контекстное меню – правой кнопкой мыши по ячейке и далее Формат ячеек (Format Cells) :
Время вводится в ячейки с использованием двоеточия. Например
По желанию можно дополнительно уточнить количество секунд – вводя их также через двоеточие:
И, наконец, никто не запрещает указывать дату и время сразу вместе через пробел, то есть
27.10.2012 16:45
Быстрый ввод дат и времени
Для ввода сегодняшней даты в текущую ячейку можно воспользоваться сочетанием клавиш Ctrl + Ж (или CTRL+SHIFT+4 если у вас другой системный язык по умолчанию).
Если скопировать ячейку с датой (протянуть за правый нижний угол ячейки), удерживая правуюкнопку мыши, то можно выбрать – как именно копировать выделенную дату:
Если Вам часто приходится вводить различные даты в ячейки листа, то гораздо удобнее это делать с помощью всплывающего календаря :
Если нужно, чтобы в ячейке всегда была актуальная сегодняшняя дата – лучше воспользоваться функцией СЕГОДНЯ (TODAY) :
Как Excel на самом деле хранит и обрабатывает даты и время
Если выделить ячейку с датой и установить для нее Общий формат (правой кнопкой по ячейке Формат ячеек – вкладка Число – Общий), то можно увидеть интересную картинку:
То есть, с точки зрения Excel, 27.10.2012 15:42 = 41209,65417
На самом деле любую дату Excel хранит и обрабатывает именно так – как число с целой и дробной частью. Целая часть числа (41209) – это количество дней, прошедших с 1 января 1900 года (взято за точку отсчета) до текущей даты. А дробная часть (0,65417), соответственно, доля от суток (1сутки = 1,0)
Из всех этих фактов следуют два чисто практических вывода:
Количество дней между двумя датами
Считается простым вычитанием – из конечной даты вычитаем начальную и переводим результат в Общий (General) числовой формат, чтобы показать разницу в днях:
Количество рабочих дней между двумя датами
Здесь ситуация чуть сложнее. Необходимо не учитывать субботы с воскресеньями и праздники. Для такого расчета лучше воспользоваться функцией ЧИСТРАБДНИ (NETWORKDAYS) из категории Дата и время. В качестве аргументов этой функции необходимо указать начальную и конечную даты и ячейки с датами выходных (государственных праздников, больничных дней, отпусков, отгулов и т.д.):
Функция ГОД в Excel
Возвращает год как целое число (от 1900 до 9999), который соответствует заданной дате. В структуре функции только один аргумент – дата в числовом формате. Аргумент должен быть введен посредством функции ДАТА или представлять результат вычисления других формул.
Пример использования функции ГОД:
Функция МЕСЯЦ в Excel: пример
Возвращает месяц как целое число (от 1 до 12) для заданной в числовом формате даты. Аргумент – дата месяца, который необходимо отобразить, в числовом формате. Даты в текстовом формате функция обрабатывает неправильно.
Примеры использования функции МЕСЯЦ:
ДЕНЬНЕД
Задача оператора ДЕНЬНЕД – выводить в указанную ячейку значение дня недели для заданной даты. Но формула выводит не текстовое название дня, а его порядковый номер. Причем точка отсчета первого дня недели задается в поле «Тип». Так, если задать в этом поле значение «1», то первым днем недели будет считаться воскресенье, если «2» — понедельник и т.д. Но это не обязательный аргумент, в случае, если поле не заполнено, то считается, что отсчет идет от воскресенья. Вторым аргументом является собственно дата в числовом формате, порядковый номер дня которой нужно установить. Синтаксис выглядит так:
“ВЫБОР”.
Теперь предположим, что вы хотите получить произвольное название месяца или имя на другом языке вместо числа или обычного имени.
В этой ситуации вам поможет функция ВЫБОР и уже пройденная нами функция МЕСЯЦ. Построим формулу. Для этого нам необходимо указать пользовательское имя для всех 12 месяцев в функции и использовать функцию месяца, чтобы получить номер месяца из даты.
Таким образом, когда функция месяца возвращает номер месяца от даты, функция выбора будет возвращать произвольное имя месяца вместо этого числа.
НОМНЕДЕЛИ
Предназначением оператора НОМНЕДЕЛИ является указание в заданной ячейке номера недели по вводной дате. Аргументами является собственно дата и тип возвращаемого значения. Если с первым аргументом все понятно, то второй требует дополнительного пояснения. Дело в том, что во многих странах Европы по стандартам ISO 8601 первой неделей года считается та неделя, на которую приходится первый четверг. Если вы хотите применить данную систему отсчета, то в поле типа нужно поставить цифру «2». Если же вам более по душе привычная система отсчета, где первой неделей года считается та, на которую приходится 1 января, то нужно поставить цифру «1» либо оставить поле незаполненным. Синтаксис у функции такой:
Формула условия для дат с функцией ДАТАЗНАЧ (DATEVALUE)
Иногда случается, что записать дату непосредственно в функцию ЕСЛИ, не ссылаясь ни на какую ячейку. В этом случае возникают некоторые сложности.
В отличие от многих других функций Excel, ЕСЛИ не может распознавать даты и интерпретирует их как текст, как простые текстовые строки.
Поэтому вы не можете выразить свое логическое условие просто как >«15.07.2019» или же >15.07.2019. Увы, ни один из приведенных вариантов не верен.
Чтобы функция ЕСЛИ распознала дату в вашем логическом условии именно как дату, вы должны обернуть ее в функцию ДАТАЗНАЧ (в английском варианте – DATEVALUE).
Полная формула ЕСЛИ может иметь следующую форму:
=ЕСЛИ(B2 
Как показано на скриншоте, эта формула ЕСЛИ оценивает даты в столбце В и возвращает «Послупил», если дата поступления до 10 сентября. В противном случае формула возвращает «Ожидается».
Расширенные формулы ЕСЛИ для будущих и прошлых дат
Предположим, вы хотите отметить только те даты, которые отстоят от текущей более чем на 30 дней.
Выделим даты, отстоящие более чем на месяц от текущей, в прошлом. Укажем для них «Более месяца назад». Запишем это условие:
=ЕСЛИ(СЕГОДНЯ()-B2>30,”Более месяца назад”,””)
Если условие не выполнено, то в ячейку запишем пустую строку “”.
А для будущих дат, также отстоящих более чем на месяц, укажем «Ожидается».
Если все результаты попробовать объединить в одном столбце, то придется составить выражение с несколькими вложенными функциями ЕСЛИ:
=ЕСЛИ(СЕГОДНЯ()-B2>30,”Более месяца назад”, ЕСЛИ(B2-СЕГОДНЯ()>30,”Ожидается”,””))
Перевод разных написаний дат
Разные системы в выгрузках выдают даты по-разному, например: 12.07.2016 12-07-16 16-07-12 и так далее. Иногда месяца пишут текстом. Для того, чтобы привести даты к одному формату мы используем функцию ДАТА:
Данный вариант подходит, когда количество символов в дате одинаковое. Если вы работаете с однотипными выгрузками еженедельно, создание дополнительного столбца и протягивание формулы будет простым решением.
Получение значения даты
С помощью формулы ЗНАЧ мы выводим текстовое значение даты, потом его форматируем как Дату:
Умножение текстового значения на единицу
Принцип аналогичный предыдущему, только мы текстовое значение умножаем на единицу и после этого форматируем как дату:
Примечание: если дата определилась как текст, то вы не сможете делать группировки. При этом дата будет выровнена по левому краю. Excel выравнивает числа и даты по правому краю.
Синтаксис
=DATE(year, month, day) – английская версия
=ДАТА(год; месяц; день) – русская версия
Аргументы
Вставка текущей даты и времени.
В Microsoft Excel вы можете сделать это в виде статического или динамического значения.
Как вставить сегодняшнюю дату как статическую отметку.
Для начала давайте определим, что такое отметка времени. Отметка времени фиксирует «статическую точку», которая не изменится с течением времени или при пересчете электронной таблицы. Она навсегда зафиксирует тот момент, когда ее записали.
Таким образом, если ваша цель – поставить текущую дату и/или время в качестве статического значения, которое никогда не будет автоматически обновляться, вы можете использовать одно из следующих сочетаний клавиш:
Скажу прямо, не все бывает гладко с этими быстрыми клавишами. Но по моим наблюдениям, если при загрузке файла у вас на клавиатуре был включен английский, то срабатывают комбинации клавиш на английском – какой бы язык бы потом не переключили для работы. То же самое – с русским.
Как сделать, чтобы дата оставалась актуальной?
Если вы хотите вставить текущую дату, которая всегда будет оставаться актуальной, используйте одну из следующих функций:
В отличие от нажатия специальных клавиш, функции ТДАТА и СЕГОДНЯ всегда возвращают актуальные данные.
А если нужно вставить текущее время?
Здесь рекомендации зависят от того, что вы далее собираетесь с этим делать. Если нужно просто показать время в таблице, то достаточно функции ТДАТА() и затем установить для этой ячейки формат «Время».
Если же далее на основе этого вы планируете производить какие-то вычисления, то тогда, возможно, вам будет лучше использовать формулу
В результате количество дней будет равно нулю, останется только время. Ну и формат времени все равно нужно применить.
При использовании формул имейте в виду, что:
Как поставить неизменную отметку времени автоматически формулами?
Допустим, у вас есть список товаров в столбце A, и, как только один из них будет отправлен заказчику, вы вводите «Да» в колонке «Доставка», то есть в столбце B. Как только «Да» появится там, вы хотите автоматически зафиксировать в колонке С время, когда это произошло. И менять его уже не нужно.
Для этого мы попробуем использовать вложенную функцию ИЛИ с циклическими ссылками во второй ее части:
Где B – это колонка подтверждения доставки, а C2 – это ячейка, в которую вы вводите формулу и где в конечном итоге появится статичная отметка времени.
В приведенной выше формуле первая функция ЕСЛИ проверяет B2 на наличие слова «Да» (или любого другого текста, который вы решите ввести). И если указанный текст присутствует, она запускает вторую функцию ЕСЛИ. В противном случае возвращает пустое значение. Вторая ЕСЛИ – это циклическая формула, которая заставляет функцию ТДАТА() возвращать сегодняшний день и время, только если в C2 еще ничего не записано. А если там уже что-то есть, то ничего не изменится, сохранив таким образом все существующие метки.
Если вместо проверки какого-либо конкретного слова вы хотите, чтобы временная метка появлялась, когда вы хоть что-нибудь пишете в указанную ячейку (это может быть любое число, текст или дата), то немного изменим первую функцию ЕСЛИ для проверки непустой ячейки:
Примечание. Чтобы эта формула работала, вы должны разрешить циклические вычисления на своем рабочем листе (вкладка Файл – параметры – Формулы – Включить интерактивные вычисления). Также имейте в виду, что в основном не рекомендуется делать так, чтобы ячейка ссылалась сама на себя, то есть создавать циклические ссылки. И если вы решите использовать это решение в своих таблицах, то это на ваш страх и риск.
Складывать и вычитать календарные дни
Excel позволяет добавлять к дате и вычитать из нее нужное количество дней. Никаких специальных формул для этого не нужно. Достаточно сложить ячейку, в которую ввели дату, и необходимое число суток.
Например, вам необходимо создать резерв по сомнительным долгам в налоговом учете. В том числе нужно просчитать, когда у покупателя возникнет задолженность со сроком 45 дней после дня реализации. Для этого в одну ячейку внесите дату отгрузки. К примеру, это ячейка D2. Тогда формула будет выглядеть так: =D2+45. Вычитаются дни по аналогичному принципу. Главное, чтобы ячейка с датой, к которой будете прибавлять число, имела правильный формат. Чтобы это проверить, нажмите правой кнопкой мыши на ячейку, выберите «Формат ячеек» и удостоверьтесь, что установлен формат «Дата».
Как выглядит формат ячейки в Excel
Таким же образом можно посчитать и количество дней между двумя датами. Просто вычтите из более поздней даты более раннюю. Результат Excel покажет в виде числа, поэтому ячейку с итогом переведите в общий формат: вместо «Дата» выберите «Общий».
К примеру, необходимо посчитать, сколько календарных дней пройдет с 05.11.2019 по 31.12.2019. Для этого введите эти даты в разные ячейки, а в отдельной ячейке поставьте знак «=». Затем вычтите из декабрьской даты ноябрьскую. Получится 56 дней. Помните, что в этом случае в подсчет войдет последний день, но не войдет первый. Если вам необходимо, чтобы итог включал оба дня, прибавьте к формуле единицу. Если же, наоборот, нужно посчитать количество дней без учета обеих дат, то единицу необходимо вычесть.
Добавить к дате рабочие дни
Функция РАБДЕНЬ позволяет точно посчитать дату через нужное количество рабочих дней. Эта функция состоит из трех элементов:
Например, директор дал вам поручение, которое необходимо выполнить за 25 рабочих дней. Допустим, сегодня вторник, 5 ноября 2019 года. Эту дату вносим в ячейку A1. Функция =РАБДЕНЬ(A1;25) определит крайний день, когда вы должны его выполнить, — 10 декабря 2019 года. При этом не забудьте поставить в ячейке с результатом формат «Дата».
Помните, что функция РАБДЕНЬ автоматически убирает из подсчетов только субботы и воскресенья. О праздниках Excel не знает. Их нужно заносить в функцию вручную. Чтобы вы не запутались, мы подготовили файл, в который уже внесли все праздники 2020 года. Ищите его в электронной версии статьи.
Как прибавить (вычесть) несколько недель к дате
Когда требуется прибавить (вычесть) несколько недель к определенной дате, Вы можете воспользоваться теми же формулами, что и раньше. Просто нужно умножить количество недель на 7:
Например, чтобы прибавить 3 недели к дате в ячейке А2, используйте следующую формулу:
Чтобы вычесть 2 недели из сегодняшней даты, используйте эту формулу:
Как прибавить (отнять) годы к дате в Excel
Добавление лет к датам в Excel осуществляется так же, как добавление месяцев. Вам необходимо снова использовать функцию ДАТА (DATE), но на этот раз нужно указать количество лет, которые Вы хотите добавить:
На листе Excel, формулы могут выглядеть следующим образом:
Чтобы получить универсальную формулу, Вы можете ввести количество лет в ячейку, а затем в формуле обратиться к этой ячейке. Положительное число позволит прибавить годы к дате, а отрицательное – вычесть.
Как отличить обычные даты Excel от «текстовых дат»
Импортированные данные (или данные, введенные неправильно) могут выглядеть как обычные даты Excel, но они не ведут себя так, как выглядят. Microsoft Excel обрабатывает такие записи как текст. Поэтому вы не сможете правильно отсортировать таблицу в хронологическом порядке, а также использовать эти «неправильные даты» в формулах, сводных таблицах, диаграммах или любом другом инструменте Excel, который работает с временем.
Сначала давайте изучим несколько признаков, которые могут помочь определить, записана в ячейке датировка либо текст.
Даты
Текстовые значения
· Выровнено по правому краю.
· По левому краю по умолчанию.
· Общий формат отображается в поле «Число» на вкладке «Главная» — «Число».
·В строке формул может быть виден апостроф перед содержимым ячейки.
Их можно легко распознать, немного расширив столбцы, выделив один из них, выбрав команду Формат ► Ячейки ► Выравнивание (Format ► Cells ► Alignment) и для параметра По горизонтали (Horizontal) выбрав значение Общий (General) (это вид ячеек по умолчанию). Щелкните кнопку ОК и внимательно просмотрите на таблицу. Если какие-либо значения не выровнены по правому краю, значит Excel не считает их датами.
Как конвертировать текст в дату в Excel
Когда возникает подобная проблема, скорее всего, вы захотите перевести эти текстовые значения в обычные даты Excel, чтобы вы могли ссылаться на них в формулах для выполнения различных вычислений. И, как это часто бывает в Экселе, есть несколько способов решения этой задачи.
Математические операции для преобразования текста в дату
Помимо использования функций Excel, о которых мы говорили чуть выше, вы можете выполнить простую математическую операцию, чтобы заставить программу выполнить реорганизацию строки в дату. Обязательное условие: операция не должна изменять ее значение (порядковый номер дня). Звучит немного сложно? Следующие примеры помогут разобраться!
Предполагая, что ваши данные находятся в ячейке A1, вы можете использовать любую из следующих формул, а затем применить формат даты к ячейке:
Как вы можете убедиться, математические операции могут помочь с датами (строки 3,4.,5,7), временем (строки 2 и 6), а также числами, отформатированными как текст (строка 8).
Иногда результат даже отображается в виде даты автоматически, и вам не нужно беспокоиться об изменении формата ячейки.
Как превратить текстовые строки с пользовательскими разделителями в даты
Если запись содержит какой-либо разделитель, отличный от косой черты (/) или тире (-), функции Excel не смогут распознать их как даты и вернут ошибку #ЗНАЧ!. Чаще всего такие «неправильные» разделители – это пробел и запятая.
Чтобы это исправить, вы можете запустить инструмент поиска и замены, чтобы заменить этот неподходящий разделитель, к примеру, косой чертой (/):
Теперь у ДАТАЗНАЧ или ЗНАЧЕН должно быть проблем с конвертацией текстовых строк в даты. Таким же образом вы можете исправить записи, содержащие любой другой разделитель, например, пробел или обратную косую черту.
Если вы предпочитаете решение на основе формул, вы можете использовать функцию ПОДСТАВИТЬ (SUBSTITUTE в английской версии), как это мы делали на одном из скриншотов ранее:
И текстовые строки преобразуются в даты, все при помощи одной формулы.
Как видите, функции ДАТАЗНАЧ или ЗНАЧЕН довольно мощные, но они, к сожалению, имеют свои ограничения. Например, если вы пытаетесь работать со сложными конструкциями, такими как четверг, 01 января 2015 г., ни одна из них не сможет помочь.
К счастью, есть решение без формул, которое может справиться с этой задачей, и следующий раздел даст нам пошаговое руководство.
Исправление записей с двузначными годами.
Современные версии Microsoft Excel достаточно умны, чтобы обнаружить некоторые очевидные ошибки в ваших данных, или, точнее, сказать, что Эксель считает ошибкой. Когда это произойдет, вы увидите индикатор ошибки (маленький зеленый треугольник) в верхнем левом углу клетки, и, когда вы выделите её, появится восклицательный знак.
При нажатии на восклицательный знак отобразятся несколько параметров, относящихся к вашим данным. В случае двухзначного года программа спросит, хотите ли вы преобразовать его в 19XX или 20XX.
Если у вас имеется несколько записей этого типа, вы можете исправить их все одним махом – выделите все ячейки с ошибками, затем нажмите на восклицательный знак и выберите соответствующую опцию.