Сравнение дат в excel

Обновлено: 23.01.2025

Для вычислений длительностей интервалов дат в Excel есть функция РАЗНДАТ, в английской версии - DATEDIF.

Нюанс в том, что Вы не найдете эту функцию в списке Мастера функций, нажав кнопку fx - она является недокументированной возможностью Excel. Точнее говоря, найти описание этой функции и ее аргументов можно только в полной версии англоязычной справки, поскольку на самом деле она оставлена для совместимости со старыми версиями Excel и Lotus 1-2-3. Однако, несмотря на то, что эту функцию не получится вставить стандартным способом через окно Вставка - Функция (Insert - Function) , ее можно вручную вписать в ячейку с клавиатуры - и она сработает!

Синтаксис функции следующий:

=РАЗНДАТ( Начальная_дата ; Конечная_дата ; Способ_измерения )

С первыми двумя аргументами все более-менее понятно - это ячейки со стартовой и конечной датами. А самый интересный аргумент, конечно, последний - он определяет, каким именно образом и в каких единицах будет измеряться интервал между начальной и конечной датами. Этот параметр может принимать следующие значения:

"y"	разница в полных годах
"m"	в полных месяцах
"d"	в полных днях
"yd"	разница в днях с начала года без учета лет
"md"	разница в днях без учета месяцев и лет
"ym"	разница в полных месяцах без учета лет

Т.е. при желании подсчитать и вывести, например, ваш стаж в виде "3 г. 4 мес. 12 дн.", необходимо ввести в ячейку следующую формулу:

где А1 - ячейка с датой поступления на работу, А2 - с датой увольнения.

или в английской версии Excel:

Ссылки по теме

Здравствуйте. Подскажите, как нужно считать стаж работы в этом случае:
с 01.09.2004г.
по 01.03.2006г.
с 14.03.2006г.
по 01.08.2006г.
с 17.08.2006г.
по 05.09.2011г.
с 05.12.2011
по 02.07.2012г.
Т.е. как учитывать разрывы? и второе, как посчитать, например не все, а пару-тройку "промежутков"? Дата ухода - Дата прихода по каждому рабочему месту отдельно. Потом все сложить. Посмотрите видео тут - будет понятнее. Николай, здравствуйте. Подскажите пожалйста следующий момент. Стаж работы по каждому месту работы в днях я подсчитал и просуммировал. допустим получилось 4231. Как сделать так чтобы представление количества полученных дней было в виде "3 г. 4 мес. 12 дн." - как в вашем примере. Т.е. как таковой разницы дат нет - есть полученное количество дней.
Спасибо. Тогда встречный вопрос - сколько дней в месяц будем брать? 30?

Если подсчитанное количество дней стажа в А1, то:
A2=ЦЕЛОЕ(A1/365) - количество лет
A3=ЦЕЛОЕ(ОСТАТ(A1;365)/30,5) - количество месяцев
A4=A1-A2*365-A3*30,5 - количество дней

Здравствуйте!
Подскажите пожалуйста, как быть если необходимость подсчитать количество полных календарных месяцев. Например, основное средство принято в эксплуатацию 14 января 2013 г., отчёт по данному ОС готовится по состоянию на 27 августа 2013 г. Количество амортизируемых месяцев - 6 (февраль, март, апрель, май, июнь, июль). Если применить РАЗНДАТ используя способ измерения "ym" , то результат будет - 7, что в данном случае не прав-но, так как требуется подсчитать не полные месяцы , а полные календарные месяцы. Как быть?
Спасибо за помощь. Леонид, если в А1 лежит дата приема в эксплуатацию, а в А2 - дата, на которую готовится отчет, то количество полных месяцев можно посчитать просто как =МЕСЯЦ(A2)-МЕСЯЦ(A1)

Николай, спасибо за подсказку. В моём случае, дополнительно к приведённой формуле, пришлось применить поправочную константу " -1 ". Формула приняла вид =(МЕСЯЦ(A2)-МЕСЯЦ(A1))-1 .

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

Ещё раз спасибо.

