Посчитать в excel разницу между датами в

Вычисление возраста в Excel

Вычислить возраст человека непросто, так как результат зависит не только от текущего года, но и дня. Кроме того, необходимо учесть осложнения, возникающие из-за високосных годов. Мы рассмотрим три метода вычисления возраста человека (рис. 1).

Рис. 1. Вычисление возраста человека

Скачать заметку в формате Word или pdf, примеры в формате Excel

Метод 1. Следующая формула вычитает дату рождения из текущей даты и делит результат на 365,25. Функция ЦЕЛОЕ отсекает дробную часть результата: =ЦЕЛОЕ((B2-B1)/365,25). Эта формула не точна на 100%, так как делителем является среднее количество дней в году. Допустим, есть ребенок, которому ровно 1 год. Для него эта формула вернет 0 в обычный год, и 1 – в високосный (рис. 2).

Рис. 2. Разность дат, деленная на 365,25 иногда дает осечку

Метод 2. Более точный метод подсчета возраста связан с использованием функции ДОЛЯГОДА: =ЦЕЛОЕ(ДОЛЯГОДА(B1;B2)). Как правило, функция ДОЛЯГОДА используется в финансовых расчетах, но подходит и для вычисления возраста. Эта функция рассчитывает долю года, соответствующую целому количеству дней между двумя датами. Функция ЦЕЛОЕ удаляет дробную часть результата и возвращает целое число, соответствующее количеству полных лет.

Метод 3. Данный метод вычисления возраста связан с использованием функции РАЗНДАТ: =РАЗНДАТ(В1:В2; » y » ). Это недокументированная функция, которой нет в списке функций, и она не появляется в подсказке, если начать набирать формулу =раз… Правда, ее описание есть в справке Excel.

Эта функция сохранена в Excel для обеспечения совместимости с Lotus 1-2-3. Функция рассчитывает разницу между двумя датами и выражает результат в месяцах, днях или годах. Синтаксис функции РАЗНДАТ(нач_дата;кон_дата;единица). Нач_дата и кон_дата — это стандартные даты (здесь также может стоять ссылка на ячейку, в которой содержится дата). Нач_дата должна быть меньше или равна кон_дата. Третий аргумент – единица – представляет собой текстовую строку, указывающую единицы времени, в которых будет выражен возвращаемый результат. Основные коды интервалов: m — количество полных месяцев, d — количество дней, у — количество полных лет. Аргумент единица может принимать и несколько других значений (подробнее см. справку).

Можно выразить возраст с точностью до дня: =РАЗНДАТ(B1;B2; » y » )& » лет » &РАЗНДАТ(B1;B2; » ym » )& » месяцев » &РАЗНДАТ(B1;B2; » md » )& » дней » . Правда, в связи с особенностями русского языка, иногда это будет выглядеть коряво.

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

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

Преобразовать даты в милисекунды прошедшие с 1970 года, вычесть, и преобразовать назад.

А вообще есть встроенная функция

Или на VBA ее нет?

Lida_K, Не надо никакого VBA ведь всё решается очень просто :

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

=РАЗНДАТ(A1;A2;»Y»)&» лет, «&РАЗНДАТ(A1;A2;»M»)-РАЗНДАТ(A1;A2;»Y»)*12&» месяцев, «&РАЗНДАТ(A1;A2;»MD») &» дней»

Кстати, ни в Excel 2000, ни в Excel 2003 функции РАЗНДАТ() нет в списках мастера функций, хотя в справке обеих версий описание этиой функции есть. Причём, кажется мне, что это не единственный такой случай.

А, а я думал пишите свой возрастник — есть такие жаба скрипты, которые вычисляют «разндат» между введеной и текущей и сообщают, типа: «Нам уже 1 год, 3 месяца, 11 дней». На родительских форумах такое повсеместно распространено.

Чтобы учесть всю календарные девиации надо использовать календарь.

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

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

Я скачал две проги (одна бесплатная «Расчет стажа 1.41», другая shareware «Расчетчик стажа 6.1.1.0»), которые самостоятельно считают стаж по исходным парам дат.

Исходя из возможностей этих прог, ищу возможность автоматизировать процесс расчета стажа в MS Office Excel.

Сказано же в писании, предварительно, т.е. допустим в ячейках «A1:10» находятся даты рождения, а в ячейках «B1:B10» даты упокоения, в таком случае

=РАЗНДАТ(СУММ(A1:A10);СУММ(B1:B10);»Y») =РАЗНДАТ(СУММ(A1:A10);СУММ(B1:B10);»YM») =РАЗНДАТ(СУММ(A1:A10);СУММ(B1:B10);»MD»)

