Количество значений/элементов между двумя датами
При работе с наборами данных, может возникнуть необходимость посчитать количество значений/элементов между двумя датами. Решить подобную задачу с помощью встроенных функций в Excel, не так уж и трудно. Чаще всего такая задача возникает при формировании отчета по набору аналогичных записей.
Исходные данные
Значения в первом столбце нам не важны, используем их в данном примере только как ориентиры. Потому для нас интерес представляет столбец «C» содержащий даты. Подсчет будет осуществляться по датам, так что будут отражать/представлять/указывать даты не важно. Например, пусть в первом столбце ФИО посетителей, во втором столбце даты их посещения.
Количество значений/элементом меду двумя датами, будем находить при помощи двух функции Excel, «СЧЕТЕСЛИМН» и «ДАТА». Однако использование функции «ДАТА» будет зависеть от контекста подсчёта.
Так, например, для подсчета количества элементов в рамках года или полугодия, использование функции «ДАТА» необходимо. Однако при подсчёте количества элементов за квартал или месяц можно использовать только ссылки на значения в ячейках. Возможен и комбинированный вариант применения, всё зависит от представления данных в таблице.
Отличие функции «СЧЕТЕСЛИМН» от «СЧЕТЕСЛИ» заключается в возможности указания нескольких условий фильтрации. Как следствие множественная фильтрация, обеспечивает возможность подсчета значений в диапазоне.
После указания диапазона, указывается логическое условие. В формуле используется строковое определение условия с добавлением к нему результата функции «ДАТА». Начало диапазона задается как «>=»&ДАТА(E3;1;1), конец диапазона как «
Для того чтобы определить две даты мы используем функцию «ДАТА», первое значение функции ссылается на ячейку с данными, в нашем случае ячейки содержат года «2016» и «2017». Второй параметр функции определяет порядковый номер месяца, третий число месяца.
Таким образом результатом вычисления функции «ДАТА» в условии получаются значения первого дня года и последнего дня года. ДАТА(E3;12;31) – E3 ссылка на ячейку содержащую значение 2016. Второй параметр функции номер месяца, а третий параметр число. Соответственно результатом вычисления функции будет 31 декабря 2016 года.
В результате выполнения функций логические условия будут читаться следующим образом, например, для 2016 года: >=01.01.2016 – больше или равно 1 января 2016
Таким образом подсчитываться будут только те даты, которые попадают в диапазон фильтра, в текущем примере в целый год.
Для подсчета значений в месяц мы будем ссылаться не только на год, но и на месяц. В целом формула для подсчета значений за месяц похожа на подсчет за год. Отличие только в диапазоне. Проблема заключается только в определении последнего дня месяца.
Написать формулу для подсчета значений между двумя датами, еще проще. Нет необходимости использовать дополнительные функции, такие как «ДАТА» и «КОНЦМЕСЯЦА». Достаточно в условиях указать ссылки на ячейки, содержащие начальную и конечную дату диапазона.
Кстати, не во всех языках программирования есть стандартные функции для определения последнего дня месяца. При этом существуют стандартные функции для работы с датами, а именно для сложения и вычитания дат, и значений к датам. Так что, одним из приемов получения последнего дня месяца, является вычитание одного дня из первого дня следующего месяца.
Функции даты и времени в Excel – инструкция
Прежде, чем начать работать с датами, нужно отформатировать ячейки под данный формат. В большинстве случаев, при введении комплекта символов, похожего на дату, ячейка сама переформатируется. Но лучше все-таки сделать это вручную, чтобы подстраховать себя от неожиданностей.
Как в Excel посчитать дни между датами?
Так как дата является числом, значит можно проводить с ним математические вычислительные и расчетные операции. Посчитать количество дней между двумя датами Excel не составляет особых проблем. Для наглядного примера сначала выполним сложение, а потом вычитание дат. Для этого:
Обратите внимание, формат «Дата» автоматически присвоился для ячейки A3. Не сложно догадаться, чтобы посчитать разницу в датах в Excel нужно от новейшей даты отнять старшую дату. В ячейке B1 введите формулу:=A3-A1. Соответственно мы получаем количество дней между этими двумя датами.
Как складывать и вычитать даты, дни, недели, месяцы и годы в Excel
- На новый лист в ячейки A1:A3 введите даты: 18.04.1985; 17.08.1977; 08.12.1992
- В ячейки B1:B3 проставьте текущею дату.
- Теперь нужно воспользоваться функцией по преобразованию количества дней в количество лет. Для этого вручную введите в диапазон C1:C3 следующее значение: =РАЗНДАТ(A1;B1;»y»).
Основная обязанность, которую выполняет функция ГОД в Excel, является возврат года, который будет соответствовать указанной дате. Сам год будет определять функция ГОД как целое число в диапазоне от 1900 года до 9999 года.
| Параметр | Описание |
| «d» | Количество полных дней |
| «m» | Количество полных месяцев |
| «y» | Количество полных лет |
| «ym» | Количество полных месяцев без учета лет |
| «md» | Количество дней без учета месяцев и лет |
| «yd» | Количество дней без учета лет |
РАБДЕНЬ.МЕЖД
Синтаксис
РАБДЕНЬ.МЕЖД(нач дата;количество дней;выходные;праздники)
Нач дата (обязательный аргумент) – дата от которой будет производится отсчет.
Количество дней (обязательный аргумент) – количество рабочих дней до или после начальной даты. Положительное значение будет означать будущую дату, отрицательное прошедшую.
Праздники (необязательный аргумент) – список дат, необходимых исключить из расчета (гос. праздники и т.д.). Может представлять собой определенную дату, ссылку на дату или на диапазон дат.
Все даты должны находится в промежутке с 01.01.1900 г по 31.12.9999г и быть в общем формате или в формате даты, но не в текстовом.
Если выходные ссылаются на ячейку, которая выражена строкой, то эта ячейка должна быть отформатирована как текст.
Если выходные ссылаются на ячейку, которая выражена номером выходного дня, то эта ячейка должна быть в общем формате.
Если выходные, которые выражены строкой, вписываются в поле аргумента функции, то эта строка должна быть заключена в кавычки.
Если выходные, которые выражены номером выходного дня, вписываются в поле аргумента функции, то кавычки не нужны.
Как посчитать рабочие дни –
В документах Microsoft Excel может потребоваться указать на листе день и время его создания; или Вы работаете с отрезками времени, между которыми нужно посчитать количество дней, месяцев; а может нужно из проставленной даты понять, какому дню недели она соответствует, или добавить к ней определенное значение.
Примеры использования функций РАБДЕНЬ и РАБДЕНЬ.МЕЖД в Excel
Пример 1. Согласно расчетам, для выполнения проекта сотрудником фирмы потребуется 236 часов его беспрерывной работы. Дата начала проекта – 18.08.2018. Определить дату окончания работы над проектом. В ближайшие 30 дней праздников нет, выходные – суббота и воскресенье.
Автоматически определилось число для сдачи реализованного проекта по ключ.
Количество значений/элементов между двумя датами — WiseData
- нач_дата – обязательный аргумент, характеризующий значения формата Дата, которое является точкой отсчета (началом какого-либо события).
- количество_дней – обязательный аргумент, принимающий данные числового типа, которые характеризуют число рабочих дней, прошедших после начала какого-либо события или предшествующих ему. Считается, что данное количество не содержит дни, которые являются праздниками или выходными днями. Для обратного отсчета относительно даты, указанной в качестве аргумента нач_дата, данный аргумент следует оказать в виде отрицательного числа.
- [праздники] – необязательный аргумент, принимающий данные формата Дата, указывающие одну или несколько дат, соответствующих праздничным дням. В качестве данного аргумента может быть передан массив ячеек.
Если по какой-то причине вы не используете Таблицы (например, тем, кто работает в Excel 2003, этот инструмент не доступен), формула будет выглядеть так: =ОСТАТ( C2 — B2 ;1) . Не забудьте скопировать формулу во все ячейки столбца.
Как определить количество дней между датами в Эксель
Нет ничего проще, чем определить количество дней между датами. Просто вычтите более позднюю дату из ранней. Например, в ячейке А1 – дата начала работы над проектом, а в А2 – дата сдачи проекта. Тогда количество дней между ними можно посчитать так: =А2-А1 .
Эту же процедуру можно выполнить с помощью функции ДНИ(Конечная дата ; Начальная дата) . Видимой разницы между первым и вторым способами нет, они возвращают одинаковые результаты. Пользуйтесь этими способами ими по ситуации.
Формула расчета рабочего времени в excel — IT Новости
- начальная дата – ставят ссылку на ячейку с датой, к которой функция будет прибавлять рабочие дни;
- число рабочих дней – ставят количество рабочих дней, которое необходимо прибавить к начальной дате;
- праздники (необязательный) – ставят ссылку на диапазон с датами праздников.
Эта функция принимает два аргумента: дату окончания и дату начала. Если вы переключите даты в формуле, вы просто получите отрицательное число. Наконец, вы можете использовать функцию под названием DATEDIF, которая включена в Excel из Lotus 1-2-3 дня, но не является официально поддерживаемой формулой в Excel.
Функции даты и времени в Excel
В документах Microsoft Excel может потребоваться указать на листе день и время его создания; или Вы работаете с отрезками времени, между которыми нужно посчитать количество дней, месяцев; а может нужно из проставленной даты понять, какому дню недели она соответствует, или добавить к ней определенное значение.
Начнем с функции ДАТА – она выводит заданное число в числовом виде (А1). Аргументы у нее такие: (год;месяц;день). При этом формат ячейки автоматически меняется на Дата.
С ее помощью можно прибавить дни (месяца, года) к дате или отнять. Например, в А2 вставим текущую, используя ТДАТА, а затем в В2 пропишем формулу:
Что она значит: из А2 поочередно вытягивается каждый аргумент, а к дням добавляется 7. И теперь в В2 всегда будет показано значение на 7 дней больше текущего.
Подробнее прочесть про функцию ДАТА можете, перейдя по ссылке.
Следующая функция РАЗНДАТ. Ее в списке нет, но пользоваться ей можно. Она поможет рассчитать дни, месяцы, годы между заданными значениями. Аргумента у нее три: начальная, конечная дата и единица. В качестве последнего указывается: y, m, d, md, ym, yd. Так, например, если нужно посчитать месяцы между датами (А3 и В3), выбирайте m. Формула выглядит так:
Подробнее о РАЗНДАТ прочтите в статье: как посчитать дни между датами в Эксель.
ДАТАЗНАЧ – делает из указанной даты порядковый номер. Дело в том, что Эксель каждую дату понимает как определенное число. Отсчет начинается с 01.01.1900 года – это 1, 02.01.1990 – это 2 и так далее. В качестве аргумента – дата, записанная в виде текста (Е1), или объединенные значения из нескольких ячеек (D4).
В примере видно, что 13 ноября 2017 года соответствует число 43052. Его можно использовать для расчетов, применения фильтров, форматирования.
ДЕНЬ – достает из определенной даты только день. В качестве аргумента – указанная в числовом формате дата.
Например, применим формулу к ячейке А1. Результат будет 13 (А5).
ДАТАМЕС – вернет дату на заданное количество месяцев больше или меньше текущей. Аргументы: начальная дата и количество месяцев. Второй может быть как отрицательным, так и положительным числом.
Если к А1 прибавить 2 мес, получится 13 января (А6): =ДАТАМЕС(А1;2). Если нужна дата в прошлом, тогда в скобках укажите отрицательное значение: (А1;-2) – выйдет 13 сентября.
КОНМЕСЯЦА – возвращает последний день указанного месяца. В качестве аргументов: дата начала и число месяцев.
Например, нужно узнать последний день декабря. Начальной датой возьмем А1, значит мне нужно прибавить 1, чтобы из ноября получить декабрь. Формула такая:
Чтобы узнать, сколько дней в другом месяце, например, сентябре, формула получится:
То есть мы отняли 2 месяца от начальной даты (А1), чтобы получился сентябрь.
Функция ЧАС преобразует заданное десятичное число в часы или выделяет из ячейки с датой и временем только час. В Экселе время обозначается десятичными числами, так 12:00 – это 0,5 (А8), а если написать 0,46 (А9), то выделится 11 часов. Соответствующие формулы записаны в Е8 и Е9.
Если же у Вас в ячейке показываются текущие дата и время (А10), то из нее можно выделить число соответствующее часам: =ЧАС(А10).
Функция МИНУТЫ похожа на предыдущую, только возвращает соответствующее значение. Например, в А10 стоит 17:45. Записав формулу: =МИНУТЫ(А10), в В11 покажется только число 45. Если работаете с десятичными числами (А9), то можно указать и их в качестве аргумента. Так из 0,46 выделяется 2 минуты.
Чтобы показать количество секунд, используется функция СЕКУНДЫ. В ячейке А10 стоит 26 сек, хотя они и не отображаются; а числу 0,46 (А9) соответствует 24 сек.
Если из установленного в ячейке дня необходимо выделить только МЕСЯЦ, используется соответствующая функция. Формула: =МЕСЯЦ(А13) вернет в В13 число 11, которое относится к ноябрю.
Если нужно посчитать дни между двумя датами, причем только рабочие, используется ЧИСТРАБДНИ. У нее 3 аргумента: начальная и конечная даты и праздники (необязательный). Подробнее о ней рассказано в статье, ссылка на которую приведена в абзаце про РАЗНДАТ.
Она посчитает все рабочие даты за период с 13 ноября по 13 января, и при этом вычтет 2 праздника, 1 и 7 января.
Верхняя функция считает за выходные все сб и вс. Если у Вас это другие дни, лучше использовать ЧИСТРАБДНИ.МЕЖД. Ее аргументы: начальная дата, конечная, выходные, праздники.
Аргументу «выходные» соответствует определенное число. При их заполнении появится выпадающий список, из которого нужно его выбрать. Так, например, у меня выходной только в среду, значит, пишу в формуле «14». Указываю праздники, и выходит 51 рабочий дней.
Можно записать выходные и по-другому – в виде 1 и 0, взятых в кавычки. Нули – это рабочие, а единицы – выходные. Запись «0010010» означает, что мы отдыхаем в среду и субботу. Итого, работаем всего 42 дня.
Если на листе Excel в определенной ячейке нужна сегодняшняя дата, тогда используйте ТДАТА. Она в числовом формате отобразит дату и время (А18), установленные на компьютере. Аргументов здесь нет.
ВРЕМЯ – позволит вывести не целое число, соответствующее заданному времени. Аргумента здесь три – часы, мин и сек. Например, 18:14:57 – это 0,760381944.
ВРЕМЗНАЧ тоже вернет числовое значение для времени, только в качестве аргумента используется число в текстовом формате, заключенное в кавычки: =ВРЕМЯЗНАЧ(«18:14:57»).
Функцию СЕГОДНЯ я уже описывала. Она отобразит текущую дату. Аргументы здесь не указываются.
Кстати именно с ее помощью можно посчитать возраст человека по дате рождения. Для этого из текущего дня выделяется год и отнимается г/р человека: =ГОД(СЕГОДНЯ())-1945.
Если нужно определить день недели по заданной дате, тогда воспользуйтесь функцией ДЕНЬНЕД. У нее первый аргумент – это дата, второй – тип, какое число нужно вернуть. При вводе формулы развернется список, из которого его можно выбрать. Например, я хочу, чтобы пн соответствовала единица, вт – двойка и так далее, значит ставлю «11».
В результате, для 13 ноября 2017 года показано – 1. Правильно – это понедельник.
Чтобы вывести номер недели в году, на которую припадает определенное число, используется НОМНЕДЕЛИ. Здесь аргументы те же – дата и тип. Второй выбирается из выпадающего списка. Первая неделя в году может считаться или с 1 января (это 1 система), или по стандарту ISO 8601 с первого четверга (2 система).
Выделяйте в списке значения и смотрите в небольшой области рядом пояснения. Например, возьмем за первую неделю ту, которая начинается с понедельника, значит, ставим для второго аргумента «2».
В примере, 13 ноября в 2017 году (А21) припадает на 47 неделю.
И последняя функция, которую мы рассмотрим – РАБДЕНЬ. Она возвращает дату, которая больше или меньше на заданное число дней, но именно рабочих. Здесь не учитываются выходные и праздники. Аргумента у нее три: дата начала, дни, праздники.
Чтобы избежать ошибок в расчетах, начальное значение введем с помощью функции ДАТА (А24). Теперь давайте отсчитаем 80 дней (В24):
Если нужно, последним аргументом указываются адреса ячеек с праздниками. Ячейка, в которую выводится результат, должна быть отформатирована как дата (С24).
На этом буду заканчивать. Надеюсь, Вы нашли нужную функцию из того списка, который я описала в статье, и теперь сможете вывести или посчитать правильно данные в ячейках с датой и временем в Экселе.
Вычисление возраста по дате рождения в Excel
На чистом листе в ячейки А1:B1 введите 01.01.1900, а в ячейках A2:B2 вводим 01.01.1901. Теперь меняем формат ячеек на «числовой» в выделенном диапазоне B1:B2. Для этого можно нажать комбинацию клавиш CTRL+SHIFT+1.