Николая доброго времени суток. Вопрос в следующем для учета дней отпуска необходимо что бы после 1 год 2 мес и 15 (и более) дн таблица давала значение 1год и 3 месяца, в случае если менее 15 календарных дней то 1 год и 2 месяца. Заранее спасибо за ответ. Николай! Аллилуйа! у меня заработало
=РАЗНДАТ(СЕГОДНЯ();C3;"y")&" г. "&РАЗНДАТ(СЕГОДНЯ();C3;"ym")&" мес. "&РАЗНДАТ(СЕГОДНЯ();C3;"md")&" дн.", это для списка задач.
А как теперь к этой красоте приделать условное форматирование? чтобы, допустим, меньше двух недель осталось - красный, от двух недель до месяца желтый, больше месяца до шести зеленый, больше шести синий? заранее спасибо! Николай, добрый день! Помогите пожалуйста.Есть два столбца: в одном фамилия сотрудника, в другом уже посчитанный медицинский стаж работы, уже в виде, допустим 22г. 2м. 13д. Хотелось бы, чтобы в третьем столбце можно было считать стаж на каждый месяц, учитывая, что по истечении 30 дней они переводятся в месяц, соответственно 12 месяцев добавляют к годам. Функцию РАЗНДАТ не использую, так как для 500 сотрудников трудоёмко вводить отдельно года их работы, тем более у многих идет прерывный стаж, поэт.сразу же на первое января 2014 года стаж посчитан и введен в столбцы. Заранее спасибо.

Доброго времени суток! Подскажите как написать формулу по следующим критериям: расчет количества дней между двумя датами, при условии что полный календарный месяц, входящий в разницу между датами, должен считаться как 30 дней?
Заранее спасибо!

Нужна помощь специалистов.
Имею время начала операции и время окончания операции в следующем формате.
Нужно рассчитать время выполнения операции в часах.

Вр. создания	Вр. решения	Длит.выполнения
10.02.2014 16:35	10.02.2014 16:54
10.02.2014 16:56	11.02.2014 09:18

Как это можно осуществить?
Буду очень признателен за помощь.

Вр. создания	Вр. решения	Длит.выполнения
10.02.2014 16:35	10.02.2014 16:54	=B1-A1
10.02.2014 16:56	11.02.2014 09:18

Добрый!
Вот это было бы очень полезно. В формате 3 г. 4 мес. 12 дн. 5 ч. 20 мин. или только количество часов. Есть у кого идеи на этот счет?

Подскажите как посчитать продолжительность отпуска ( с вычетом заданных праздничных дней) но не в рабочих днях, а в календарных.
Я пробовала задать в функции WORKDAY.INTL условие - что нет выходных, но это не работает

У нас подсчет отпусков ведется в календарных днях (минус праздники , выпадающие на период отпуска)

Динара, вам нужна функция NETWORKDAYS.INTL в следующем виде:
=NETWORKDAYS.INTL(A1,A2,"0000000",D1:D5)
где
А1 - начало отпуска
А2 - конец отпуска
D1:D5 - даты праздников

Николай скажите пожалуйста,а как полученный стаж: например: 2 года 2 месяца и 2 дня умножить на какой либо коэффициент. например 2 .

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

Заранее спасибо за ответ,
Трофимова Татьяна

Прибавить один?

Николай, как не прискорбно, но я вынужден всё-таки признать, что сам никак (сколько не бился) не могу этого сделать - найти способ добавить к формуле =РАЗНДАТ(A1;B1;"d") еще один день. Прошу помощи!
Заодно, если можно, подскажите: каким образом (формулой?) можно перевести количество дней (т.е.простое целое положительное число) в показатель: Х л Х м Х д?(если таковая есть вообще)
Спасибо!

Разность между текстовыми датами(кол-во месяцев с учетом последнего) =(12*(ПСТР(B1;7;4)-ПСТР(A1;7;4))-ПСТР(A1;4;2)+ПСТР(B1;4;2))

Приветствую! подскажите новичку. какую конструкцию построить, чтоб выдавал г. (год), если результат 1,2,3,4 и л. (лет), если результат по годам больше 5? спасибо! ( а то как-то рез-т : " стаж 2 лет " или " стаж 5 год " режет слух:D)

Прошу совета
Использую формулу вычисления возраста =РАЗНДАТ(A1; СЕГОДНЯ(); "y")

A1 в формате дата.
Если А1 не заполнено (то есть равно 0) то возраст показывается как 116

как этого избежать?