Вы получите данные — довольно близкие к общему возврасту (стажу)

Меня смущает понятие «довольно близкие»! Не уж то батюшка Билл не предусмотрел возможность использования его продукта (MS Office Excel) сотрудниками «Human Resource Manager».

Уверен, что процесс просчёта стажа (по крайней мере общего) возможно автоматизировать и получить ТОЧНЫЕ результаты. Вот не знаю где бы эти формулы достать (формулы, которые используют сотрудники пенсионного фонда для просчёта стажа).

Как я уже указывал, я скачал две проги (одна бесплатная «Расчет стажа 1.41», другая shareware «Расчетчик стажа 6.1.1.0»), которые самостоятельно считают стаж по исходным парам дат. Парадокс: я получил ЧЕТЫРЕ! разных результата — два результата мне дали указанные программы, один результат Excel и ещё один я просчитал на старой доброй бумаге.

Как в экселе сравнить две даты?

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

Первый вариант. Самый простой, мы сравним больше первая дата второй или нет. Для этого в ячейке «А2» поставим обычно сравнение =A2>B2, ответ получим, что это «Истина», что означает правда. Вот как это будет выглядеть в программе эксель.

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

Второй вариант. Можно воспользовавшись функцией «Если», получать в качестве ответа непросто слова: истина или ложь, но и результат с определенным количеством разницы дат.

Для этого в ячейке «С2», напишем новую формулу: =ЕСЛИ(A2>B2;»Первая дата больше второй на»&» «&A2-B2&» «&»дней»;»Первая дата меньше второй на»&» «&B2-A2&» «&»дней»)

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

Как посчитать сроки окончания действия договора в Excel

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

С помощью функции РАЗНДАТ определим, сколько дней прошло с момента заключения трудового договора. В новом столбце для ячейки С2 используем формулу: =РАЗНДАТ(B2;СЕГОДНЯ();”d”) и протянем ее на весь столбец ниже.

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

Сначала мы определяем количество дней во году по дате (365 или 366) по формуле ЕСЛИ(ОСТАТ(ГОД(B2);4);365;366), а после вычитаем, чтобы узнать сколько осталось -C2.

Для удобства можно добавить еще одну колонку, в которой с помощью условий указать замечание для продления договора, если осталось меньше 10 дней до его окончания. Для ячейки E2 используем формулу:

