Как сравнить дату с текущей датой в excel - Word и Excel

Пытливый

Пользователь

Сообщений: 4587
Регистрация: 22.12.2012

1.»method rows of object _global failed»(
это в каком месте выдает? У меня не возникает такой ошибки при открытии файла (хошь какой лист сохраняй активным).
2. Вы ж не написали, что надо цветом выделять. Вы написали — сравнить. Вот макрос и сравнивает. Одну дату нашел — ругнулся и вышел из процедуры.
Если вам надо было сравнить все даты в столбце и выделить просроченные — так и надо было писать.
Сформулируйте задачу конкретно — надо сообщение, не надо, каким цветом выделять, какие ячейки и т.д.
Переделывать все по 15 раз не особо хочется.

Кому решение нужно — тот пример и рисует.

Источник

Например, вы хотите сравнить список заданных дат с текущей датой и определить, прошла ли дата в этом списке. В этой статье показаны методы определения того, прошла ли дата в Excel.

Определите, прошли ли даты с помощью формулы
Определите, прошли ли даты с помощью условного форматирования
Легко определить, прошли ли даты с Kutools for Excel

Определите, прошли ли даты с помощью формулы

Предположим, что текущая дата — 2015/5/25, с помощью следующей формулы вы можете определить, прошла ли дата по сравнению с сегодняшней датой. Пожалуйста, сделайте следующее.

1. Выберите пустую ячейку рядом с датой, по которой вы хотите определить, просрочена ли она. Введите в нее формулу ниже, а затем нажмите Enter .

=IF(ISBLANK(A2),»»,IF(A2<TODAY(),»Overdue»,»Not due»))

2. Перетащите маркер заполнения в диапазон с датами, которые вы хотите определить, если они просрочены. Смотрите скриншот:

Заметки:

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

Определите, прошли ли даты с помощью условного форматирования

Для Условное форматирование вы можете выделить все просроченные даты по сравнению с сегодняшней датой.

1. Выберите список дат и щелкните Условное форматирование > Новое правило под Главная таб. Смотрите скриншот:

2. в Новое правило форматирования диалоговое окно, вам необходимо:

А. В Выберите тип правила раздел, выберите Форматировать только ячейки которые содержат.
Б. Выберите Значение ячейки в первом Форматировать только ячейки с раскрывающимся списком.
C. Выберите меньше, чем во втором Форматировать только ячейки с выпадающим списком.
D. В третьей ячейке Только формат с раскрывающимся списком введите = СЕГОДНЯ ().
E. Щелкните значок Формат кнопка. Смотрите скриншот:

3. в Формат ячеек диалоговом окне укажите цвет фона для выделения просроченных дат, а затем щелкните значок OK кнопку.

4. Когда он вернется в Новое правило форматирования диалоговое окно, нажмите OK.

Затем все прошедшие даты по сравнению с текущей датой сразу выделяются заданным цветом заливки.

Внимание: Если в списке дат есть пустые ячейки, они также будут выделены.

Определите, прошли ли даты с Kutools for Excel

Для Выбрать определенные ячейки полезности Kutools for Excel, вы можете легко выбрать все просроченные даты в столбце по сравнению с сегодняшней датой.

1. Выберите диапазон дат, которые вы хотите сравнить с текущей датой, и выберите просроченные даты, затем нажмите Кутулс > Выберите > Выбрать определенные ячейки. Смотрите скриншот:

2. в Выбрать определенные ячейки диалоговое окно, выберите Ячейка в Тип выбора раздел, выберите Менее в Конкретный тип раскрывающийся список, введите сегодняшнюю дату в следующее поле и, наконец, щелкните OK кнопку.

3. Затем Выбрать определенные ячейки всплывающее окно подсказки, чтобы сообщить вам, сколько ячеек будет выбрано, щелкните OK Кнопка, и ячейки с просроченной датой выбираются немедленно.

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

Статьи по теме:

Как определить, находится ли дата между двумя датами или выходными в Excel?
Как определить, находится ли число или значение в диапазоне в Excel?
Как определить / проверить дату выходного дня в Excel?

Лучшие инструменты для работы в офисе

Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%

Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

Источник

Как сравнить даты в Excel.

Смотрите также 1 столбце меньше: так надо: Даст неверный ответ: =И(ГОД(A1)=ГОД(B1);МЕСЯЦ(A1)=МЕСЯЦ(B1);ИЛИ(И(ДЕНЬ(A1)=16;ДЕНЬ(B1)>=16))) нужна колонка с там у Вас
— работа УФ формате даты. Аргумент перейдут на следующийПодсчитывает число ячеек вЕгоров «Функция «СЧЕТЕСЛИ» в в ячейках столбца Получилось так. разницу цветом шрифта, отличающиеся от дат
Нам нужно  даты в 3
=((ДЕНЬ(A5) для второй половиныДаты а А1
результатами, в которой уже было в (условного форматирования) – дата дня, месяц. Например, указав диапазоне B14:B17 с9 000 Excel». В, то окраситьКак сравнить даты в ячеек, т.д. Один в первом столбце.
в Excel сравнить даты столбце, то должныGIG_ant месяца, например 20.01.11
и В1 видны цифры с
исходном файле. Я
Тут, конечно, форум который нужно найти, для декабря 32 датами в интервалеГладкихЭтот способ сравнения эти ячейки вExcel. способ сравнения, разместить Столбцов для сравненияв разных периодах,
получить дату 1: вот такой монстрик: и 25.01.11.
roka разницей 1,2,3 дня же не знал, по Excel, а в числовом формате.
дня, получим в от 01.01.2010 до8 000 можно применить при красный свет.Можно сравнить даты.
две таблицы на может быть больше. найти одинаковые даты, столбца, если же=ЕСЛИ((ГОД(A2)=ГОД(B2))*(МЕСЯЦ(A2)=МЕСЯЦ(B2));ЕСЛИ((ДЕНЬ(A2)15)*(ДЕНЬ(B2)>15);»Равно»;»Не равно»);»Не равно»)можно так: спасибо, сейчас запробую и т.д. не зачем оно Вам, не по русскомуЧтобы вернуть порядковый номер результате 1 января.
 31.12.2010 включительно (3). Гладких сравнении двух прайсов.Как работать с Принцип сравнения дат
одном мониторе одновременно,Как посчитать в посчитать, сколько одинаковых больше, то итогroka=(ДЕНЬ(A1) а тут
webley
более того поэтому стирать не
языку, но, знаете, дня недели дляПример использования функции: В данном примере20 000 Смотрите статью «Как условным форматированием, как
тот же – описан в статьеExcel, сколько дней в дат совпадают в дата в 3: О, вот это получается, что все: Вариант:
Pelena стал. «есть же какие-то указанной даты, можноЗададим большее количество дней показано, как обойтисьЕгоров сделать прайс-лист в настроить цвет заливки, выделяем столбцы, нажимаем «Сравнение таблиц Excel». периодах дат пересекаются,
определенных периодах. столбце. отлично даты в месяце=ЕСЛИ(И(ДЕНЬ(A1): Так?
AGK вещи!»… применить функцию ДЕНЬНЕД: для июня: без функции СЧЁТЕСЛИМН,5 000 Excel». шрифта в условном на кнопку «Найти

excel-office.ru

Как сравнить два столбца в Excel на совпадения.

Здесь рассмотрим, сколько одинаковых датНапример, мы составляемПользуюсь формулой если,Guest равны, а должныZ200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ОТБР(B2-A2): Добрый день! НужнаKarataevПо умолчанию функция считаетПримеры использования в качестве которая впервые появиласьТумановПримечание: форматировании, как написать и выделить». Выбираемкак сравнить столбцы в в периодах. график отпусков или то, но не
: «О, вот это быть равны даты: Это как это?Rustem помощь, застопорился на
: Результат вставляется в воскресенье первым днем аргументов ссылок на в Excel 2007.22 500
Мы стараемся как другие условия для функцию «Выделение группы ExcelДля этого нужно график отгрузки товара, все даты дают отлично» с 1 до  01.01.2011 = 40544.00,
: Что-то в этом решении  столбец «G». Для
недели. ячейки: Функция ДАТАЗНАЧ преобразуетФормула можно оперативнее обеспечивать выделения ячеек, строк, ячеек», ставим галочку, выделить разницу цветом,
определить, пересекаются периоды оплаты счетов, график верные данные. Даты,А теперь сравнить 15 между собой а 15.01.2011 = духе?. Нужно контролировать «G3»:Для отображения порядкового номераВозвращает разницу между двумя даты в числовые
Описание (результат) вас актуальными справочными т.д., читайте в у слов «Отличия символами, т.д. дат полностью, частично тренингов, работы, т.д. которые больше 3 15.02.11 и 01.03.11 и с 16 40558.00 Иль о200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(B2-A2 оплату по отгрузкам.
=ЕСЛИ(СЧЁТЕСЛИМН(A:A;E3;B:B;F3)=0;1;»»)
 недели для указанной датами.  значения, с которыми
=СЧЁТЕСЛИ(B2:B7;»>9000″) материалами на вашем статье «Условное форматирование по строкам». НажимаемНапример, несколько магазинов или не пересекаются. Нам среди всех столбца, так и Все формулы, кроме  по 31, а
чем другом речьГостьотсрочка 45 дней.vikttur даты применяется функцияАргументы: может работать функцияФункция СЧЁТЕСЛИ подсчитывает число языке. Эта страница в Excel». Получилось «ОК».
сдали отчет по В ячейке Е2 этих дат нужно
остаются равны 1 двух, посчитают неправильно даты до 15 — одно пишем: Подскажите как можно нужно считать сколько: Кросс НОМНЕДЕЛИ:начальная дата; СУММПРОИЗВ. ячеек в диапазоне переведена автоматически, поэтому так.Здесь расхождение дат в продажам. Нам нужно пишем такую формулу. выявить даты, которые столбцу. Перепробовала все.Михаил С. и с 16 два на ум
сравнить между собой дней просрочки поhttp://www.planetaexcel.ru/forum….i-datoyДата 24.05.2015 приходится наконечная дата;Для работы с датами B2:B7, значения которых
ее текст может
Третий способ. ячейках строк второй сравнить эти отчеты=МЕДИАНА(B2;C2+1;C3+1)-МЕДИАНА(B2;C2+1;B3) пересекаются, накладываются друг
 и форматы меняла
: Вы правы, нужна между собой естественно пошло?.. «Сравнить» -  две ячейки и отгрузке прошло, ноNikolь
22 неделю вкод, обозначающий единицы подсчета в Excel в больше 9000 (4). содержать неточности иСравнить значения столбцов в и третьей. и выявить разницу.Функция «МЕДИАНА» находится на друга. и пыталась даты еще проверка не равны выявить схожесть или выделить те ячейки если оплата прошла: Добрый вечер! Изучила году. Неделя начинается (дни, месяцы, годы разделе с функциями
=СЧЁТЕСЛИ(B2:B7;» грамматические ошибки. ДляExcel формулой.Есть еще одинУ нас такая в статистических формулах.Как сравнить даты в представить в формате=((ДЕНЬ(A1)Михаил С. же разницу, отличия?..  где разница во
 и заполнены соответствующие  темы с похожими
с воскресенья (по и др.). определена категория «ДатаФункция СЧЁТЕСЛИ подсчитывает число нас важно, чтобы
Можно сделать в
способ таблица с даннымиКопируем формулу внизExcel.
гггг.мм.дд. но ничегоКазанский: Даст неверный ответ ;-) времени будет больше столбцы, то индикация задачами, но кажется умолчанию).Способы измерения интервалов между и время». Рассмотрим ячеек в диапазоне эта статья была таблице дополнительный столбец
сравнить даты в Excel из магазинов. Как по столбцу. Для
У нас такой не получается. помогите,: Раз пошла такая для второй половиныps В правила! или равна 3 дней просрочки должна моих знаний неВ качестве второго аргумента заданными датами: наиболее распространенные функции B2:B7, значения которых
 вам полезна. Просим
и установить в- сравнить периоды  перенести данные из большей визуализации выделяем список с датами пожалуйста, найти ошибку. пьянка :) месяца, например 20.01.11
И пример - часам. пропадать. прилагаю файл хватит для доработки указана цифра 2.для отображения результата в в этой категории. меньше 9000 (4).
вас уделить пару ячейках этого столбца дат,есть ли в разных таблиц в
условным форматированием слова отпусков сотрудников. Файл прилагается. Версия=ВЫБОР((ДЕНЬ(A1) и 25.01.11. в студию!

excel-office.ru

Подсчет числа или даты, по условию

|19.09.2011 10:32| |19.09.2011 с примером. предложенных там решений Поэтому формула считает, днях – «d»;Программа Excel «воспринимает» дату=СЧЁТЕСЛИМН(B2:B7;»>=9000″;B2:B7;» секунд и сообщить, формулу. =А2=В2 указанных периодах одинаковые одну, читайте в «Истина» — наложениеУ Ивановой и Петрова excel 2007.С учетом месяцаможно такwebley 13:33|На форуме запрещено под мой случай. что неделя начинаетсяв месяцах – «m»;

и время какФункция СЧЁТЕСЛИМН (доступна в помогла ли онаПолучится так. даты и сколько статье «Как связать периодов дат. Получилась в периоде датPelena и года (хотя=(ДЕНЬ(A1) а тут: А лучше так:Serge_007 задавать новые вопросыИмеется пофамильный список с понедельника (второйв годах – «y»; обычное число. Электронная Excel 2007 и вам, с помощьюМожно условным форматированием окрасить дней в периодах

Пример

 таблицы в Excel»

 такая таблица.

 есть общие дни,

:

 ТС написал «мы

 получается, что все

=ЕСЛИ(И(ДЕНЬ(A1)

:

 в чужих темах.

 сотрудников с тремя

 день недели).

в месяцах без учета

 таблица преобразует подобные

 более поздних версиях

 кнопок внизу страницы.

 слова «Ложь» другим

 совпадают. Этот способ

тут.	Здесь идет сравнение верхней
т.е. в какие-то	belosnegka
имеем даты одного	даты в месяце
roka	200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(МАКС(A1:B1)-МИН(A1:B1)>=0,125;»3 или более часа»;»»)
Создайте свою	столбцами данных по
Скачать примеры функций для	лет – «ym»;
данные, приравнивая сутки	приложения) подсчитывает число
Для удобства также	цветом или окрасить
может пригодиться, чтобы	Первый способ.
даты в таблице	дни в отпуске, придумайте для темы года и месяца», равны, а должны
: Ну спасибо огромное	Гостьv_shchelkov датам проведения мед. работы с датами
в днях без учета	к единице. В ячеек в диапазоне приводим ссылку на эти ячейки. выявить пересечение датКак сравнить два столбца с нижней, п.э. будут сразу два более конкретное название но опыт показывает,
быть равны даты	всем: Спасибо, но я: Добрый день! комиссий. Флюра, аттестацияДля указания текущей даты месяцев и лет результате значение времени B2:B7, значения которых оригинал (на английскомМожно написать такую в периодах. Например, в
в третьей строке
сотрудника. Нам нужно
в соответствии с
что такая проверка
с 1 до
roka	немножко не точно
На форуме нашёл	и допуск должны используется функция СЕГОДНЯ – «md»; представляет собой долю
больше или равны	языке) . формулу в ячейке чтобы в отпускеExcel на совпадения. (у Петрова) стоит выявить у кого Правилами форума, иначе не бывает лишней 15 между собой
: Это как это?	описал суть вопроса. подобную тему но проводиться один раз (не имеет аргументов).в днях без учета
от единицы. К	9000 и меньшеПодсчет числа или даты, С2. =СУММ(ЕСЛИ(A2:A6<>B2:B6;1;0)) Нажимаем не было дваВыделяем столбцы (у «0», п.ч. период из сотрудников идет тему придётся закрыть. — человеческий фактор и с 16 01.01.2011 = 40544.00, Выделить нужно только она закрыта на в год. То Чтобы отобразить текущее

support.office.com

Функции для работы с датами в Excel: примеры использования

лет – «yd». примеру, 12.00 – или равны 22500 которые соответствуют одного «Enter». Копируем формулу сотрудника сразу или нас столбцы А

Как Excel обрабатывает время

Петрова сравнивается с накладка периода отпускаТем более, что и т.д.): по 31, а а 15.01.2011 = те строки у добавление вопросов, потому есть необходимо, чтобы время и дату,В некоторых версиях Excel

это 0,5. (4). условия (например, равно, по столбцу. Тогда даты учебы, оплаты и В). На периодом Сидорова. и на сколько вашу задачу можно=ВЫБОР((ДЕНЬ(A1) даты до 15 40558.00 Иль о

которых вторая ячейка создал новую. Вопрос даты, указанные в применяется функция ТДАТА при использовании последнихЗначение даты электронная таблица=СУММПРОИЗВ((B2:B7>=9000)*(B2:B7 больше, чем меньше,

в столбце с

Пример функции ДАТА

счетов, т.д. не закладке «Главная» нажимаемКак настроить ячейки

дней.

решить и безGIG_ant и с 16 чем другом речь больше чем 2 заключается в сравнении столбцах автоматически сравнивались (). двух аргументов («md», преобразует в число,Функция СУММПРОИЗВ подсчитывает число чем больше или

разницей будут стоять пересекались. Об этом на кнопку функции столбца D, чтобыСначала узнаем, у ЕСЛИ(): или так: между собой естественно — одно пишем часа 59 минут,

двух дат, а

с текущей датойzhekachan

«yd») функция может равное количеству дней ячеек в диапазоне

Функция РАЗНДАТ в Excel

равно или меньше цифры. Единица будет

способе читайте в

«Найти и выделить»,
ячейки окрашивались при
кого есть вbelosnegka=(ГОД(A1)=ГОД(B1))*(МЕСЯЦ(A1)=МЕСЯЦ(B1))*((ДЕНЬ(A1)

не равны{/post}{/quote}Вы пример два на ум

но только в именно:
и, если на
: Добрый вечер!
выдать ошибочное значение. от 1 января
B2:B7, значения которых или равно), используйте стоять, если есть
статье «Как сравнить выбираем функцию «Выделение

определенных условиях, смотрите периоде дат накладка: Pelena, тему изменила!ЗЫ: вот это post_190488.xls посмотрели? Даты пошло?.. «Сравнить» - большую сторону. Т.е.

Есть колонка Даты

текущую дату сМожете мне помочь Лучше применять альтернативные 1900 года (так больше или равны функцию

Функция ГОД в Excel

различия, а «нуль» даты в Excel». группы ячеек». в статье «Условное дат. Подскажите, пожалуйста, решение условие: (ДЕНЬ(A1) разные пробовали подставлять? выявить схожесть или совершилось ли какое-то создания транспортной заявки момента прохождения, например,

пожалуйста с проблемой!!

ГОД.

Функция МЕСЯЦ в Excel: пример

формулы. решили разработчики) до 9000 и меньшеСЧЁТЕСЛИ — данные вКак посчитать разницуВ появившемся окне ставим форматирование в Excel».В ячейке D2 на вопрос.GIG_ant

roka

Примеры функций ДЕНЬ, ДЕНЬНЕД и НОМНЕДЕЛИ в Excel

же разницу, отличия?.. действие в течении и реальная дата аттестации прошло болееВ первой таблицеПримеры действия функции РАЗНДАТ: заданной даты. Например, или равны 22500

. Для подсчета ячейках одинаковые. Получится дат, стаж, возраст, галочку у слов

Мы для этого пишем формулу.Pelena

: Проверьте на датах:: Даст неверный ответ ;-) трех часов или

загрузки. Нужно отобразить года, то ячейка есть дата иВ версии Excel 2007 при преобразовании даты

(4). Эту функцию числа или даты, так. как прибавить к «Отличия по строкам». выбрали функцию условного

=СУММПРОИЗВ((B2<>=$B$2:$B$4))>1: Во-первых, у Вас

02.01.2011 и 03.01.2011, для второй половиныps В правила! нет. все даты которые в столбцах с время и во

exceltable.com

Сравнение таблицы по времени и дате (Формулы/Formulas)

 данной функции нет 13.04.1987 получается число
можно использовать в которые входят в
Четвертый дате число, т.д., Нажимаем «ОК». форматирования — «СодержитВ этой формуле даты — не по моему что
месяца, например 20.01.11 И пример -MCH имеют разницу 1
датой прохождения аттестации второй тоже самое, в справочнике, но 31880. То есть Excel 2003 и
определенный диапазон (например,с смотрите в статье
В таблице выделились все

 текст» и написали, мы сравниваем дату даты, а текст. то не верно и 25.01.11. в студию!{/post}{/quote}: Тогда еще проще, день или менее автоматически выделялась цветом. но в первой она работает. Хотя
от 1.01.1900 прошло более ранних версиях, больше чем 9000пособ. «Дата в Excel. ячейки с разными какое слово нужно
из ячейки В2 Выделите столбцы А:СКазанскийможно такНу это нужно используйте в ЕСЛИ

 от времени созданияЕсли это возможно, таблице не все результаты лучше проверять,
31 880 дней.

 в которых функция на том же
Можно

excelworld.ru

Сравнение заданных дат с текущей и выделение просроченных (Формулы/Formulas)

 Формула» здесь. данными так. Excel окрашивать «Истина». (начало периода) с установите Краткий формат: Да, один нолик=(ДЕНЬ(A1) а тут для того, чтобы
условие B1-A1>=3/24 заявки, так чтобы то буду благодарна данные со второй!!! т.к. возможны огрехи.Этот принцип лежит в СЧЁТЕСЛИМН не поддерживается. время и меньше,объединить таблицы ExcelМожно сравнить числа. сравнила данные вПроще выявить количество датами столбца С даты, затем Ctrl+h был лишним: получается, что все сравнить даты первойГость был результат разницы за решение.
Как можна найтиВозвращает год как целое основе расчетов временных

Дата чем 22500), можно
с отчетами. ТогдаФункцию выделения ячеек можно
ячейках двух столбцов

 дней совпадений в (окончание периодов). А, — Найти .=ВЫБОР((ДЕНЬ(A1) даты в месяце половины и второй: Большое спасибо!!! Всё отображался в целых_Boroda_

 пропавшые данные с число (от 1900

 данных. Чтобы найти11.03.2011 использовать функцию все одинаковые данные вызвать клавишей F5. — данные ячейки периодах дат можно, затем, сравниваем дату (точку) — Заменить

Казанский равны, а должны половины месяца, т.е. работает. сутках (днях). Кроме: Так нужно? первой таблице во до 9999), который количество дней между01.01.2010

СЧЁТЕСЛИМН соберутся в одну В появившемся окне А2 и данными сделав отдельно табличку из ячейки С2
на . (точку): О! Я нашел быть равны даты если обе датыroka этого в форматеСмотрите условное форматирование. второй? соответствует заданной дате. двумя датами, достаточно
31.12.2010. Кроме того строку, но можно «Переход» нажимаем кнопку

excelworld.ru

Формула для Сравнение двух дат (Формулы)

 ячейки В2, т.д. с двумя периодами.
(окончание периода) с — Заменить всё. аналог ф-ии ИСКЛЮЧАЮЩЕЕ с 1 до одной половины месяца,: Добрый день! ячейки кроме даты Там формула
Грубо говоря что-бы В структуре функции от более позднего30.06.2010 можно использовать функцию будет посмотреть и «Выделить…».Если мы сейчас нажмём Например, так. датами из столбцаВо-вторых формулу можно ИЛИ: 15 между собой то одно условие,Помогите, плиз, сравнить присутствует время. Формулу200?’200px’:»+(this.scrollHeight+5)+’px’);»>=РАЗНДАТ(B2;СЕГОДНЯ();»y»)>0 время которого нету только один аргумент временного периода отнять
Формула СУММПРОИЗВ слишком. отдельно данные по

Второй способ. на мышь, то
Даты можно сортировать по В (начало периодов). записать так=СОВПАД(ДЕНЬ(A1) и с 16 если разных, то даты. чтобы убрать времяNikolь в первой таблице – дата в

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

200?’200px’:»+(this.scrollHeight+5)+’px’);»>=МИН(A2;C2)roka
по 31, а

 другоеТребуется формула для: и оставить дату
: Да! Пример был


excelworld.ru

Помогите сравнить дату и время и найти значения с разницей

 в Строке1 писалась числовом формате. Аргумент=СЧЁТЕСЛИ(B14:B17;»>01.03.2010″)2 сделать, смотрите вExcel сравнить и выделить Чтобы эти выделенные например, по дню
по столбцу. Получитсяbelosnegka

: СОВПАД=ОТПАД ;) даты до 15Guest

- если сравнивать знаю, но как формальный, объясните, пожалуйста, «1» должен быть введенПостроение значение даты, составляяПодсчитывает число ячеек в3 статье «Как объединить цветом ячеек оставить, мы рождения, по месяцам. так.: Pelena, пыталась установитьroka

 и с 16: Даст неверный ответ даты в диапазоне правильно сравнить даты

 как это сделано,Очень прошу о посредством функции ДАТА

excelworld.ru

сравнить даты

 его из отдельных диапазоне B14:B17, в
4 таблицы в Excel».
ячейки с разными данными
можем закрасить эти Как сортировать даты,Слово «Истина» говорит о краткий формат дат,: СОВПАД=ОТПАД ;){/post}{/quote} между собой естественно
для второй половины с 1 по не выходит. чтобы можно было помощи, туплю уже или представлять результат
элементов-чисел. которых указана дата5Пятый способ.с помощью условного ячейки или изменить читайте в статье
том, что в

 никаких изменений невот тут тока

не равны{/post}{/quote}Вы пример месяца, например 20.01.11

 15 число одногоСпасибо всем заранее,

 тоже самое проделать второй день!
вычисления других формул.

Синтаксис: год; месяц, день. после 01.03.2010 (3).6Используем форматирования. цвет шрифта в «Как сделать сортировку этом периоде есть произошло. Делала я опять надо год post_190488.xls посмотрели? Даты и 25.01.11.
месяца и года, файл с примером с реальными данными.

Спасибо за внимание!Пример использования функции ГОД:
Все аргументы обязательные. Их

=СЧЁТЕСЛИ(B14:B17;»31.12.2010″)7функцию «СЧЕТЕСЛИ» в

Итак, мы выделяем этих ячейках функциями в Excel по даты, которые повторяются так: выделила столбец и месяц учитывать разные пробовали подставлять?{/post}{/quote}можно так то они равны, в приложении. Спасибо большое заChe79
Возвращает месяц как целое можно задать числамиПодсчитывает число ячеек в

8Excel столбцы с данными раздела «Шрифт» на дате». в другом периоде. — формат ячеек16.02.2010 30.03.2010да, конечно,=(ДЕНЬ(A1)

- если сравниватьPelena отклик.: число (от 1
или ссылками на
диапазоне B14:B17 с

9. Эта функция посчитает (без названия столбцов). закладке «Главная», покаЕсть несколько способов,

Слово «Ложь» говорит — дата -

rokaпосмотрите строку 3

webley даты диапазона 1-15: Здравствуйте.Nikolь

zhekachan до 12) для ячейки с соответствующими датой 31.12.2010 (1).10
количество повторов данных
На закладке «Главная» эти ячейки выделены.как сравнить два столбца о том, что и выбирала формат: СОВПАД=ОТПАД ;){/post}{/quote}вот пример: а в условии и 16-31 одногоv_shchelkov: Спасибо, буду пробовать!, здравствуйте. Проверяйте, так заданной в числовом

 числовыми данными: для Знак равенства в11 их первого столбца, в разделе «Стили»
Например, так.
в Excel на дат, которые в 14.3.01. Пробовала копироватьвот тут опять01.02.2011 25.02.2011 Не про сравнение внутри месяца и года,, покажите в файле,Nikolь хотите? В столбце формате даты. Аргумент года – от условии не обязателен12 сравнив их с

 нажимаем на кнопкуИли так. совпадения этом периоде нет столбец через специальную
надо месяц и
равны ИСТИНА второй половины месяца то они не в каком виде: С формула массива – дата месяца, 1900 до 9999; и поэтому не13 данными второго столбца. «Условное форматирование». ИзСравнить данные в нескольких, в других периодах
ставку — значения
год учитывать:
ИСТИНА — а

ничего не сказано… равны
Вы хотите получить_Boroda_

 (ввод одновременно Ctrl+Shift+Enter) который необходимо отобразить,
для месяца – указывается (формула будет
14В дополнительном столбце
появившегося списка выбираем

 столбцахкак сравнить две таблицы
дат.

 и снова менять16.02.2010 30.03.2010
должно быть ЛОЖЬ

Михаил С.В общем если результат (несколько строк).

, подскажите пожалуйста, почему=—ЕНД(ПОИСКПОЗ(ОКРУГЛ($E3&$F3;5);ОКРУГЛ($A$3:$A$148&$B$3:$B$148;5);)) в числовом формате.
от 1 до работать и со
15 устанавливаем формулы, они

 функцию «Создать правило».Excel. Excel
Другой способ сравнения

 формат. Не получается.belosnegkaroka

: >
мы имеем даты Не совсем понятно, во втором правиле+ ФЯ Даты в текстовом 12; для дня знаком равенства («=31.12.2010»).16 разные с разнымиВ строке «Формат…» пишем

Здесь мы сравнили

, дат в Excel
Я делаю что-то
: Добрый вечер!: должно быть так:

webley одного года и каким образом «отобразить» форматирования указан диапазон0;;;

 формате функция обрабатывает – от 1=СЧЁТЕСЛИМН(B14:B17;»>=01.01.2010″;B14:B17;»

 условиями. Или в такую формулу. =$А2<>$В2. три столбца всписки

смотрите в статье

 не так?Даны две даты,

01.03.2011 до 15.03.2011: ну может…
месяца, то нужно и как результат применения именно $D$9?

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

planetaexcel.ru

Сравнение дат происходит неправильно (Формулы/Formulas)

 таблице, предварительно выделив,
«Как сравнить дваPelena которые необходимо сравнить: ИСТИНАroka сравнить даты первой разницы может быть Какой диапазон указывать,еслиРезультат 1 (ИСТИНА)Примеры использования функции МЕСЯЦ:Если для аргумента «День» диапазоне B14:B17 сB данные, слова, которые
говорим Excel, что все три столбцадаты в Excel столбца в Excel: Ну а дальше-то дата в 116.03.2011 до 31.03.2011: мне твоего решения и второй половины «в целых сутках», список значительно длиннее? показывает какой даты/времениВозвращает день как целое задать большее число датами в интервалеПродавец

 нужно посчитать в если данные в таблицы. Сравниваются данные. Не только сравнить на совпадение». Этимbelosnegka столбце и дата ИСТИНА
пока было достаточно, месяца. если даты_Boroda_

 из таблицы 2 число (от 1 (чем количество дней от 01.01.2010 до

Счет столбце. ячейках столбца А в ячейках построчно столбцы в Excel, способом выделяются даты: Pelena, все получилось! в 3 столбце,05.03.2011 25.03.2011 ЛОЖЬ а там посмотримСпасибо
v_shchelkov: А это не
нет в таблице

 до 31) для в указанном месяце), 31.12.2010 включительно (3).ЕгоровПодробнее смотрите такие не равны данным (А2, В2, С2,т.д.). но и выделить во втором столбце, Спасибо за помощь! если дата вМихаил С.rokaКазанский: Pelena, просто мне мое правило, оно

 1. Зелёная заливка заданной в числовом

 то лишние дни=СУММПРОИЗВ((B14:B17>=ДАТАЗНАЧ(«01.01.2010»))*(B14:B1715 000

excelworld.ru

формулы в статье

Источник

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

Содержание

Обработка времени в табличном редакторе
Использование оператора ДАТА в табличном редакторе
Использование оператора РАЗНДАТ в табличном редакторе
Применение оператора ГОД в табличном редакторе
Применение оператора МЕСЯЦ в табличном редакторе
Примеры применения операторов ДЕНЬ, ДЕНЬНЕД и НОМНЕДЕЛИ в табличном редакторе
Заключение и выводы о сравнении дат в табличном редакторе

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

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

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

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

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

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

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

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

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

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

«d» — выводит итоговый показатель в сутках;
«m» — выводит итоговый показатель в месяцах;
«y» — выводит итоговый показатель в годах;
«ym» — выводит итоговый показатель в месяцах без учета годов;
«md» — выводит итоговый показатель в сутках без учета годов и месяцев;
«yd» — выводит итоговый показатель в сутках без учета годов.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Оцените качество статьи. Нам важно ваше мнение:

Источник

Добрый день.
В одну ячейку вставил текущую дату «ctrl+;»
Есть столбец с датами (сроками). Нужно сравнить их с текущей датой и отдельным столбцом вывести результат «срок истек» или «срок действителен».
Вот формула:
=ЕСЛИ(датазнач(D4)>L2;»Действительна»;»Истекла»)
Все считает нормально, но если копирую формулу, сбивается значение.
Во второй строке получилось так:
=ЕСЛИ(—D5>L2;»Действительна»;»Истекла»)
В третьей беда «ошибка в значении».

Попробовал дома.
Вроде заработало.
Но непонятно как и почему.
Оба файла.

в обоих ошибки
для «Дата11 работает»

=ЕСЛИ(A2>$F$1;"действительно";"просрочено")для «Дата11 не работает»

=ЕСЛИ(C6>$F$2;"Действительна";"Истекла")

Отдельное спасибо можно на QiWi-кошелек 909-771-53-87 или ЯД 41001136675053

Заменил на $F$2, не работает.

в файле «Дата11 не работает» в C4 не дата, а текст. Чтобы даты текстом преобразовывались в дату используйте формулу:

=ЕСЛИ(C4-$F$2>0;"Действительна";"Истекла")

PS: ну а даты 99.99.9999 не бывает, это текст, который не преобразовывается в дату

Источник

Содержание

1 Как Excel обрабатывает время
- 1.1 Пример функции ДАТА
- 1.2 Функция РАЗНДАТ в Excel
- 1.3 Функция ГОД в Excel
- 1.4 Функция МЕСЯЦ в Excel: пример
- 1.5 Примеры функций ДЕНЬ, ДЕНЬНЕД и НОМНЕДЕЛИ в Excel
2 Excel сравнение дат
- 2.1 Видео
- 2.2 Как вводить даты и время в Excel
- 2.3 Быстрый ввод дат и времени
- 2.4 Как Excel на самом деле хранит и обрабатывает даты и время
- 2.5 Количество дней между двумя датами
- 2.6 Количество рабочих дней между двумя датами
- 2.7 Количество полных лет, месяцев и дней между датами. Возраст в годах. Стаж.
- 2.8 Сдвиг даты на заданное количество дней
- 2.9 Сдвиг даты на заданное количество рабочих дней
- 2.10 Вычисление дня недели
- 2.11 Вычисление временных интервалов
- 2.12 Ссылки по теме

Для работы с датами в Excel в разделе с функциями определена категория «Дата и время». Рассмотрим наиболее распространенные функции в этой категории.

Программа Excel «воспринимает» дату и время как обычное число. Электронная таблица преобразует подобные данные, приравнивая сутки к единице. В результате значение времени представляет собой долю от единицы. К примеру, 12.00 – это 0,5.

Значение даты электронная таблица преобразует в число, равное количеству дней от 1 января 1900 года (так решили разработчики) до заданной даты. Например, при преобразовании даты 13.04.1987 получается число 31880. То есть от 1.01.1900 прошло 31 880 дней.

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

Пример функции ДАТА

Построение значение даты, составляя его из отдельных элементов-чисел.

Синтаксис: год; месяц, день.

Все аргументы обязательные. Их можно задать числами или ссылками на ячейки с соответствующими числовыми данными: для года – от 1900 до 9999; для месяца – от 1 до 12; для дня – от 1 до 31.

Если для аргумента «День» задать большее число (чем количество дней в указанном месяце), то лишние дни перейдут на следующий месяц. Например, указав для декабря 32 дня, получим в результате 1 января.

Пример использования функции:

Зададим большее количество дней для июня:

Примеры использования в качестве аргументов ссылок на ячейки:

Функция РАЗНДАТ в Excel

Возвращает разницу между двумя датами.

Аргументы:

начальная дата;
конечная дата;
код, обозначающий единицы подсчета (дни, месяцы, годы и др.).

Способы измерения интервалов между заданными датами:

для отображения результата в днях – «d»;
в месяцах – «m»;
в годах – «y»;
в месяцах без учета лет – «ym»;
в днях без учета месяцев и лет – «md»;
в днях без учета лет – «yd».

В некоторых версиях Excel при использовании последних двух аргументов («md», «yd») функция может выдать ошибочное значение. Лучше применять альтернативные формулы.

Примеры действия функции РАЗНДАТ:

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

Функция ГОД в Excel

Возвращает год как целое число (от 1900 до 9999), который соответствует заданной дате. В структуре функции только один аргумент – дата в числовом формате. Аргумент должен быть введен посредством функции ДАТА или представлять результат вычисления других формул.

Пример использования функции ГОД:

Функция МЕСЯЦ в Excel: пример

Возвращает месяц как целое число (от 1 до 12) для заданной в числовом формате даты. Аргумент – дата месяца, который необходимо отобразить, в числовом формате. Даты в текстовом формате функция обрабатывает неправильно.

Примеры использования функции МЕСЯЦ:

Примеры функций ДЕНЬ, ДЕНЬНЕД и НОМНЕДЕЛИ в Excel

Возвращает день как целое число (от 1 до 31) для заданной в числовом формате даты. Аргумент – дата дня, который нужно найти, в числовом формате.

Чтобы вернуть порядковый номер дня недели для указанной даты, можно применить функцию ДЕНЬНЕД:

По умолчанию функция считает воскресенье первым днем недели.

Для отображения порядкового номера недели для указанной даты применяется функция НОМНЕДЕЛИ:

Дата 24.05.2015 приходится на 22 неделю в году. Неделя начинается с воскресенья (по умолчанию).

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

Скачать примеры функций для работы с датами

Для указания текущей даты используется функция СЕГОДНЯ (не имеет аргументов). Чтобы отобразить текущее время и дату, применяется функция ТДАТА ().

Excel сравнение дат

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

Ответ от Abram Pupkin
вы изначально поставили неправильно вопрос. возмем 2-е даты: например 02.01.1990 и 25.11.2010 как надо сравнить эти даты : по расположению (справа/слева) , какая дата больше, сколько прошло дней (недель, месяцев) , сколько осталось до

Видео

Как обычно, кому надо быстро — смотрим видео. Подробности и нюансы — в тексте ниже:

Как вводить даты и время в Excel

Если иметь ввиду российские региональные настройки, то Excel позволяет вводить дату очень разными способами — и понимает их все:

«Классическая» форма	3.10.2006
Сокращенная форма	3.10.06
С использованием дефисов	3-10-6
С использованием дроби	3/10/6

Внешний вид (отображение) даты в ячейке может быть очень разным (с годом или без, месяц числом или словом и т.д.) и задается через контекстное меню — правой кнопкой мыши по ячейке и далее Формат ячеек (Format Cells):

Время вводится в ячейки с использованием двоеточия. Например

16:45

По желанию можно дополнительно уточнить количество секунд — вводя их также через двоеточие:

16:45:30

И, наконец, никто не запрещает указывать дату и время сразу вместе через пробел, то есть

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)

Из всех этих фактов следуют два чисто практических вывода:

Во-первых, Excel не умеет работать (без дополнительных настроек) с датами ранее 1 января 1900 года. Но это мы переживем! 😉
Во-вторых, с датами и временем в Excel возможно выполнять любые математические операции. Именно потому, что на самом деле они — числа! А вот это уже раскрывает перед пользователем массу возможностей.

Количество дней между двумя датами

Считается простым вычитанием — из конечной даты вычитаем начальную и переводим результат в Общий (General) числовой формат, чтобы показать разницу в днях:

Количество рабочих дней между двумя датами

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

Примечание: Эта функция появилась в стандартном наборе функций Excel начиная с 2007 версии. В более древних версиях сначала необходимо подключить надстройку Пакета анализа. Для этого идем в меню Сервис — Надстройки (Tools — Add-Ins) и ставим галочку напротив Пакет анализа (Analisys Toolpak). После этого в Мастере функций в категории Дата и время появится необходимая нам функция ЧИСТРАБДНИ (NETWORKDAYS).

Количество полных лет, месяцев и дней между датами. Возраст в годах. Стаж.

Про то, как это правильно вычислять, лучше почитать тут.

Сдвиг даты на заданное количество дней

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

Сдвиг даты на заданное количество рабочих дней

Эту операцию осуществляет функция РАБДЕНЬ (WORKDAY). Она позволяет вычислить дату, отстоящую вперед или назад относительно начальной даты на нужное количество рабочих дней (с учетом выходных суббот и воскресений и государственных праздинков). Использование этой функции полностью аналогично применению функции ЧИСТРАБДНИ (NETWORKDAYS) описанной выше.

Вычисление дня недели

Вас не в понедельник родили? Нет? Уверены? Можно легко проверить при помощи функции ДЕНЬНЕД (WEEKDAY) из категории Дата и время.

Первый аргумент этой функции — ячейка с датой, второй — тип отсчета дней недели (самый удобный — 2).

Вычисление временных интервалов

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

Нюанс здесь только один. Если при сложении нескольких временных интервалов сумма получилась больше 24 часов, то Excel обнулит ее и начнет суммировать опять с нуля. Чтобы этого не происходило, нужно применить к итоговой ячейке формат 37:30:55:

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

Как вычислять возраст (стаж) в полных годах-месяцах-днях
Как сделать выпадающий календарь для быстрого ввода любой даты в любую ячейку.
Автоматическое добавление текущей даты в ячейку при вводе данных.
Как вычислить дату второго воскресенья февраля 2007 года и т.п.

Источник

Вычисление разности двух дат

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

Предупреждение: Если значение нач_дата больше значения кон_дата, возникнет ошибка #ЧИСЛО!

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

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

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

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

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

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

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

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

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

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

1. Используйте функцию РАЗНДАТ, чтобы найти общее количество лет.

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

2. Снова используйте функцию РАЗНДАТ с «гм», чтобы найти месяцы.

В другой ячейке используйте функцию РАЗНДАТ с параметром «гм». Параметр «гм» возвращает количество оставшихся месяцев с последнего полного года.

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 г.

Используйте функцию ЧИСТРАБДНИ.МЕЖД, если нужно вычислить количество рабочих дней между двумя датами. Вы также можете исключить выходные и праздники.

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

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

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

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

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

Примечание. В Excel 2007 нет функции ЧИСТРАБДНИ.МЕЖД. Однако там есть функция ЧИСТРАБДНИ. Указанный выше пример будет выглядеть в Excel 2007 следующим образом: =ЧИСТРАБДНИ(D53;E53). Не нужно указывать цифру 1, так как функция ЧИСТРАБДНИ предполагает, что выходными являются суббота и воскресенье.

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

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

Если вы используете Excel 2007, пропустите этот шаг. Функция ЧИСТРАБДНИ в Excel 2007 всегда предполагает, что выходными являются суббота и воскресенье.

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

Если вы создали имя диапазона праздников в разделе «Прежде чем начать» выше, введите его в конце следующим образом. Если у вас нет праздников, вы можете не использовать точку с запятой и МоиПраздники. Если вы используете Excel 2007, указанный выше пример будет выглядеть так: =ЧИСТРАБДНИ(D53;E53;MyHolidays).

Совет. Если вы не хотите указывать имя диапазона праздников, вместо этого вы можете ввести диапазон, например D35:E39. Или можно ввести в формулу каждый праздник. Например, если ваши праздники приходились на 1 и 2 января 2016 г., введите их следующим образом: =ЧИСТРАБДНИ.МЕЖД(D53;E53;1;{«01.01.2016″;»02.01.2016»}). В Excel 2007 это будет выглядеть так: =ЧИСТРАБДНИ(D53;E53;{«01.01.2016″;»02.01.2016»})

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

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

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

2. Установите формат «ч:мм AM/PM».

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

3. Вычтите два времени.

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

4. Установите формат «ч:мм».

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

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

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

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

2. Установите формат «14.03.12 1:30 PM».

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

3. Вычтите два значения.

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

4. Установите формат «[ч]:мм».

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

Статьи по теме

Функция РАЗНДАТ

Функция ЧИСТРАБДНИ.МЕЖД

ЧИСТРАБДНИ

Дополнительные функции даты и времени

Вычисление разницы во времени

Нужна дополнительная помощь?

Источник

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

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

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

Как посчитать разницу во времени в Excel (прошедшее время)
Вычесть одно время из другого
Вычисление разницы во времени с помощью функции ТЕКСТ
Как сосчитать часы, минуты или секунды
Как посчитать разницу в каждой единице времени.
Как перевести секунды в часы, минуты и секунды
Расчет времени с момента начала до настоящего момента
Отображение разницы во времени как «XX дней, XX часов, XX минут и XX секунд».
Как рассчитать и отобразить отрицательное время в Excel
Сложение и вычитание времени в Excel
Как добавить или вычесть часы
Как прибавить или вычесть минуты
Как прибавить или вычесть секунды
Как суммировать время в Excel

Как посчитать разницу во времени в Excel (прошедшее время)

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

Вычесть одно время из другого

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

Самая простая и очевидная формула Excel чтобы посчитать время от одного момента до другого:

= Время окончания — Время начала

В зависимости от ваших данных, формула разницы во времени может принимать различные формы, например:

Формула	Пояснение
=A2-B2	Вычисляет разницу между временами в ячейках A2 и B2.
=ВРЕМЗНАЧ(«21:30») — ВРЕМЗНАЧ («8:40»)	Вычисляет разницу между указанными моментами времени, которые записаны как текст.
=ВРЕМЯ(ЧАС(A2); МИНУТЫ(A2); СЕКУНДЫ(A2)) — ВРЕМЯ (ЧАС (B2); МИНУТЫ (B2); СЕКУНДЫ (B2))	Вычисляет разницу во времени между значениями в ячейках A2 и B2, игнорируя разницу в датах, когда ячейки содержат значения даты и времени.

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

В зависимости оп применяемого форматирования, в столбце D вы можете увидеть десятичные дроби (если установлен формат Общий). Чтобы сделать результаты более информативными, вы можете настроить отображаемый формат времени с помощью одного из следующих шаблонов:

Формат	Объяснение
ч	Прошедшее время отображается только в часах, например: 4.
ч:мм	Прошедшие часы и минуты отображаются в формате 4:50.
ч:мм:сс	Прошедшие часы, минуты и секунды отображаются в формате 4:50:15.

Чтобы применить пользовательский формат времени, используйте комбинацию клавиш Ctrl + 1, чтобы открыть диалоговое окно «Формат ячеек», выберите «Пользовательский» и введите шаблон формата в поле «Тип». Подробные инструкции вы можете найти в статье Создание пользовательского формата времени в Excel .

А теперь давайте разберем это на простых примерах. Если время начала находится в столбце B, а время окончания — в столбце A, вы можете использовать эту простую формулу:

=$A2-$B2

Прошедшее время отображается по-разному в зависимости от использованного формата времени, как видно на этом скриншоте:

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

Вычисление разницы во времени с помощью функции ТЕКСТ

Еще один простой метод расчета продолжительности между двумя временами в Excel — применение функции ТЕКСТ:

Рассчитать часы между двумя временами: =ТЕКСТ(A2-B2; «ч»)
Рассчитать часы и минуты: =ТЕКСТ(A2-B2;»ч:мм»)
Посчитать часы, минуты и секунды: =ТЕКСТ(A2-B2;»ч:мм:сс»)

Как видно на скриншоте ниже, вы сразу получаете время в нужном вам формате. Специально устанавливать пользовательский формат ячейки не нужно.

Примечание:

Значение, возвращаемое функцией ТЕКСТ, всегда является текстом. Обратите внимание на выравнивание по левому краю содержимого столбцов C:E на скриншоте выше. В некоторых случаях это может быть существенным ограничением, поскольку вы не сможете использовать полученное «текстовое время» в других вычислениях.
Если результатом является отрицательное число, ТЕКСТ возвращает ошибку #ЗНАЧ!.

Как сосчитать часы, минуты или секунды

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

Как рассчитать количество часов.

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

=( Время окончания — Время начала ) * 24

Если начальное время записано в A2, а время окончания – в B2, вы можете использовать простое выражение B2-A2, чтобы вычислить разницу между ними, а затем умножить результат на 24. Это даст количество часов:

=(B2-A2)* 24

Чтобы получить количество полных часов, используйте функцию ЦЕЛОЕ, чтобы округлить результат до ближайшего целого числа:

=ЦЕЛОЕ((B2-A2) * 24)

Пример расчета разницы во времени только в одной единице измерения вы видите на скриншоте ниже.

Считаем сколько минут в интервале времени.

Чтобы вычислить количество минут между двумя метками времени, умножьте разницу между ними на 1440, что соответствует количеству минут в одном дне (24 часа * 60 минут = 1440).

=( Время окончания — Время начала ) * 1440

Как показано на скриншоте выше, формула может возвращать как положительные, так и отрицательные значения. Отрицательные возникают, когда время окончания меньше времени начала, как в строке 5:

=(B2-A2)*1440

Как сосчитать количество секунд.

Чтобы посчитать разницу в секундах между двумя моментами времени, вы умножаете разницу во времени на 86400, что соответствует числу секунд в одном дне (24 часа * 60 минут * 60 секунд = 86400).

=( Время окончания — Время начала ) * 86400

В нашем примере расчет выглядит следующим образом:

=(B2-A2)* 86400

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

Как посчитать разницу в каждой единице времени.

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

Разница в часах без учета минут и секунд:

=ЧАС(B2-A2)

Разница в минутах без учета часов и секунд:

=МИНУТЫ(B2-A2)

Разница в секундах без учета часов и минут:

=СЕКУНДЫ(B2-A2)

При использовании функций Excel ЧАС, МИНУТЫ и СЕКУНДЫ помните, что результат не может превышать 24 для часов, и 60 – для минут и секунд.

В данном случае не играет роли, сколько дней прошло с даты начала до даты окончания. Учитывается только дробная часть числа, то есть время.

Примечание. Однако,если в ячейку вы записали не просто время, а дату и время, и при этом дата окончания окажется меньше даты начала (т. е. результатом будет отрицательное число), то вы увидите ошибку #ЧИСЛО!.

Как перевести секунды в часы, минуты и секунды

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

Давайте рассмотрим небольшой пример.

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

Вот как это будет:

Дни:

=ЦЕЛОЕ(A2/(60*60*24))

Часы:

=ЦЕЛОЕ(A2/(60*60)) — ЦЕЛОЕ(A2/(60*60*24))*24

Минуты:

=ЦЕЛОЕ(A2/60) — ЦЕЛОЕ(A2/(60*60))*60

Секунды – это просто две последние цифры:

=—ПРАВСИМВ(A2;2)

Если нужно рассчитать время только в часах, минутах и секундах, то изменим формулу подсчета часов:

=ЦЕЛОЕ(A2/(60*60))

Конечно, здесь результат может быть больше 24.

Минуты и секунды подсчитываем, как и прежде.

Еще один вариант перевода секунд в дни, часы, минуты и секунды вы можете посмотреть на скриншоте ниже.

Расчет времени с момента начала до настоящего момента

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

Предположим, что начальная дата и время находятся в A2, выражение =ТДАТА()-A2 возвращает следующие результаты, при условии, что вы применили соответствующий формат времени к столбцу B (в этом примере «ч:мм») :

Если прошло больше чем 24 часа, используйте один из этих форматов времени , например Д «дн.» ч:мм:сс, как показано на скриншоте ниже:

Если ваши начальные данные содержат только время без дат, вам нужно использовать функцию ВРЕМЯ, чтобы правильно рассчитать, сколько времени прошло. Например, следующая формула возвращает время, истекшее с момента, указанного в ячейке A2, и до текущего момента:

=ВРЕМЯ(ЧАС(ТДАТА()); МИНУТЫ(ТДАТА()); СЕКУНДЫ(ТДАТА())) — A2

Примечание. Прошедшее время не обновляется в режиме реального времени, оно заново рассчитывается только при повторном открытии или пересчете рабочей книги. Чтобы принудительно обновить результат, нажмите либо Shift + F9, чтобы пересчитать активную таблицу, или F9 для пересчета всех открытых книг. Еще один способ быстрого пересчета – внесите изменения в любую ячейку вашего рабочего листа.

Отображение разницы во времени как «XX дней, XX часов, XX минут и XX секунд».

Это, вероятно, самая удобная формула для расчета разницы во времени в Excel. Вы используете функции ЧАС, МИНУТЫ и СЕКУНДЫ для возврата соответствующих единиц времени, и функцию ЦЕЛОЕ для вычисления разницы в днях. Затем вы объединяете все эти функции в одно выражение вместе с текстовыми пояснениями:

=ЦЕЛОЕ(B2-A2)&» дн., «&ЧАС(B2-A2)&» час., «&МИНУТЫ(B2-A2)&» мин. и «&СЕКУНДЫ(B2-A2)&» сек.»

Вот как это может выглядеть:

Чтобы скрыть нулевые результаты в формуле разницы во времени в Excel, встройте в нее четыре функции ЕСЛИ:

=ЕСЛИ(ЦЕЛОЕ(B2-A2)>0; ЦЕЛОЕ(B2-A2) & » дн., «;»») &

ЕСЛИ(ЧАС(B2-A2)>0; ЧАС(B2-A2) & » час., «;»») &

ЕСЛИ(МИНУТЫ(B2-A2)>0; МИНУТЫ(B2-A2) & » мин. и «;»») &

ЕСЛИ(СЕКУНДЫ(B2-A2)>0; СЕКУНДЫ(B2-A2) & » сек.»;»»)

Синтаксис может показаться чрезмерно сложным, но это работает

Кроме того, вы можете рассчитать разницу во времени, просто вычитая время начала из времени окончания (например =B2-A2 ), а затем применив к ячейке следующий формат времени:

Д «дн.,» ч «час.,» м «мин. и » с «сек.»

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

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

Как рассчитать и отобразить отрицательное время в Excel

При расчете разницы во времени в Excel вы иногда можете получить результат в виде ошибки ######, если вдруг она представляет собой отрицательное число. Это происходит, если время начала больше, чем время окончания события. Такое часто случается, если рассчитывают продолжительность каких-то событий без учета даты. Получается, что время окончания меньше, чем время начала, поскольку это просто время следующего дня. К примеру, мы начинаем работу в 17 часов и заканчиваем на следующий день в 10 часов.

Но есть ли способ правильно отображать отрицательное время в Excel? Конечно, способ есть, и даже не один.

Способ 1. Измените систему дат Excel на систему дат 1904 года.

Самый быстрый и простой способ нормально отображать отрицательное время (со знаком минус) — это перейти на систему дат 1904 года. Для этого нажмите « Файл» > «Параметры» > «Дополнительно », прокрутите вниз до раздела « При расчете этой книги» и поставьте галочку в поле «Использовать систему дат 1904».

Нажмите OK , чтобы сохранить новые настройки, и теперь отрицательные величины времени будут отображаться правильно, как отрицательные числа:

Как видите, время начала больше, чем время окончания, и результат получен со знаком «минус».

Способ 2. Рассчитать отрицательное время в Excel с помощью формул

Если изменить систему дат по умолчанию в Excel нецелесообразно, вы можете заставить отрицательное время отображаться правильно, используя одну из следующих формул:

=ЕСЛИ(A2-B2>0; A2-B2; «-» & ТЕКСТ(ABS(A2-B2);»ч:мм»))

=ЕСЛИ(A2-B2>0; A2-B2; ТЕКСТ(ABS(A2-B2);»-ч:мм»))

Они обе проверяют, является ли отрицательной разница во времени (A2-B2). Если она меньше нуля, первая формула вычисляет абсолютное значение (без учёта знака) и объединяет этот результат со знаком «минус». Вторая формула делает точно такой же расчет, но использует отрицательный формат времени «-ч::мм «.

Примечание. Имейте в виду, что в отличие от первого метода, который обрабатывает отрицательное время как отрицательное число, результатом функции ТЕКСТ всегда является текстовая строка, которую нельзя использовать в математических вычислениях.

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

На случай если время окончания меньше, чем время начала, мы можем проверить это при помощи функции ЕСЛИ.

=ЕСЛИ(B2<A2;B2+1;B2)-A2

Если разница во времени отрицательная, ко времени окончания добавляем 1 день (24 часа). В результате получим правильную разницу во времени, но без знака «минус».

Сложение и вычитание времени в Excel

По сути, есть 2 способа сложения и вычитания времени в Excel:

При помощи функции ВРЕМЯ.
Применяя арифметические вычисления, основанные на количестве часов (24), минут (1440) и секунд (86400) в одних сутках.

Функция ВРЕМЯ(часы; минуты; секунды) делает вычисления времени в Excel очень простыми, однако не позволяет добавлять или вычитать более 23 часов, 59 минут или 59 секунд.

Если вы работаете с большими временными интервалами, то используйте один из способов, описанных ниже.

Как добавить или вычесть часы

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

Функция ВРЕМЯ для добавления до 24 часов

= Время начала + ВРЕМЯ( N часов ; 0 ; 0)

Например, если ваше время начала записано в ячейке A2, и вы хотите добавить к нему 2 часа, формула выглядит следующим образом:

=A2 + ВРЕМЯ(4; 0; 0)

Примечание. Если вы попытаетесь добавить более 23 часов с помощью функции ВРЕМЯ, указанные часы будут разделены на 24, а остаток от деления будет добавлен к времени начала. Например, если вы попытаетесь добавить 28 часов к «10:00» (ячейка A2) с помощью формулы =A4 + ВРЕМЯ(28; 0; 0), результатом будет «14:00», т. е. A2 + 4 часа.

Как добавить любое количество часов (меньше или больше 24 часов)

Следующая формула не имеет ограничений на количество часов, которые вы хотите добавить:

= Время начала + ( N часов / 24)

Например, чтобы добавить 36 часов к времени начала в ячейке A2:

=A2 + (36/24)

Чтобы вычесть часы из заданного времени, вы используете аналогичные формулы и просто заменяете «+» знаком «-»:

Например, чтобы вычесть 40 часов из времени в ячейке A2, можно употребить формулу:

=A2-(40/24)

Если вычитаем менее чем 24 часа, то используйте функцию ВРЕМЯ:

=A2 — ВРЕМЯ(4; 0; 0)

Как прибавить или вычесть минуты

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

Чтобы добавить или вычесть менее 60 минут

Используйте функцию ВРЕМЯ и укажите минуты, которые вы хотите добавить или вычесть, во втором аргументе:

= Время начала + ВРЕМЯ(0; N минут ; 0)

Вот несколько примеров для расчета минут в Excel:

Чтобы добавить 30 минут ко времени в A2: =A2 + ВРЕМЯ(0;30;0)

Чтобы вычесть 50 минут из времени в A2: =A2 — ВРЕМЯ(0;50;0)

Как добавить или вычесть более 60 минут

При расчете разделите количество минут на 1440, то есть на количество минут в сутках, и прибавьте получившееся число к времени начала:

= Время начала + ( N минут / 1440)

Чтобы вычесть минуты из времени, просто замените плюс знаком минус. Например:

Чтобы добавить 600 минут: =A2 + (600/1440)

Чтобы вычесть 600 минут: =A2 — (600/1440)

Как прибавить или вычесть секунды

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

Чтобы добавить менее 60 секунд к заданному времени, вы можете использовать функцию ВРЕМЯ:

= Время начала + ВРЕМЯ(0; 0; N секунд )

Чтобы добавить более 59 секунд, используйте следующую формулу:

= Время начала + ( N секунд / 86400)

86400 – это количество секунд в сутках.

Чтобы вычесть секунды, используйте те же формулы, только со знаком минус (-) вместо плюса (+).

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

Чтобы добавить 50 секунд к A2: =A2 + ВРЕМЯ(0;0;50)

Чтобы добавить 1500 секунд к A2: =A2 + (1500/86400)

Чтобы вычесть 25 секунд из A2: =A2 — ВРЕМЯ(0;0;25)

Чтобы вычесть 2500 секунд из A2: =A2 — (2500/86400)

Примечание:

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

Как суммировать время в Excel

Формула суммирования времени в Excel — это обычная функция СУММ, и применение правильного формата времени к результату — это то, что помогает получить верный результат.

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

=СУММ(B2:B8)

Затем устанавливаете в ячейке нужный формат времении получаете результат, как на скриншоте ниже.

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

Щелкните правой кнопкой мыши ячейку и выберите «Формат ячеек» в контекстном меню или нажмите Ctrl + 1, чтобы открыть диалоговое окно «Формат ячеек». Выберите «Пользовательский» и введите один из следующих форматов времени в поле «Тип»:

Чтобы отобразить общее время в часах и минутах: [ч]:мм
Чтобы отобразить общее время в минутах и секундах: [м]:сс
Чтобы отобразить общее время в секундах: [сс]

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

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

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

Как перевести время в число — В статье рассмотрены различные способы преобразования времени в десятичное число в Excel. Вы найдете множество формул для преобразования времени в часы, минуты или секунды. Поскольку Microsoft Excel использует числовую систему для работы с временем, вы можете…

Формат времени в Excel — Вы узнаете об особенностях формата времени Excel, как записать его в часах, минутах или секундах, как перевести в число или текст, а также о том, как добавить время с помощью…

Как вывести месяц из даты — На примерах мы покажем, как получить месяц из даты в таблицах Excel, преобразовать число в его название и наоборот, а также многое другое. Думаю, вы уже знаете, что дата в…

Как быстро вставить сегодняшнюю дату в Excel? — Это руководство показывает различные способы ввода дат в Excel. Узнайте, как вставить сегодняшнюю дату и время в виде статической метки времени или динамических значений, как автоматически заполнять столбец или строку…

Количество рабочих дней между двумя датами в Excel — Довольно распространенная задача: определить количество рабочих дней в период между двумя датами – это частный случай расчета числа дней, который мы уже рассматривали ранее. Тем не менее, в Excel для…

Источник