В ответе показывается 116, потому что если значение A1 равно 0, то Excel вместо 0 подставляет дату 00.01.1900 (минимальную, которую система считает начальной). Поэтому 116 - это значение между датой 00.01.1900 и сегодняшней. Решить вопрос можно через функцию ЕСЛИ. То есть в ячейке, где считается стаж (допустим, C1) функция РАЗНДАТ должна быть внутри функции ЕСЛИ. Пример: ячейка A1 содержит дату поступления на работу, ячейка B1 - сегодняшнюю дату (функцию СЕГОДНЯ), ячейка C1 - формулу, что считает стаж. Тогда значение ячейки C1 будет таким: =ЕСЛИ(A1=0;0;РАЗНДАТ(A1;B1;"y";)&"г."&РАЗНДАТ(A1;B1;"ym";)&"мес."&РАЗНДАТ(A1;B1;"md";)&"дн.";). То есть Если A1 равно 0, то ячейка C1 будет показывать 0, если больше, то будет включаться функция РАЗНДАТ. В вашем случа формула будет такая: =ЕСЛИ(A1=0;0;РАЗНДАТ(A1;СЕГОДНЯ();"y")) Добрый день! Подскажите, пожалуйста, как дату в формате 6 лет, 6 мес. преобразовать в формат 6,5?

Добрый день! Подскажите, есть две ячейки с вычесленными стажами через РАЗНДАТ. Мне нужно их сложить. То есть, есть ячейка 1г. 5мес. 12дн. и есть ячейка 2г. 11мес. 4дн. Вот как их сложить, чтобы получилось общее количество лет, месяцев и дней? То есть 4г. 4мес. 16дн. (в ручную примерно посчитал). Есть ли такая функция? Спасибо!

ФИО	Принят	УВОЛЕН	ОТРАБОТАНО	ЛЕТ	МЕСЯЦЕВ	ДНЕЙ
Сидоров Иван Петрович	24.02.2014	29.05.2018	1555	4	3	5
03.09.2018	14.10.2018	41	0	1	11
03.11.2018	19.12.2018	46	0	1	16
25.04.2019	27.11.2020	582	1	7	2
0	0	0	0
0	0	0	0
0	0	0	0
0	0	0	0
0	0	0	0
0	0	0	0
0	0	0	0
0	0	0	0
0	0	0	0
0	0	0	0
0	0	0	0
2224	5	12	34
6,09	=ПРАВСИМВ(ГОД(I19);2)	=МЕСЯЦ(I19)	=ДЕНЬ(I19)
I19 >	01.02.1906	06	2	1

Доброго времени суток! Я столкнулся с задачей следующего содержания:
Есть стаж работы в нескольких организациях (4-6 периодов). Как вычислить дату , на которую стаж будет равен 10 лет?
Спасибо!

помогите, пожалуйста, никак не могу сообразить. надо просчитать количество лет (нужно получить не целое число) просрочки платежей с учетом того, что просрочка составляет больше одного года и часть периода просрочки может попадать на високосный год, а может не попадать.

например, срок оплаты 20.10.2015, нужен срок просрочки задолженности на 10.02.2017 в нецелых годах с учетом того, что в 2015 году 365 дней, в 2016 - 366, и в 2017 - 365. на данный момент я высчитываю сначала кол-во дней с 20.10.15 по 01.01.16 плюс с 01.01.17 по 10.02.17 и делю на 365, потом прибавляю один год. либо если надо посчитать просрочку например, на 20.10.16 то сначала считаю просрочку 2015 года и делю на 365, потом отдельно за 2016 и делю их на 366 и итоги суммирую. скорее всего это абсолютно и совершенно кустарный метод на коленке, но другого не знаю, а потом взываю к вам за помощью и советом:)

Здравствуйте! А вы можете сделать видео урок посещенной функции "РАЗНДАТ"?
По вычислению трудового стажа?
Заранее спасибо

привет! Подскажите, есть две ячейки с вычесленными стажами через РАЗНДАТ. Мне нужно их сложить. То есть, есть ячейка 1г. 5мес. 12дн. и есть ячейка 2г. 11мес. 4дн. Вот как их сложить, чтобы получилось общее количество лет, месяцев и дней? То есть 4г. 4мес. 16дн. Есть ли такая функция? Спасибо!