10;”Действует”;ЕСЛИ(D2

В данной формуле используется функция ЕСЛИ с несколькими условиями. Если до конца срока больше 10-ти суток тогда формула возвращает значение «Действует», а если меньше 10-то тогда одно из условий:

1. Если меньше 10-ти суток, но больше чем 0 – значение «Заканчивается».
2. Если меньше чем 0, тогда «Просрочен».

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

​»yd»​ же не даст​ возраст по дате​ примера сначала выполним​Дата и время в​ если день рождения​Для вычислений длительностей интервалов​

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

​ дат удобней всего​

​ и текущей датой.​

​ в Excel, перетащите​

​ можно оперативнее обеспечивать​

​ ДР внутри коллектива​ дате рождения?​ високосный год (366​ правой стороне, а​ лет​

​Дни в года наиболее​На новый лист в​ вычитание дат. Для​

​ числа сформатированные специальным​ уже наступил, т.е.​ использовать недокументированную функцию​Количество дней между двумя​​Этот пример содержит вызовы​​Вычисляет возраст между датами​ ТДАТА .​ выделите данные в​ вас актуальными справочными​

​ появляется дополнительный повод​

​ShAM​ дней) и 1​ текст по левой.​

​Проиллюстрируем пример использования нескольких​ точно преобразует функция:=РАЗНДАТ().​ ячейки A1:A3 введите​ этого:​​ образом. Датой является​​ ДАТА(ГОД(B2);МЕСЯЦ(A2);ДЕНЬ(A2))​ РАЗНДАТ(). Этой функции​ датами в ячейках​ функции ГОД ,​

​Если эта ячейка не​ таблице, а затем​ материалами на вашем​

​:​ день.​ Значение в ячейке​ параметров:​

​ Английская версия =DATEDIF().​ даты: 18.04.1985; 17.08.1977;​На чистом листе в​ целая часть числа,​

​Чтобы определить возраст или​

​ нет в справке​ A2 и A3,​ функции ТДАТА и​ A6, который является​ отображается число, обеспечить​

​ правой кнопкой мыши​

​ языке. Эта страница​HoBU4OK​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СРЗНАЧ(РАЗНДАТ(A2:A5;СЕГОДНЯ();»y»))​

​Способ отображения даты можно​ B4 распознается программой​Внимание! Чтобы функция:=РАЗНДАТ() работала​ Вы не найдете​ 08.12.1992​ ячейку А1 введите​ а время (часы​

​ стаж с точностью​

​ EXCEL 2007 и​ который является 570.​

​ функции МЕСЯЦ.​ 12.08.​ отформатировано как число​ и выберите​

​ переведена автоматически, поэтому​: Спасибо, получилось​Формула массива.​ задать с помощью​​ как текст.​​ без ошибок, следите​ ее в списке​В ячейки B1:B3 проставьте​ текущею дату нажав​

​ до дней, читайте​ в Мастере функций​ Это на основе​Если эта ячейка не​С учетом происходит каждые​ или​Копировать​ ее текст может​»Среднее ДР»​_Boroda_​ диалогового окна «Формат​

​В ячейке B7 Excel​ за тем, чтобы​

​ мастера функций (SHIFT+F3).​

​ текущею дату.​ CTRL+;.​ это дробная часть.​ статью Сколько лет,​ (​ 360-дневного года (двенадцать​ отображается как число,​ 4 лет году​Общие​

​. Откройте новый лист,​ содержать неточности и​

​ЗЫ. отписался около​

​: Так нужно?​ ячеек». Для его​ сам присвоил текущий​ начальная дата была​ Но если просто​Теперь нужно воспользоваться функцией​В ячейке A2 введите​По умолчанию число 1​

​ месяцев, дней прошло​SHIFT+F3​ 30-дневной месяцев), обычно​ обеспечить отформатировано как​

Расчет разности дат

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

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

1. Для начала выделяем целевой диапазон ячеек, воспользовавшись зажатой левой кнопкой мыши.
2. Щелкаем по выделенной области правой кнопкой мыши и в открывшемся контекстном меню выбираем команду “Формат ячеек“. Аналогичного результата можно добиться, используя комбинацию клавиш Ctrl+1.
3. Перед нами окно настроек формата ячеек:
   • по умолчанию мы должны оказаться во вкладке “Число” (если это не так, переключаемся в нее);
   • в списке “Числовые форматы” находим позицию “Дата” и кликаем на нее;
   • в поле “Тип” выбираем нужный образец представления данных, в соответствии с которым они будут отображаться на листе;
   • нажимаем OK.
4. Теперь все значения, вводимые в ячейки выделенной области будут восприниматься программой как данные в формате “Дата“.

Как 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. Поэтому формула считает, что неделя начинается с понедельника (второй день недели).

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

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

• https://ru.extendoffice.com/documents/excel/3797-excel-compare-month-and-year-only.html
• https://exceltable.com/formatirovanie/raznica-mejdu-datami
• https://mister-office.ru/funktsii-excel/if-with-dates.html
• https://ru.extendoffice.com/documents/excel/2739-excel-compare-dates-greater-than.html
• https://exceltable.com/funkcii-excel/funkcii-raboty-datami

Как вычислить дату в Excel

​ содержимого ячеек в​ с датами. Так​Функция РАЗНДАТ поддерживает несколько​ вручную введите в​ формулу:=A1+A2.​ дата – это​ что между датами​В6​

​ функция ДНЕЙ360.​ на ячейку в​ двумя датами без​=ГОД(ТДАТА())-1960​ исходное форматирование​ вас уделить пару​ поподробнее — как​Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММПРОИЗВ(РАЗНДАТ(A2:A5;СЕГОДНЯ();»y»))/СЧЁТ(A2:A5)​ «Дата». В разделе​ строке формул. Обратите​ же, мы убедимся,​

Как в Excel посчитать дни между датами?

​ параметров:​ диапазон C1:C3 следующее​Обратите внимание, формат «Дата»​ количество дней прошедших​ 01.01.2014 и 31.12.2014​содержится сегодняшняя дата​=ДАТАМЕС(A3;-4)​ виде определенное число​ использования ссылок на​Возраст человека родился в​.​ секунд и сообщить,​ поменять операции местами​

​Rioran​ «Тип» отображаются самые​ внимание, как изначально​ что для Excel​
​Параметр​ значение: =РАЗНДАТ(A1;B1;»y»).​ автоматически присвоился для​
​ от 01.01.1900. На​ полного года не​

​ (используйте формулу =СЕГОДНЯ()),​Преобразовать в Формат даты​ или дата.​ ячейки, который является​ 1960, без использования​Данные​ помогла ли она​ — очень хочется​:​ популярные форматы для​ вводилось значение в​ тип данных дата​Описание​

​Таким образом, применение функции​

Вычисление возраста по дате рождения в Excel

​ ячейки A3. Не​ данном уроке детально​ прошло. Формула =ОТБР(ДНЕЙ360(A6;B6)/360), напротив,​

1. ​ а в​ , и она​=ЧИСТРАБДНИ(A3;A2;A3:A5)​ 153.​
2. ​ ссылок на ячейки.​02.10.2012​
3. ​ вам, с помощью​ «вычислить» дополнительный повод​HoBU4OK​ отображения дат.​ A7.​ является числом.​»d»​

​ позволило нам точно​ сложно догадаться, чтобы​ рассмотрим даты, а​ считает. что прошел​А6​

​ должна быть 1​Число рабочих дней между​=ДНИ(СЕГОДНЯ();»15.02.79″)​Если эта ячейка не​02.05.2014​ кнопок внизу страницы.​ для коллектива. Я​, здравствуйте.​Скачать примеры вычисления даты​На чистом листе в​

​Заполните таблицу датами, так​Количество полных дней​ вычислить возраст по​ посчитать разницу в​ на следующих уроках​ 1 год, однако,​– дата рождения​ 02.09.14, который является​ двумя датами в​Количество дней между двумя​

​ отображается как число,​03.06.2014​

​ приводим ссылку на​ екселе. Возраст вычисляю,​

Ввод даты в ячейку Excel

​ Excel.​ нужно от новейшей​Так как дата является​ и 27.02.1988; 28.02.1987 и 28.02.1988; 28.02.1987​ работы), то формула​ (-4 месяца) от​ A3, который является​

​ двух функций.​ число или​03.06.2002​

​ оригинал (на английском​ а как вычислить​ ячейку сочетанием Ctrl​ работы с датами​ ячейках A2:B2 вводим​Разные способы ввода дат.​

​»y»​Внимание! Чтобы перевести дни​ даты отнять старшую​ числом, значит можно​ и 29.02.1988 — так не​ =РАЗНДАТ(A62;B6;»y») вернет количество​ даты в ячейке​ 107. Рабочие дни​Два аргумента дни могут​Общие​. ​Формула​ языке) .​ дату рождения коллектива​ + Shift +​ в Excel​ 01.01.1901

Теперь меняем​ В столбце А​Количество полных лет​ в года не​ дату. В ячейке​

​Формула​ языке) .​ дату рождения коллектива​ + Shift +​ в Excel​ 01.01.1901. Теперь меняем​ В столбце А​Количество полных лет​ в года не​ дату. В ячейке​

Вычисление даты Excel

​ проводить с ним​ считает (сказывается високосный​ полных лет (или​ A3.​ исключить выходные дни​ быть фактическими датами,​. Узнайте, как Формат​Описание​Excel может помочь расчета​ . ​

​ Enter):​На следующем уроке мы​ формат ячеек на​ – способ ввода,​»ym»​ достаточно формулы: =(B1-A1)/365.​ B1 введите формулу:=A3-A1.​

​ математические вычислительные и​ год).​ стаж).​В этом примере функция​ и праздники. Последний​ ссылки на ячейки​ на ячейку в​=(ГОД(ТДАТА())-ГОД(A2))​ возраста человека по-разному.​BobroEJ​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СРЗНАЧ(РАЗНДАТ(A2:A5;СЕГОДНЯ();»y»))​ на готовых примерах​

