Является ли текст данных ячейки или числа?
Функция DATEDIF (РАЗНДАТ) в Excel. Как использовать?
Как посчитать сколько осталось дней до даты?
Руководители проектов выполняют такие расчеты с целью определения на основе контрольных дат процента выполнения реализации проекта. В персональной деятельности такие операции служат для заполнения деклараций сотрудников, а в финансовых отделах для расчета амортизации.
Для решения данной задачи в Excel можно пойти двумя путями:
- Воспользоваться функцией РАЗНДАТ.
- Выполнить обычное вычитание старой даты от новой.
Ниже на рисунке схематически изображен пример отчета с использованием функции РАЗНДАТ и второй вариант с помощью обычного вычитания дат. В отчете необходимо посчитать сколько осталось дней от сегодняшнего дня до оплаты фактур:
Как показано на рисунке, в ячейке D4 находиться следующая формула:
В первой формуле используется функция РАЗНДАТ с кодом измерения времени – «d» (дни), в третьем аргументе функции. В результате вычисления формулы функция РАЗНДАТ возвращает количество дней в данном периоде. В значениях ячеек мы получили число дней, которое должно пройти от сегодняшней даты (ее возвращает функция СЕГОДНЯ) до сроков оплат, изначально указанных в столбце A (Дата оплаты).
Второй вариант формулы:
Во второй формуле мы делаем обычное вычитание дат. От указанной в столбце А (новой) отнимаем сегодняшнюю (старшую) дату. В итоге получаем тот же результат. Почему это работает? В Excel, как и во всех других программах входящий в пакет MS Office используется система нумерации дат под названием «система 1900». Каждая дата в Excel – это число пройденных дней от 01.01.1900г. Если в ячейке с датой поменять формат на «Общий» или «Числовой» то вместо даты будет отображается числовой код. Он является порядковым номером дня по состоянию на эту же дату. Поэтому при вычитании дат мы просто отнимаем от большего количества, меньшее и получаем тот же результат, который возвращает функция.
В этом уроке Вы познакомитесь с различными полезными формулами сложения и вычитания дат в Excel. Например, Вы узнаете, как вычесть из одной даты другую, как прибавить к дате несколько дней, месяцев или лет, и т.д.
Если Вы уже проходили уроки по работе с датами в Excel (наши или любые другие уроки), то должны знать формулы для вычисления единиц времени, таких как дни, недели, месяцы, года.
Анализируя даты в каких-либо данных, часто нужно выполнять над этими датами арифметические действия. Эта статья объяснит некоторые формулы сложения и вычитания дат, которые могут оказаться для Вас полезными.
Как из даты выделить год в excel?
Используем функцию «Год», например, в ячейке «Е1» указана дата, и чтобы получить в ячейке «F1» год из даты, нужно написать формулу: =ГОД(E1).
Второй способ.
Используем функцию «ТЕКСТ», чтобы получить из ячейки «Е2» год, нужно записать функцию в виде =ТЕКСТ(E2;»ГГГГ»), получим год в виде четырех цифр, либо пишем функцию =ТЕКСТ(E2;»ГГ»), тогда получим две последние цифры года.
Третий способ.
Выделяем ячейку с датой и жмем правую клавишу мыши, в появившемся меню выбираем «Формат ячеек».
В меню «Формат ячеек» во вкладке «Число», в разделе «Числовые форматы» выбираем строку «Все форматы» и прописываем либо «ГГГГ», чтобы получить год в виде четырех цифр, либо «ГГ» и получим в виде двух цифр.
Как из даты получить год, месяц, день в Excel
Уважаемые читатели, мы продолжаем изучать функции дат. Мы уже научились вставлять текущую дату и время в ячейку. На очереди составные части дат.
Предположим, у вас есть перечень дат, для каждой из которых нужно ответить, «а в каком году…». То есть, выделить год, или месяц, или день из полной даты. Как это сделать? Ну не вручную же их перебирать? Нет, друзья, не вручную! Читайте пост и получайте долгожданные ответы!
Я знаю 6 функций для получения составных частей даты в Эксель. Все они имеют один обязательный аргумент – дата в числовом формате:
- ГОД(дата) – возвращает номер года из даты
- МЕСЯЦ(дата) – номер месяца из даты
- ДЕНЬ(дата) – указывает число месяца в заданной дате
- ЧАС(дата) – возвращает количество часов в дате
- МИНУТЫ(дата) – минуты в дате
- СЕКУНДЫ(дата) – секунды в дате
Если для даты не задано время, последние 3 функции вернут «0».
С помощью этих очевидных функций, вы легко выделите нужные компоненты из даты, а дальше – дело ваших рук и фантазии.
И напоследок, иллюстрация применения всех приведенных функций для закрепления знаний.
Получение части даты в Эксель
В следующем посте мы выполним обратную операцию – соберем дату из составных частей.
Функция Excel ДАТАЗНАЧ — изменить текст на дату
Преобразование даты в текст на практических примерах наглядно.
Формат даты и времени в Excel
Работая с ячейками в Microsoft Excel, которые содержат дату или время, Вы имеете возможность применять к ним различное форматирование. Например, Вы можете отображать в ячейке только день недели, или только месяц и год, или же только время.
Получить доступ ко всем возможным форматам Вы можете на вкладке Число диалогового окна Формат ячеек. В категории Дата приведены встроенные форматы дат:
А в категории Время – встроенные форматы времени:
Для того чтобы применить форматирование к ячейке, достаточно выбрать нужный формат в разделе Тип и нажать ОК. Нужное форматирование будет применено. Если встроенных числовых форматов Вам будет недостаточно, то можно воспользоваться категорией Все форматы. Здесь Вы также сможете найти много всего интересного.
Если ни один из вариантов Вас так и не устроил, то можно создать пользовательский числовой формат. Его создать достаточно легко, если в качестве образца использовать встроенные числовые форматы. Чтобы создать пользовательский числовой формат, выполните следующие действия:
- Выделите ячейку, к которой необходимо применить требуемый числовой формат.
- Откройте диалоговое окно Формат ячеек и перейдите на вкладку Число.
- В категории Все форматы в поле Тип введите необходимый код числового формата. В нашем случае мы введем: ДДДД ДД.ММ.СС чч:мм:cc и нажмем ОК.
- Пользовательский числовой формат будет применен к ячейке.
Как видите все достаточно просто!
В этом уроке мы научились настраивать формат отображения дат и времени в Microsoft Excel, а также разобрали несколько полезных вариантов их ввода на рабочий лист. В следующем уроке мы поговорим о 7-ми функциях Excel, которые позволяют извлекать нужные параметры из значений дат и времени. На этом урок закончен. Всего Вам доброго и успехов в изучении Excel.
Сколько лет, месяцев, дней прошло с конкретной даты в EXCEL
Как посчитать стаж в Excel
Стандартное нормальное распределение
Как превратить текстовые строки с пользовательскими разделителями в даты
Функции, задающие дату и время
Вставка текущих даты и времени в ячейку
Использование функций при работе с датами и временем
Применение функции СЕГОДНЯ в Excel
MySQL | Функции для работы с датами и временем
Функция ГОД() в EXCEL
Дата — дата, год которой необходимо найти.
Примеры
Используемая в качестве аргумента Дата должна быть в формате воспринимаемым EXCEL. Следующие формулы будут работать (см. файл примера ):
=ГОД(«28.02.2011») =ГОД(«28/02/2011») =ГОД(«28-02-2011») =ГОД(«28-фев-2011») =ГОД(«28-февраль-2011») =ГОД(«28 февраль 2011») =ГОД(«2011.02.28») =ГОД(40602) В EXCEL даты хранятся в виде последовательности чисел (1, 2, 3, . ), что позволяет выполнять над ними вычисления. По умолчанию день 1 января 1900 г. имеет номер 1, а 28 февраля 2011 г. — номер 40602, так как интервал между этими датами составляет 40 602 дня. О том как EXCEL хранит дату и время, читайте эту статью . =ГОД(A1) Если в ячейке А1 введена дата в одном из вышеуказанных форматов, то формулой будет возвращен год (2011).
Следующие формулы вернут ошибку #ЗНАЧ!
=ГОД(«28-февраля-2011») =ГОД(«28 02 2011»)
Использование с функцией ДАТА()
Для того, чтобы прибавить к дате 28.02.2011, например, 5 лет, можно использовать следующую формулу: =ДАТА(ГОД(A1)+5;МЕСЯЦ(A1);ДЕНЬ(A1))
Как преобразовать текст в дату при помощи формул.
Как вытащить месяц и год из даты в excel
Способ 3: считаем рабочие дни
Чтобы посчитать именно рабочие дни за определенный период используется функция ЧИСТРАБДНИ().
Для примера возьмем такую табличку. В столбце А у нас начало месяца или начальная дата отсчета, в В – конец месяца или отсчета. Данная функция считает рабочие дни без учета субботы и воскресенья, но в месяцах есть еще и праздники, поэтому столбец С заполним соответствующими датами.
Выделите ячейку, в которой будете считать, и выберите для нее общий числовой формат.
Дальше ставьте «=» и пишите функцию. У меня она выглядит так:
=ЧИСТРАБДНИ(A5;B5;C5)
В качестве аргументов указываем начальную дату (А5), потом конечную (В5). Последний аргумент – это праздники (С5). Разделяем их «;».
Нажав «Enter» появится результат, в примере ячейка D5 – 21 день.
Теперь рассмотрим, если в месяце несколько праздников. Например, в январе Новый год и Рождество. Выделяем ячейку (D6) и ставим в нее равно. Потом нажимаем в строке формул на букву «f». Откроется окно «Вставка функции». В поле «Категория» выберите «Полный алфавитный перечень» и найдите в списке нужную функцию. Жмите «ОК».
Дальше необходимо выбрать аргументы функции. В «Нач_дата» выбираем начальное значение (А6), в «Кон_дата» – конечное (В6). В последнем поле вписываем даты праздников в скобках {} и кавычках «». Потом нажимайте «ОК».
В результате мы получим такую функцию и значение будет посчитано без учета выходных и указанных праздников:
=ЧИСТРАБДНИ(A6;B6;{«01.01.17″;»07.01.17»})
Чтобы не прописывать праздники вручную, можно указать в соответствующем поле определенный диапазон. У меня это С6:С7.
Рабочие дни посчитаются, и функция будет иметь вид:
=ЧИСТРАБДНИ(A6;B6;C6:C7)
Теперь сделаем подсчет для последнего месяца. Вводим функцию и заполняем ее аргументы:
=ЧИСТРАБДНИ(A8;B8;C8)
В феврале получилось 19 рабочих дней.
Выбирайте один из описанных способов и считайте разницу в Эксель между необходимыми датами в днях, месяцах или годах, ну а если нужно, то рассчитайте только количество рабочих дней.
Об авторе: Олег Каминский
Вебмастер. Высшее образование по специальности «Защита информации». Создатель портала comp-profi.com. Автор большинства статей и уроков компьютерной грамотности
Синтаксис
Расчет разности дат
Для корректной работы с датами сперва нужно задать ячейкам соответствующий формат. В противном случае возможны проблемы с корректным отображением дат, что сделает невозможным их дальнейшую обработку.
На самом деле Эксель способен самостоятельно определять формат ячейки после ввода в нее информации. Но этот механизм не всегда срабатывает как надо, поэтому стоит заранее самостоятельно настроить формат ячеек или проверить, правильно ли его определила программа.
- Для начала выделяем целевой диапазон ячеек, воспользовавшись зажатой левой кнопкой мыши.
- Щелкаем по выделенной области правой кнопкой мыши и в открывшемся контекстном меню выбираем команду “Формат ячеек“. Аналогичного результата можно добиться, используя комбинацию клавиш Ctrl+1.
- Перед нами окно настроек формата ячеек:
- по умолчанию мы должны оказаться во вкладке “Число” (если это не так, переключаемся в нее);
- в списке “Числовые форматы” находим позицию “Дата” и кликаем на нее;
- в поле “Тип” выбираем нужный образец представления данных, в соответствии с которым они будут отображаться на листе;
- нажимаем OK.
- Теперь все значения, вводимые в ячейки выделенной области будут восприниматься программой как данные в формате “Дата“.