Николай, спасибо за открытие функции РАЗНДАТ – очень полезная функция!
Интересно, какие еще в Excel есть недокументированные функции?

Добрый день!
В первой из двух ячеек с датами для расчета, через Alt+Enter перечислено несколько дат. Есть ли возможность сконструировать формулу так, чтобы она могла отсортировывать и обращаться к первой из имеющихся в первой ячейке дат или такая возможность отсутствует и содержимое этой ячейки программой воспринимается как свалка? Заранее спасибо!

Вывести список рабочих, работавших в субботу и в воскресенье

Здаствуйте помогите вычислить формате 3 г. 4 мес. 12 дн. 5 ч. 20 мин. или только количество часов. Есть у кого идеи на этот счет?

Я прошу помощи в следующем.
Имеются два столбика. Первый столбик - это дата начала периода. Второй столбик - дата окончания периода. Каждая строка представляет ПЕРИОД, протяженностью несколиких лет (больше одного).
Нужно вычислить количество месяцев для каждого "составляющего" года. То есть итог будет выглядеть результатом для каждого года, который вкладывается в период каждой строки.

Например для периода 02.01.2018 (столбик 1) - 28.06.22 (столбик 2) результат будет выражен следующим:

Столбик 3 (2018 год) - 12 месяцев;
Столбик 4 (2019 год) - 12 месяцев;
Столбик 5 (2020 год) - 12 месяцев;
Столбик 6 (2021 год) -12 месяцев;
Столбик 7 (2022 год) -6 месяцев.
Всего месяцев - 54

Необходимость связана с планированием и расчетами долгосрочных проектов (работ), когда необходимо автоматически (при изменении дат начала или окончания при планировании и расчетах) получать количество месяцев производимых работ в каждом годе, входящим в состав поолного ПЕРИОДА. Фактически формула кажется должна иметь универсальную архитектуру и количество лет будет определять эту формулу с интерпретацией для каждого входящего в полный период года.

В принципе, с учетом срочной необходимости, меня устроит ЦЕЛОЕ число месяцев, поскольку, видимо, сложно определить точное число месяев (не целое).

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

Часто пользователям табличного редактора необходимо осуществить такую непростую процедуру, как сравнение дат. Реализовать это действие можно различными способами. В статье мы детально разберем все методы, позволяющие осуществить сравнение дат в табличном редакторе.

Обработка времени в табличном редакторе

Табличный редактор обрабатывает время и дату в виде числовых данных. Программа производит преобразование этой информации таким образом, что одни сутки приравниваются к 1. В итоге показатель времени представляет из себя часть от единицы. Например, 12.00 – это 0.5. Показатели даты табличный редактор превращает в числовое значение, которое равно числу суток от 1 января 1900 года до указанной даты. К примеру, если пользователь преобразует дату 14.04.1987, то она будет иметь значение 31881. Иными словами, от изначального показателя прошло 31881 суток. Эта механика применяется при вычислениях значений времени. Для того чтобы вычислить число суток между 2-мя датами, необходимо от большего временного показателя отнять меньший временной показатель.

Использование оператора ДАТА в табличном редакторе

Общий вид оператора выглядит так: ДАТА(год; месяц; день). Каждый из аргументов обязателен к написанию в операторе. Задать аргумент можно двумя способами. Первый способ подразумевает обычный ввод числовых значений. Второй способ подразумевает ввод координат ячеек, в которых располагается необходимая числовая информация. В первый аргумент вводятся числовые значения от 1900 до 9999. Во второй аргумент вводятся числовые значения от 1 до 12. В третий аргумент вводятся числовые значения от 1 до 31.

К примеру, если в качестве дня указать числовое значение, превышающее показатель 31, то лишние сутки переместятся на другой месяц. Если пользователь введет в марте тридцать два дня, то в итоге он получит первое апреля.

Пример применения оператора выглядит так:

Пример указания большего числа суток в июне:

Пример, показывающий применение в виде аргументов координат ячеек:

Использование оператора РАЗНДАТ в табличном редакторе

Этот оператор осуществляет возврат между 2-мя значениями дат. Общий вид оператора выглядит так: РАЗНДАТ(начальная_дата;последняя_дата;код_для_обозначения_единиц_подсчета). Виды вычисления интервалов между двумя указанными показателями дат:

В некоторых версиях табличного редактора при применении крайних 2-х аргументов оператор может вывести ошибку. В этом случае целесообразнее использовать другие формулы.

Пример, показывающий работу оператора:

В табличном редакторе 2007 года этот оператор отсутствует в справочнике, но его все равно можно использовать.

Применение оператора ГОД в табличном редакторе

Этот оператор позволяет вернуть год в виде целочисленного значения, соответствующего указанной дате. Числовое значение выводится в диапазоне от 1900 до 9999. Общий вид оператора ГОД имеет 1 аргумент. Аргументом является дата, записанная в числовом виде. Он должен быть написан посредством оператора ДАТА или же выводить итоговый показатель расчета каких-либо других формул. Пример, показывающий работу оператора:

Применение оператора МЕСЯЦ в табличном редакторе

Этот оператор позволяет вернуть месяц в виде целочисленного значения, соответствующего указанной дате. Числовое значение выводится в диапазоне от 1 до 12. Общий вид оператора МЕСЯЦ имеет 1 аргумент. Аргументом является дата месяца, записанная в числовом виде. Он должен быть написан посредством оператора ДАТА или же выводить итоговый показатель расчета каких-либо других формул. Стоит заметить, что месяц, записанный в текстовом виде, будет обработан табличным редактором неправильно. Пример, показывающий работу оператора:

Примеры применения операторов ДЕНЬ, ДЕНЬНЕД и НОМНЕДЕЛИ в табличном редакторе

Этот оператор позволяет вернуть день в виде целочисленного значения, соответствующего указанной дате. Числовое значение выводится в диапазоне от 1 до 31. Общий вид оператора ДЕНЬ имеет 1 аргумент. Аргументом является дата дня, записанная в числовом виде. Он должен быть написан посредством оператора ДАТА или же выводить итоговый показатель расчета каких-либо других формул. Пример, показывающий работу оператора:

Оператор, имеющий наименование ДЕНЬНЕД, позволяет осуществить возврат порядкового номера дня недели заданной даты. Стандартно оператор считает воскресенье 1-м днем недели. Пример, показывающий работу оператора:

Оператор, имеющий наименование НОМНЕДЕЛИ, позволяет осуществить показ порядкового номера недели в заданной дате. Пример, показывающий работу оператора:

К примеру, 24.05.2015 – это двадцать вторая неделя в году. Как было написано выше, программа считает воскресенье первым днем недели.

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

Для задания текущего показателя даты применяется оператор СЕГОДНЯ. В этом операторе отсутствуют аргументы. Для отображения текущих показателей даты и времени используется оператор ТДАТА().

Заключение и выводы о сравнении дат в табличном редакторе

Мы выяснили, что существует множество способов и операторов, позволяющих произвести сравнение двух дат в табличном редакторе. Самый распространенный вариант – это использование оператора РАЗНДАТА, позволяющего реализовать возврат разницы между двумя показателями дат. Дополнительно можно использовать схожие формулы, позволяющие вернуть значения дня, месяца и года. Каждый пользователь может самостоятельно подобрать для себя наиболее удобный способ, позволяющий осуществить сравнение дат в табличном редакторе.

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

Эта задача сперва может несколько сбить с толку, поскольку даты нельзя на первый взгляд сравнить между собой, как числа. Но на самом деле, это именно так и делается. Причина в том, что Excel каждую дату записывает в числовом формате. Следовательно, сравнивая даты, по сути программа сравнивает между собой два числа. И здесь возможно несколько вариантов, которые мы рассмотрим более подробно.

2 популярных способа сравнения 2 дат в Excel

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

Первая дата больше второй, или нет

В этом случае нужно использовать аналогичную формулу, которая используется для проверки чисел: =A1>A2. Если эта проверка подтверждается, то в ячейку, куда была записана эта формула, возвращается значение “ИСТИНА”. Если же первое число меньше второго, то тогда будет записано “ЛОЖЬ”. Аналогично, только зеркально противоположно с формулой =A1<A2. Если в результате проверки оказывается, что первое значение оказалось меньше второго, то в ячейку будет возвращено логическое значение «ИСТИНА». Если же больше – «ЛОЖЬ».

Функция ЕСЛИ, значение ИСТИНА или ЛОЖЬ