​ «числовой» в выделенном​ а в столбце​

​Количество полных месяцев без​ Более того даже​ Соответственно мы получаем​

​ расчетные операции. Посчитать​В статье Расчет страхового (трудового)​Альтернативным вариантом является использование​ ДАТАМЕС используется для​ аргумент ячейках a3:​

Мастер даты и времени — простой способ создавать формулы разницы дат в Excel

Как показано в первой части этого руководства, функция РАЗНДАТ (DATEDIF) — довольно универсальная, подходящая для самых разных целей. Однако есть один существенный недостаток — она не документирована Microsoft, то есть вы не найдете РАЗНДАТ в списке функций и не увидите всплывающих подсказок для аргументов, когда начнете вводить формулу в ячейку. Чтобы иметь возможность использовать функцию РАЗНДАТ в своих таблицах, вы должны запомнить ее синтаксис и ввести все аргументы вручную, что может занять много времени и привести к ошибкам, особенно у начинающих.

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

1. Выберите ячейку, в которую вы хотите вставить формулу.
2. Перейдите на вкладку Ablebits Tools  и нажмите кнопку Мастер даты и времени (Date & Time Wizard).
3. Появится диалоговое окно мастера даты и времени, где вы переходите на вкладку «Разница (Difference)» и вводите необходимые данные для расчёта:
   • Щелкните поле Дата 1  и выберите ячейку, содержащую первую дату.
   • Щелкните поле Дата 2 и укажите ячейку со второй датой.
   • Выберите желаемую единицу или комбинацию единиц времени из раскрывающегося меню «Разница в (Difference in)» . При этом программа позволяет предварительно просмотреть результат в поле и формулу в ячейке.

