Функция РАЗНДАТ – разность двух дат в днях, месяцах и годах
Предположим, вы работаете в пенсионном фонде и хотите определить трудовой стаж в количестве лет, дней, месяцев и так далее. Это можно сделать с помощью функции РАЗНДАТ.
Ее синтаксис элементарный.
РАЗНДАТ(начальная_дата; конечная_дата; способ_измерения)
Единственный из этих аргументов, который действительно может вызвать вопросы у неподготовленного читателя – это способ измерения. С его помощью мы задаем, в каких единицах будет измеряться разница между датами. Это может быть количество месяцев, лет или количество месяцев без учета лет. Еще один вариант – это количество дней без учета месяцев и лет.
Важно учесть, что в некоторых случаях эта функция может вернуть неправильное значение. Это бывает, когда день первой даты больше, чем день второй
Такая ситуация может быть, например, когда нужно рассчитать количество дней между двумя датами, первая из которых происходила в конце месяца, а другая – в начале следующего.
Пример №1. Подсчет количества лет между двумя датами
На примере выше, формула возвращает количество лет между двумя датами. Например, функция вернет значение “27” если вы захотите посчитать количество полных лет между двумя датами 19 июня 1989 года и 26 марта 2017 года. Система подсчитывает количество полных лет и игнорирует количество месяцев и дней между датами.
Разница дат в полных месяцах
Чтобы определить количество полных месяцев, которое прошло с одной даты до другой, необходимо воспользоваться такой же самой формулой, которая была приведена выше, только третий аргумент должен содержать аргумент m.
То есть, формула следующая.
=РАЗНДАТ(A2;B2;”m”)
Эта формула также может давать несколько неточные данные. Поэтому всегда нужно перепроверять значения самостоятельно.
Разница в днях без учета лет
Эта формула также может использоваться для определения количества дней между двумя датами, но при этом не учитывать количество лет, которое прошло между этими двумя временными точками. Эта возможность есть, но пользоваться ею настоятельно не рекомендуется, потому что искажений в этом случае может быть еще больше.
1
Эта формула вернет правильный результат только тогда, когда в компьютере показано правильное время. Конечно, в большинстве современных операционных системах дата и время определяется автоматически, но иногда случаются сбои. Поэтому перед тем, как подсчитывать разницу в днях без учета лет, нужно убедиться, что на компьютере установлена правильная дата и время. Особенно если электронная таблица пересылалась другому человеку.
Количество рабочих дней между двумя датами
Здесь ситуация чуть сложнее. Необходимо не учитывать субботы с воскресеньями и праздники. Для такого расчета лучше воспользоваться функцией ЧИСТРАБДНИ (NETWORKDAYS) из категории Дата и время. В качестве аргументов этой функции необходимо указать начальную и конечную даты и ячейки с датами выходных (государственных праздников, больничных дней, отпусков, отгулов и т.д.):
Примечание: Эта функция появилась в стандартном наборе функций Excel начиная с 2007 версии. В более древних версиях сначала необходимо подключить надстройку Пакета анализа. Для этого идем в меню Сервис — Надстройки (Tools — Add-Ins) и ставим галочку напротив Пакет анализа (Analisys Toolpak). После этого в Мастере функций в категории Дата и время появится необходимая нам функция ЧИСТРАБДНИ (NETWORKDAYS).
Сдвиг даты на заданное количество дней
Поскольку одни сутки в системе отсчета даты Excel принимаются за единицу (см.выше), то для вычисления даты, отстоящей от заданной на, допустим, 20 дней, достаточно прибавить к дате это число.
Сдвиг даты на заданное количество рабочих дней
Эту операцию осуществляет функция РАБДЕНЬ (WORKDAY). Она позволяет вычислить дату, отстоящую вперед или назад относительно начальной даты на нужное количество рабочих дней (с учетом выходных суббот и воскресений и государственных праздинков). Использование этой функции полностью аналогично применению функции ЧИСТРАБДНИ(NETWORKDAYS) описанной выше.
Вычисление дня недели
Вас не в понедельник родили? Нет? Уверены? Можно легко проверить при помощи функции ДЕНЬНЕД (WEEKDAY) из категории Дата и время.
Первый аргумент этой функции — ячейка с датой, второй — тип отсчета дней недели (самый удобный — 2).
Вычисление временных интервалов
Поскольку время в Excel, как было сказано выше, такое же число, как дата, но только дробная его часть, то с временем также возможны любые математические операции, как и с датой — сложение, вычитание и т.д.
Нюанс здесь только один. Если при сложении нескольких временных интервалов сумма получилась больше 24 часов, то Excel обнулит ее и начнет суммировать опять с нуля. Чтобы этого не происходило, нужно применить к итоговой ячейке формат 37:30:55:
Как прибавить (вычесть) дни к дате в Excel
Если у Вас есть дата в ячейке или список дат в столбце, Вы можете прибавить к ним (или отнять) определённое количество дней, используя соответствующую арифметическую операцию.
Пример 1. Прибавление дней к дате в Excel
Общая формула добавления определённого количества дней к дате выглядит так:
Дату можно задать несколькими способами:
-
Cсылкой на ячейку:
-
Обращением к функции ДАТА (DATE):
-
Обращением к другой функции. Например, чтобы прибавить несколько дней к текущей дате, используйте функцию СЕГОДНЯ (TODAY):
Следующий рисунок показывает действие этих формул. На момент написания текста текущей датой было 6 мая 2015 г.
Примечание: Результатом выполнения этих формул является целое число, представляющее дату. Чтобы показать его как дату, необходимо выбрать ячейку (или ячейки) и нажать Ctrl+1. Откроется диалоговое окно Формат ячеек (Format Cells). На вкладке Число (Number) в списке числовых форматов выберите Дата (Date) и затем укажите нужный вам формат. Более подробное описание Вы найдёте в статье Как изменить формат даты в Excel.
Пример 2. Вычитание дней из даты в Excel
Чтобы вычесть определённое количество дней из даты, Вам снова нужно использовать обычную арифметическую операцию. Единственное отличие от предыдущего примера – минус вместо плюса
Вот несколько примеров формул:
Шаг 2. Формат даты. Настройка формата даты по умолчанию
Настройка системной переменной “Формат даты по-умолчанию”
Формат даты по умолчанию в рамках 1 приложения настраивается с помощью переменной на вкладке MAIN в скрипте:
Настройка форматы даты по-умолчанию в рамках 1 приложения Qlik Sense
SET DateFormat='DD.MM.YYYY';
К этой настройке относитесь очень внимательно, т.к. функции дат, например Date(), без обозначения формата даты используют формат даты по-умолчанию.
Формат даты. Примеры
Рассмотрим примеры записи форматов даты:
Date(43500,'DD.MM.YYYY') = 04.02.2019 Date(43500,'DD.MM.YY') = 04.02.19 Date(43500,'DDMMYYYY') = 04022019 Date(43500,'DD/MM/YYYY') = 04/02/2019 Date(43500,'DD+MM+YYYY') = 04+02+2019 Date(43500,'DD|MM|YYYY') = 04|02|2019 Date(43500,'DD MM YYYY') = 04 02 2019 Date(43500,'DD.MMM.YYYY') = 04.февр..2019 Date(43500,'DD MMM YYYY') = 04 февр. 2019 Date(43500,'DD/MMM/YYYY') = 04/февр./2019 Date(43500,'D/MMM/YYYY') = 4/февр./2019 Date(43500,'D MMMM YYYY') = 4 февраль 2019 Date(43500,'D MMMM YY') = 4 февраль 19 Date(43500,'YYYY.MM.DD') = 2019.02.04 Date(43500,'YYYYMMDD') = 20190204 Date(43500,'YYYY-MM') = 2019-02 Date(43500,'YYYY-DD-MM') = 2019-04-02
Расчет кол-ва неполных рабочих дней между двумя датами
Также, к примеру, можно использовать эту же функцию для расчета неполных рабочих дней.
Допустим, у нас есть похожая таблица с данными:
Формула, для нашего случая, будет такой:
Для обозначения выходных дней, мы использовали «1010111».
- 0 — рабочий день
- 1 — неполный рабочий день
Первая цифра из этого числа — понедельник, последняя — воскресенье
Грубо говоря, «0000011» значит, что с понедельника по пятницу — рабочие дни, а суббота и воскресенье — нерабочие (выходные).
По той же логике, «1010111» означает, что только вторник и четверг являются рабочими, а остальные 5 дней — нерабочими.
Если вам нужно исключить какие-то дни из расчетов, можете исключать их таким образом.
Примеры практического применения функций для работы с датами
Эти примитивные функции очень полезны при группировки данных по: годам, месяцам, дням недели и конкретным дням.
Допустим у нас имеется простой отчет по продажам:
Нам нужно быстро организовать данные для визуального анализа без использования сводных таблиц. Для этого приведем отчет в таблицу где можно удобно и быстро группировать данные по годам месяцам и дням недели:
Теперь у нас есть инструмент для работы с этим отчетом по продажам. Мы можем фильтровать и сегментировать данные по определенным критериям времени:
Кроме того, можно составить гистограмму для анализа самых продаваемых дней недели, понять на какой день недели приходится наибольшее количество продаж:
В таком виде очень удобно сегментировать отчеты по продажам за длительные, средние и короткие периоды времени.
Стоит сразу отметить что для того чтобы получить разницу между двумя датами нам не поможет ни одна из выше описанных функций. Для данной задачи следует воспользоваться специально предназначенной функцией РАЗНДАТ:
Тип значений в ячейках «дата» требует особого подхода при обработке данных. Поэтому следует использовать соответствующие данному типу функции в Excel.
Каждая транзакция проводиться в какое-то время или период, а потом привязывается к конкретной дате. В Excel дата – это преобразованные целые числа. То есть каждая дата имеет свое целое число, например, 01.01.1900 – это число 1, а 02.01.1900 – это число 2 и т.д. Определение годов, месяцев и дней – это ничто иное как соответствующий тип форматирования для очередных числовых значений. По этой причине даже простейшие операции с датами выполняемые в Excel (например, сортировка) оказываются весьма проблематичными.
Обработка времени в табличном редакторе
Табличный редактор обрабатывает время и дату в виде числовых данных. Программа производит преобразование этой информации таким образом, что одни сутки приравниваются к 1. В итоге показатель времени представляет из себя часть от единицы. Например, 12.00 – это 0.5. Показатели даты табличный редактор превращает в числовое значение, которое равно числу суток от 1 января 1900 года до указанной даты. К примеру, если пользователь преобразует дату 14.04.1987, то она будет иметь значение 31881. Иными словами, от изначального показателя прошло 31881 суток. Эта механика применяется при вычислениях значений времени. Для того чтобы вычислить число суток между 2-мя датами, необходимо от большего временного показателя отнять меньший временной показатель.
Функции времени ГОД, МЕСЯЦ, ДЕНЬ в Excel
В этой статье мы поговорим о трёх основных функций дат, это функция ГОД, функция МЕСЯЦ и функция ДЕНЬ в Excel. Это три составляющие любой даты и теперь рассмотрим, как с ними работать и для чего это нам надо. В первую очередь, эти функции служат для извлечения определенного параметра из имеющейся даты, мы может отдельно изъять год, числовую последовательность месяца или дня. Использование этих функций будет актуально когда, к примеру, нужно будет поделить ваши данные по временным отрезкам по годам, по месяцам или по дням. Часто использование этих функций оправдано при работе со статистическими данными, когда их применение улучшают показатели анализа.
Теперь давайте рассмотрим каждую из функций времени по отдельности:
Функция ГОД в Excel
Основная обязанность, которую выполняет функция ГОД в Excel, является возврат года, который будет соответствовать указанной дате. Сам год будет определять функция ГОД как целое число в диапазоне от 1900 года до 9999 года.
Синтаксис, который использует функция ГОД в Excel очень простой:
=ГОД(указаная_дата_в_числовом_формате)
Дата_в_числовом_формате, является обязательным аргументом и представляет собой дату, год которой следует вычислить. Даты нужно вводить при помощи функции ДАТА, указывать ссылкой на ячейку, которая содержит дату в соответствующем формате или это будет результат отдельных расчётов иных функций и формул
Важно!В случае, когда дата будет введена как текст, это приведёт к ошибкам и проблемам.
Стоит знать что значения, которое возвращает функция ГОД в Excel полностью, ведется по григорианскому календарю, без разницы какой формат для даты был указан. Даже если вы указываете дату по другому летоисчислению или альтернативному календарю, значение которое функция ГОД вернет, будет соответствовать дате григорианского календаря.
Все даты MS Excel хранит как последовательные числа и именно это позволяет нам работать с ними и использовать в своих вычислениях. По умолчанию первая дата под номером 1, это 1 января 1900 года, а вот, к примеру, 1 января 2018 года будет предоставлен числом 43101, так разница между этими двумя датами будет составлять 43101 день.
Функция МЕСЯЦ в Excel
Главная особенность, как используется функция МЕСЯЦ в Excel, это возврат значения месяца из даты, который заданный как числовой формат. Месяц будет возвращен функцией как целое число в диапазоне чисел от 1 до 12, что соответствует месяцам от января до декабря.
Синтаксис, который использует функция МЕСЯЦ в Excel, простой и незатейливый:
=МЕСЯЦ(ваша_дата_в_числовом_формате)
Дата_в_числовом_формате, является обязательным аргументом и представляет собой дату, месяц которой следует вычислить. Даты нужно вводить при помощи функции ДАТА, указывать ссылкой на ячейку, которая содержит дату в соответствующем формате или это будет результат отдельных расчётов иных функций и формул
Важно!В случае, когда дата будет введена как текст, это приведёт к ошибкам и проблемам.
Особенности, как используется функция МЕСЯЦ в Excel, соответствуют аналогично предыдущей рассматриваемой функции, так как формат дат распространяется на все функции времени.
Функция ДЕНЬ в Excel
Основная обязанность, которую выполняет функция ДЕНЬ в Excel, является возврат число дня, которое будет в указанной дате. День будет возвращен функцией как целое число в диапазоне чисел от 1 до 31, что зависит от максимального количества дней в месяце.
Синтаксис, который использует функция ДЕНЬ в Excel, так же не отличаеться обилием аргументов:
=ДЕНЬ(ваша_дата_в_числовом_формате)
Дата_в_числовом_формате, является обязательным аргументом и представляет собой дату, день которой следует вычислить. Даты нужно вводить при помощи функции ДАТА, указывать ссылкой на ячейку, которая содержит дату в соответствующем формате или это будет результат отдельных расчётов иных функций и формул
Важно!В случае, когда дата будет введена как текст, это приведёт к ошибкам и проблемам.
Как и предыдущие функции, функция ДЕНЬ в Excel хранит даты как последовательные числа и значения которые она возвращает, соответствуют датам григорианского календаря.
Отдельно для каждой функции я решил не создавать примеры их использования, поскольку они очень схожи в характере работы, рознятся только полученные результаты, разницу вы можете изучить на скриншоте:
Как посчитать количество дней в Excel
Ну, вот и всё что я, пока, хотел вам рассказать о таких функциях работы с датами как функция ГОД, функция МЕСЯЦ и функция ДЕНЬ. Очень надеюсь, что моя статья вам помогла в решении вашей проблемы. Жду ваши лайки и комментарии, делитесь статьей с друзьями и коллегами, пусть нас будет много!
Примеры практического применения функций для работы с датами
Эти примитивные функции очень полезны при группировки данных по: годам, месяцам, дням недели и конкретным дням.
Допустим у нас имеется простой отчет по продажам:
Нам нужно быстро организовать данные для визуального анализа без использования сводных таблиц. Для этого приведем отчет в таблицу где можно удобно и быстро группировать данные по годам месяцам и дням недели:
Теперь у нас есть инструмент для работы с этим отчетом по продажам. Мы можем фильтровать и сегментировать данные по определенным критериям времени:
Кроме того, можно составить гистограмму для анализа самых продаваемых дней недели, понять на какой день недели приходится наибольшее количество продаж:
В таком виде очень удобно сегментировать отчеты по продажам за длительные, средние и короткие периоды времени.
Стоит сразу отметить что для того чтобы получить разницу между двумя датами нам не поможет ни одна из выше описанных функций. Для данной задачи следует воспользоваться специально предназначенной функцией РАЗНДАТ:
Скачать примеры функций для работы с датами в Excel
Тип значений в ячейках «дата» требует особого подхода при обработке данных. Поэтому следует использовать соответствующие данному типу функции в Excel.
Вступление
Недавно, после очередной бессонной ночи, я размышлял о методах запоминания количества дней в каждом месяце года. Для этого есть считалочка, а также способ считать на костяшках пальцев, но ни то, ни другое меня не устроило. Я задумался, а не существует ли какой-нибудь математической формулы для решения такой задачи, и — не обнаружив при беглом изучении таковую — бросил себе вызов её создать.
Формализуя
Другими словами, необходимо найти функцию
f
, такую, что значение
f(x)
для каждого месяца
x
, представленного числом от 1 до 12, равняется количеству дней в этом месяце. Таблица значений аргумента и функции
1
| x | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| f(x) | 31 | 28 | 31 | 30 | 31 | 30 | 31 | 31 | 30 | 31 | 30 | 31 |
Если у вас возникло желание попробовать самому до прочтения моего решения, то сейчас самое время. Если же вы предпочитаете немедленно увидеть готовый ответ, то посмотрите под спойлер.
Ответ
Ниже следуют мои шаги по нахождению решения.
Функция Excel ДАТАЗНАЧ — изменить текст на дату
Функция DATEVALUE (DATEVALUE в английской версии) преобразует текстовую дату в порядковый номер дня, который Excel распознает как дату.
Синтаксис DATEVALUE очень прост:
= DATEVALUE (date_as_text)
Простейшее использование этой функции = DATEVALUE (A1), где A1 — это ячейка с датой, хранящейся в виде строки символов.
Поскольку при этом текст преобразуется в порядковый номер дня, вам все равно необходимо применить соответствующий формат представления. И иногда программа может делать это автоматически за вас.
На снимке экрана показано несколько формул DATEVALUE в действии:
На первый взгляд результат работы может показаться вам довольно неожиданным. Вы можете увидеть это на скриншоте в столбце C. Но не запутайтесь — вам просто нужно применить нужное форматирование. Как видите, результатом формулы является порядковый номер дня с 1900 года (столбец C). И если мы применим правильный формат, то есть укажем программе, что на самом деле это не число, а день, мы сразу получим приемлемый результат (столбец D).
Следовательно, столбец D показывает результат C, только на этот раз как дату.
Как вы, наверное, знаете, Excel хранит дату и время как порядковые числа, и только форматирование ячеек позволяет отображать число как дату. В частности, 1 января 1900 года сохраняется как номер 1, а 2 января 1900 года сохраняется как 2. В результате 8 марта 2020 года сохраняется как 43898. Чтобы преобразовать такой серийный номер в дату, все, что вам нужно сделать сделать это изменить формат ячейки.
Для этого выберите ячейку или диапазон ячеек с числами, которые вы хотите представить в виде дат, и нажмите Ctrl + 1, чтобы открыть диалоговое окно «Формат ячеек». Во вкладке «Числовые форматы» в пункте «Дата» выберите подходящий вам аспект в разделе «Тип» и нажмите кнопку «ОК».
Также обратите внимание, что не весь текст можно использовать таким образом. Если Excel не может с этим справиться, он возвращает # ЗНАЧ! (см пункт 3 на фото выше). Также, если по какой-то причине вы использовали запятые вместо точек, то с помощью функции REPLACE сразу замените их точками — и все будет хорошо
Также, если по какой-то причине вы использовали запятые вместо точек, то с помощью функции REPLACE сразу замените их точками — и все будет хорошо.
Функция ДАТАВАЛОРЕ — что запомнить?
При преобразовании текстовой строки в дату имейте в виду, что:
- Информация о времени в текстовых значениях игнорируется, как вы можете видеть в строках 2 и 6 на изображении выше (1). Чтобы преобразовать эти значения, содержащие дату и время, используйте функцию VALUE.
- Если год не указан в «текстовой дате», DATEVALUE заменит текущий год системным временем компьютера, как показано в строке 3 выше (2).
- Поскольку Microsoft Excel начинает отсчет времени только с 1 января 1900 г., использование VALOREDATA с более ранним возрастом приведет к ошибке #VALUE!.
- Все значения, связанные со временем, хранятся в Excel в виде числа. Но DATEVALUE не может преобразовывать числовое значение в меру времени и не может обрабатывать текст, который выглядит как число. Для этого вам нужно будет использовать функцию ЗНАЧЕНИЕ. И именно об этом мы поговорим позже.