Это уже более сложный вариант, который дает возможность не только говорить, действительно ли одна дата больше другой, но и указывать, на какое количество дней. Чтобы это сделать, необходимо нажать по ячейке и записать туда формулу =ЕСЛИ(A2>B2;»Первая дата больше второй на»&» «&A2-B2&» «&»дней»;»Первая дата меньше второй на»&» «&B2-A2&» «&»дней»)

Здесь мы осуществляем проверку двух дат, и исходя из того, что у нас получилось в итоге, выводим результат, на сколько дней первая дата больше, чем вторая. Чтобы это сделать, нами был использовал оператор &, который объединяет две строки текста между собой и объединенный результат возвращает в ячейку. Далее получившийся текст можно использовать сам по себе или в других формулах.

Предположим, вы работаете в пенсионном фонде и хотите определить трудовой стаж в количестве лет, дней, месяцев и так далее. Это можно сделать с помощью функции РАЗНДАТ .

Ее синтаксис элементарный.

РАЗНДАТ(начальная_дата; конечная_дата; способ_измерения)

Единственный из этих аргументов, который действительно может вызвать вопросы у неподготовленного читателя – это способ измерения. С его помощью мы задаем, в каких единицах будет измеряться разница между датами. Это может быть количество месяцев, лет или количество месяцев без учета лет. Еще один вариант – это количество дней без учета месяцев и лет.

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

Разница 2 дат в днях

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

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

Предположим, начальной датой у нас является 23.02.2009, а конечной – 24.02.2009.

В результате формулой будет возвращено значение 1, что равняется количеству дней, которое прошло с момента исходной даты (отсчет начинается с нуля). Именно из-за того, что отсчет в этом случае начинается с нуля, нельзя использовать эту формулу в чистом виде, например, чтобы рассчитать количество дней, которое отработал человек. Ведь очевидно, что если человек начал работать 23 февраля, а 24 числа прекратил работать, то он по факту отработал 2 дня, а не один.

Та же самая история с количеством месяцев. Если начальной датой служит 28 февраля, а конечная – первое марта, и оба числа относятся к 2008 году, то результат будет 2 дня. И это правильно.

Чтобы решить все проблемы с отображением месяцев в Excel, существует специальная формула ЦЕЛОЕ() , которая округляет число в меньшую сторону. Поскольку время является дробной частью целого дня в понимании Excel, то фактически эти часы будут отсечены. То же касается и других элементов даты, числа, месяца, которые представлены, как дробная часть.

Кстати, можно использовать формулу =ЧИСТРАБДНИ() для того, чтобы определить количество рабочих дней между двумя датами.

Разница дат в полных месяцах

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

То есть, формула следующая.

Эта формула также может давать несколько неточные данные. Поэтому всегда нужно перепроверять значения самостоятельно.

Разница в днях без учета лет

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

Эта формула вернет правильный результат только тогда, когда в компьютере показано правильное время. Конечно, в большинстве современных операционных системах дата и время определяется автоматически, но иногда случаются сбои. Поэтому перед тем, как подсчитывать разницу в днях без учета лет, нужно убедиться, что на компьютере установлена правильная дата и время. Особенно если электронная таблица пересылалась другому человеку.

Быстрое выделение различающихся значений

Это очень просто. Если вам необходимо понять, есть ли отличающиеся значения в документе Эксель, вам необходимо найти кнопку «Найти и выделить», расположенную на вкладке «Главная». После этого в диалоговом окне «Выделить группу ячеек» в качестве критерия выбираем «отличия по строкам».

2 3

После этого все отличающиеся значения будут выделены автоматически, и с ними можно осуществлять те действия, которые нужно.

Сравнение 2 дат с помощью условного форматирования

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

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

В качестве правила условного форматирования необходимо использовать формулу. После этого записываем формулу (важно, чтобы результат получался истинным), стиль форматирования и нажимаем на кнопку «ОК».

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

Сравнение 2 столбцов на предмет совпадения

Чтобы сравнить между собой два столбца, являются ли они одинаковыми или нет, необходимо воспользоваться условным форматированием. Последовательность действий в целом такая же, как описано выше. Предположим, нам необходимо выполнить такие операции со следующей таблицей.

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

Сама последовательность действий следующая:

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

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

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