Инструмент может рассчитать разницу между двумя датами в 15 форматах:

1. Годы
2. Месяцы
3. Недели
4. Дни
5. Годы + месяцы
6. Годы + недели
7. Годы + дни
8. Месяцы + недели
9. Месяцы + дни
10. Недели + дни
11. Годы + месяцы + недели
12. Годы + месяцы + дни
13. Годы + недели + дни
14. Месяцы + недели + дни
15. Годы + месяцы + недели + дни

1. Если вас устраивает то, что вы увидели, нажмите кнопку «Вставить формулу (Insert Formula)», в противном случае попробуйте другие единицы измерения.

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

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

Да и вряд ли вы самостоятельно напишете такую сложную конструкцию 

Для наиболее удобного представления результатов доступно еще несколько дополнительных опций:

• Исключить годы и / или месяцы из расчетов. (Годы и / или месяцы просто будут отброшены).
• Показывать или не показывать текстовые метки времени, такие как дни, месяцы, недели и годы .
• Показывать или не показывать нулевые единицы (0 месяцев, например) .
• Возвращает результаты как отрицательные значения, если Дата 1 (дата начала) больше, чем Дата 2 (дата окончания). Думаю, вы сталкивались с ситуацией, когда попытка вычесть из более ранней даты более позднюю приводила к ошибке. Здесь вы просто получите отрицательную разность.

Более подробная информация доступна здесь.

Преимущества использования мастера формул даты и времени

Помимо скорости и простоты, Мастер даты и времени предоставляет еще несколько преимуществ:

В отличие от обычной формулы РАЗНДАТ, для формулы, созданной мастером, не важно, какая из двух дат меньше, а какая больше. Разница всегда рассчитывается идеально, даже если Дата 1 (дата начала) больше, чем Дата 2 (дата окончания).
Мастер поддерживает все возможные единицы измерения (дни, недели, месяцы и годы) и позволяет вам выбирать подходящий вариант из 11 различных комбинаций этих единиц.
Формулы, которые мастер создает для вас, являются обычными формулами Excel, поэтому вы можете редактировать, копировать или перемещать их как обычно. Вы также можете поделиться своими таблицами с другими людьми, и все формулы останутся на своих местах, даже если у кого-то нет Ultimate Suite в их Excel.

Также рекомендуем статьи о работе с датами:

Как складывать и вычитать даты, дни, недели, месяцы и годы в Excel

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

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

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

Работа с датами в excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Другой способ: выделить ячейку с датой, открыть меню Формат — Ячейки (Format — Cells) , вкладка Число , далее — Все форматы и ввести в строку Тип новый формат ДДДД :

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

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

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

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

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

Обратите внимание на то, что на момент написания статьи день – 6 октября 2016 г. Используйте ЧИСТРАБДНИ

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

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

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

В этом примере Дата начала находится в ячейке 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, михолидайс).

ПероЕсли вы не хотите ссылаться на имя диапазона праздников, вы также можете ввести диапазон, например D35: E:39. Кроме того, вы можете ввести каждый праздник в формуле. Например, если праздничные дни – 1 января и 2 из 2016, введите их следующим образом: = ЧИСТРАБДНИ. Межд (D53, E53, 1, ). В Excel 2007 оно будет выглядеть следующим образом: = ЧИСТРАБДНИ (D53, E53, . 2016″>)

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

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

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

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

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

3. вычитание двух значений.

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

4. Задайте формат ч.

Нажмите клавиши CTRL+1 (или +1 на Mac). Выберите » пользовательские _гт_», чтобы исключить из него результаты «AM» и «PM».

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

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

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

2. Задайте формат 3/14/12 1:30 PM.

Выберите обе ячейки, а затем нажмите клавиши CTRL + 1 (или + 1 на компьютере Mac). Затем выберите Дата _ГТ_ 3/14/12 1:30 PM. Это не Дата, которую вы настроили, а вот только пример того, как будет выглядеть формат

Обратите внимание, что в версиях до Excel 2016 этот формат может иметь другой образец даты, например 3/14/01 1:30 PM

3. вычитание двух значений.

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