Для сравнения двух колонок нам нужно воспользоваться уже знакомым инструментом «Условное форматирование». Приблизительная последовательность действий будет следующей. Если говорить о конкретных шагах, то они будут сильно зависеть от того, какую конкретную задачу необходимо выполнить пользователю.

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

Нами была применена функция СЧЕТЕСЛИ , которая осуществляет проверку списка на предмет соответствия определенному критерию, после чего выдает значение «ИСТИНА» или «ЛОЖЬ». Если была возвращена «ИСТИНА», то соответствующие ячейки окрашиваются в цвета, заданные пользователем до использования условного форматирования.

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

Формула для сравнения 2 дат

Как видим, формула для сравнения 2 дат может быть любой из вышеперечисленных. Пользователь может выбирать, какую из них выбирать. В ряде случаев придется модифицировать их, чтобы адаптировать под конкретные цели. Например, когда перед нами стояла задача определить, сколько дней работник проработал, то к имеющейся формуле нужно добавлять единицу, чтобы отсчет начать не с нуля, а первого дня.

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

Используйте функцию РАЗДАТ, если требуется вычислить разницу между двумя датами. Сначала поместите дату начала в ячейку, а дату окончания в другую. Введите формулу, как по одной из следующих формул:

Разница в днях

В этом примере начальная дата находится в ячейке D9, а даты окончания — в ячейке E9. Формула находится в F9. "d" возвращает количество полных дней между двумя датами.

Разница в неделях

В этом примере начальная дата находится в ячейке D13, а даты окончания — в ячейке E13. "d" возвращает количество дней. Однако обратите внимание на /7 в конце. При этом количество дней делится на 7, так как в неделе 7 дней. Обратите внимание, что этот результат также должен быть отформатирован как число. Нажмите CTRL+1. Затем щелкните Число>десятичных числах: 2.

Разница в месяцах

В этом примере начальная дата находится в ячейке D5, а даты окончания — в ячейке E5. В формуле "м" возвращает количество полных месяцев между двумя днями.

Разница в годах

В этом примере начальная дата находится в ячейке D2, а даты окончания — в ячейке E2. "y" возвращает количество полных лет между двумя днями.

Расчет возраста в накопленных годах, месяцах и днях

Вы также можете вычислить возраст или время службы другого человека. Результат может выглядеть так: "2 года, 4 месяца, 5 дней".

1. Чтобы найти общее количество лет, используйте dateDIF.

В этом примере дата начала находится в ячейке D17, а даты окончания — в ячейке E17. В формуле "y" возвращает количество полных лет между двумя днями.

2. Чтобы найти месяцы, снова используйте DATEDIF с "м".

В другой ячейке используйте формулу РАЗНОМЕР с параметром "м". "м" возвращает количество оставшихся месяцев за последний полный год.

3. Используйте другую формулу для поиска дней.

Теперь нужно найти количество оставшихся дней. Для этого мы напишем формулу другого типа, как показано выше. Эта формула вычитает первый день окончания месяца (01.05.2016) из исходной даты окончания в ячейке E17 (06.05.2016). Вот как это делается: сначала функция ДАТА создает дату 01.05.2016. Она создается с помощью года в ячейке E17 и месяца в ячейке E17. 1 обозначает первый день месяца. Результатом функции ДАТА будет 01.05.2016. Затем мы вычитаем эту дату из исходной даты окончания в ячейке E17 (06.05.2016), в результате чего получается 5 дней.

Предупреждение: Не рекомендуется использовать аргумент "мд" РАЗДАТ.

4. Необязательно: объединение трех формул в одну.

Все три вычисления можно поместить в одну ячейку, как в этом примере. Используйте амперанд, кавычка и текст. Это длинная формула, но по крайней мере она все в одной формуле. Совет. Нажмите ALT+ВВОД, чтобы ввести разрывы строк в формулу. Это упрощает чтение. Кроме того, если вы не видите всю формулу, нажмите CTRL+SHIFT+U.

Скачивание примеров

Вы можете скачать образец книги со всеми примерами из этой статьи. Вы можете следовать за ним или создать собственные формулы.

Другие вычисления даты и времени

Как было посчитано выше, функция РАЗДАТ.В.ДВ вычисляет разницу между датой начала и датой окончания. Однако вместо ввода определенных дат в формуле можно использовать функцию СЕГОДНЯ(). При использовании функции СЕГОДНЯ() Excel для даты используется текущая дата вашего компьютера. Имейте в виду, что эта переменная будет меняться при повторном открыть файле в будущем.

Обратите внимание, что на момент написания статьи день был 6 октября 2016 г.

Используйте NETWORKDAYS. Функция INTL, если вы хотите вычислить количество рабочих дней между двумя датами. Вы также можете исключить выходные дни и праздники.

Прежде чем начать: Решите, нужно ли исключить даты праздников. В этом случае введите список дат праздников в отдельной области или на отдельном листе. Поместите каждую праздниковую дату в свою ячейку. Затем выберем эти ячейки и выберем формулы > определить имя. Привяжем диапазон MyHolidaysи нажмите кнопку ОК. Затем создайте формулу с помощью действий ниже.

1. Введите даты начала и окончания.

В этом примере дата начала находится в ячейке D53, а даты окончания — в ячейке E53.

2. В другой ячейке введите формулу такого вида:

Введите формулу, как в примере выше. Число 1 в формуле устанавливает субботы и воскресенье в качестве выходных и исключает их из общего числа.

Примечание. Excel 2007 не имеет networkDAYS. Функция INTL. Однако в нем есть NETWORKDAYS. В этом примере в Excel 2007 г. будет выглядеть так: =NETWORKDAYS(D53;E53). Вы не указываете 1, так как в networkDAYS предполагается, что выходные — в субботу и воскресенье.

3. При необходимости измените 1.

Если суббота и воскресенье не являются выходными, измените число 1 в списке IntelliSense. Например, 2 дня по понедельникам и понедельникам являются выходными.

Если вы используете Excel 2007, пропустите этот шаг. Excel 2007 г. функция NETWORKDAYS всегда предполагает, что выходные дни — в субботу и воскресенье.

4. Введите название диапазона праздников.

Если вы создали имя диапазона праздников в разделе "Начало работы" выше, введите его в конце так: Если у вас нет праздников, оставьте запятую и запятую MyHolidays. Если вы используете Excel 2007, пример будет такой: =ДНЕЙМН(D53;E53;MyHolidays).

Совет: Если вы не хотите ссылаться на название диапазона праздников, можно также ввести диапазон, например D35:E:39. Можно также ввести в формулу каждый праздник. Например, если ваши праздники были 1 и 2 января 2016 г., введите их так: =СЕТЕВЫЕДНИ. INTL(D53;E53;1;). В Excel 2007 г. она будет выглядеть так: =ДНЕЙМН(D53;E53;)

Вы можете вычесть затращенное время, вычитая одно время из другого. Сначала поместите время начала в ячейку, а время окончания в другую. Обязательно введите полные часы, минуты и пробелы до am или PM. Ниже рассказывается, как это сделать.

1. Введите время начала и окончания.

В этом примере время начала находится в ячейке D80, а время окончания — в ячейке E80. Введите часы, минуты и пробелы до am или PM.

2. Задать формат ч:мм.AM/PM.

Выберите обе даты и нажмите CTRL+1 (или + 1 на Компьютере Mac). Выберите настраиваемый >ч:мм/PM, если он еще не установлен.

3. Вычитать два раза.

В другой ячейке вычитайте ячейку времени начала из ячейки времени окончания.

4. За установите формат ч:мм.

Нажмите клавиши CTRL+1 (или +1 на Mac). Выберите Настраиваемые > ч:мм, чтобы исключить из результатов am и PM.

Расчет заархив времени между двумя датами и временем

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

1. Введите две полные даты и время.

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

2. Задать формат 14.03.2012, 13:30.

Выберем обе ячейки и нажмите CTRL+1 (или + 1 на компьютере Mac). Затем выберите Дата >14.03.2012, 13:30. Это не та дата, которая будет заданной вами, а только пример того, как будет выглядеть формат. Обратите внимание, что в версиях, предшествующих Excel 2016, этот формат может иметь другой пример даты, например14.03.2011 13:30.

3. Вычитать эти два.

4. За установите формат [ч]:мм.

Нажмите клавиши CTRL+1 (или +1 на Mac). Выберите пункт (все форматы). В поле Тип введите [ч]:мм.

Читайте также: