Как посчитать возраст в excel

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

Как посчитать в Excel количество лет, месяцев и дней возраста. В ячейке А1 стоит дата рождения. В ячейке В2 стоит текущая дата, если нам нужно посчитать возраст на сегодняшний день.

В ячейке С1 пишем формулу:

РАЗНДАТ – это название функции.
y – считает года
ym – считает месяца
md – считает дни
«г.», «мес.», «дн.» написанные между знаками & — так будут подписаны года, месяца и дни в итоговой дате.

Если нужно посчитать возраст человека на определенную дату (не текущую), то в ячейку В2 пишем эту дату.

Формула для расчета сколько прошло лет и месяцев с даты в Excel

Регистрация на форуме тут, о проблемах пишите сюда — alarforum@yandex.ru, проверяйте папку спам! Обязательно пройдите восстановить пароль.

Поиск по форуму
Расширенный поиск
К странице.

Есть количество дней 10232 дня (стаж работы).
Надо узнать сколько это лет, месяцев и дней.
Программа по расчету стажа выдает 28 лет 0 месяцев 19 дней.

Просмотрел форум, не нашел.

Пример вложен.
Заранее спасибо.

Данные формулы выдают 28 лет 0 месяцев но не 19 дней, а 12
A1=10232
A2=ЦЕЛОЕ(A1/365) — количество лет
A3=ЦЕЛОЕ(ОСТАТ(A1;365)/30,5) — количество месяцев
A4=A1-A2*365-A3*30,5 — количество дней

Вложения

Пример.zip (7.8 Кб, 27 просмотров)

есть алгоритм — найдуться и значения, но по сути
10232-й день начиная с 01.01.1900 это 5.01.1928
из которого легко определить стаж
1928-1900 = 28 лет
1-1 = 0 месяцев
5 дней

а что вам нужно — не понимаю

давайте не будет "тянуть кота за резину"
для примера выше:
непрерывный стаж работы с 01.01.1987 по 05.01.2015
без программ, пользуясь простой логикой:
если бы отработал по 01.01.2015 это было бы 28 лет и 1 день (начальная и конечная дата — это отработанные дни)
так вот с 01.01.1987 по 05.01.2015 отработано
2015 — 1987 = 28 лет, 1-1 = 0 месяцев, 5-1+1 = 5 дней. так?
идем дальше
если человек работал с 01.02.2015 по 01.02.2015 — это сколько дней он отработал?, правильно 01.02.2015 — 01.02.2015 +1 = 1 день
возвращаемся к нашему примеру и теперь спросим Ексель сколько будет
05.01.2015 — 01.01.1987 + 1?
правильно 10232 дня?

выше мы выяснили что это 28 лет 0 месяцев и 5 дней

а не 28 лет 0 месяцев и 12 или 19 дней как насчитали Вы и Ваша официальная (правильно считающая) программа для суммарного стажа 10232 дня

excel пересечение интервалов дат

В качестве примера подсчета чисел возьмем список с числовыми значениями от 4 до 30 (См. файл примера ).

Будем подсчитывать значения, попадающие в интервал, например, (4;15]. Причем, границы интервала «включает [ ]» и «не включает ( )» будем выбирать из Выпадающего (раскрывающегося) списка.

Примечание: решение без выбора интервалов = СЧЁТЕСЛИМН(A2:A12;»>»&D2;A2:A12;» Предполагается, что границы интервала введены в ячейки D2 и F2. Эти ячейки не должны быть пустыми, даже если одна из границ =0. Если в диапазоне A2:A12 содержатся числовые значения в текстовом формате, то они будут проигнорированы.

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

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

Например, чтобы прибавить к дате в ячейке А1 пять месяцев, мы использовали формулу: =ДАТА(ГОД(А1);МЕСЯЦ(А1)+5;ДЕНЬ(А1)) . На самом деле, есть более простой и наглядный способ выполнить эту операцию – используем функцию ДАТАМЕС(Дата ; Количество_месяцев) .

У функции два обязательных аргумента :

  • Дата – исходная дата, к которой будем прибавлять заданное количество месяцев
  • Количество месяцев – число месяцев, которое нужно прибавить к дате. Если нужно вычесть месяца – укажите отрицательное число

Приведенный выше пример можно решить с помощью простой формулы: =ДАТАМЕС(А1;5) . Согласитесь, такая запись короче и легче для восприятия.

Часто нужно знать – какой день недели был (будет) в определенную дату. Как бы вы решали такую задачу? Вручную сложно, если нужно обработать несколько десятков, сотен, тысяч дат.

Воспользуйтесь функцией ДЕНЬНЕД(Дата ; Тип) . Она возвращает порядковый номер дня недели и имеет два аргумента :

  1. Дата, для которой нужно определить день недели – обязательный аргумент
  2. Тип – необязательный параметр, который указывает какой день недели считать первым. Например, в странах восточной Европы первый день недели – понедельник, в США – воскресенье. В любом случае, формула может считать первым днем любой день недели. Если аргумент не указан – первым днем считается воскресенье. При записи формулы – Excel выведет подсказку с перечнем возможных параметров

Функция ДЕНЬНЕД в Эксель

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

Нет ничего проще, чем определить количество дней между датами. Просто вычтите более позднюю дату из ранней. Например, в ячейке А1 – дата начала работы над проектом, а в А2 – дата сдачи проекта. Тогда количество дней между ними можно посчитать так: =А2-А1 .

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

Если вам известен некий период, и нужно знать, какую часть календарного года он занимает, используйте функцию ДОЛЯГОДА(Начальная_дата ; Конечная_дата; Базис) . Как видите, у функции 3 агрумента :

  1. Начальная дата – дата старта изучаемого периода – обязательный аргумент
  2. Конечная дата – дата окончания период – обязательный аргумент
  3. Базис – базовые значения длительности года. При введении параметра программа выведет подсказку по выбору этого аргумента.

Например, проект начался 10.08.2015 и закончился 08.05.2016. Чтобы определить долю периода от календарного года, запишем формулу: =ДОЛЯГОДА(«10.08.2015″;»08.05.2016»;1) . Получим результат 0,7432. Отформатируем его в процентном формате и получим 74% года.

Чтобы получить дату последнего дня месяца – используйте функцию КОНМЕСЯЦА(Дата ; Количество_месяцев) . Эта функция возвращает последний день заданной даты, или отстоящей от нее на определенное количество месяцев. Она использует 2 обязательных аргумента :

  • Дата – базовый день месяца, к которому нужно прибавить месяца и вывести последний итогового месяца
  • Количество месяцев – сколько месяцев нужно прибавить к дате. Укажите этот параметр равным нулю, если хотите получить последний день месяца, заданного в аргументом «Дата».

Если у вас есть дата, и вам нужно узнать порядковый номер этой недели в году, используйте функцию НОМНЕДЕЛИ(Дата;Базис):

  • Аргумент «Дата» — это ваша дата, которая принадлежит к искомой неделе (обязательный аргумент)
  • Базис – необязательный аргумент, указывающий какой день недели считается первым. По умолчанию для функции – это воскресенье.

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

Вам так же может быть интересно:

Одна из типичных задач для пользователя Microsoft Excel. Имеем два диапазона дат вида «начало-конец». Задача состоит в том, чтобы определить пересекаются ли эти диапазоны и, если да, то на сколько дней.

Пересекаются или нет?

Начнем с решения вопроса о том, есть ли пересечение интервалов в принципе? Предположим, что у нас есть таблица рабочих смен сотрудников вот такого вида:

Хорошо видно, что рабочие смены Ярослава и Елены пересекаются, но как это вычислить, не прибегая к построению календарного графика и визуальному контролю? Нам поможет функция СУММПРОИЗВ (SUMPRODUCT) .

Вставим в нашу таблицу еще один столбец с формулой, которая выдает логическое значение ИСТИНА в случае пересечения дат:

На сколько дней пересечение?

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

  • интервалы не пересекаются
  • один из интервалов полностью поглощает другой
  • интервалы пересекаются частично

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

На самом деле все можно сделать красиво с помощью функции МЕДИАНА (MEDIAN) из категории Статистические.

Если условно обозначить начало первого интервала за Н1 , а конец за К1 , и начало второго за Н2 и конец за К2 , то в общем виде наша формула может быть записана как:

Как вычитать даты в Excel

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

Читайте также:  Примеры формул с использованием функций МИН и МИНА в Excel

Пример 1. Непосредственно вычитаем одну дату из другой

Думаю, Вы знаете, что Excel хранит даты в виде целых чисел, начиная с 1, что соответствует 1 января 1900 г. Поэтому Вы можете просто арифметически вычесть одно число из другого:

Пример 2. Вычитание дат с помощью функции РАЗНДАТ

Если предыдущая формула кажется Вам слишком простой, тот же результат можно получить более изощрённым способом с помощью функции РАЗНДАТ (DATEDIF).

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

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

Пример 3. Вычитаем дату из текущей даты

Чтобы вычесть определенную дату из текущей даты, Вы можете использовать любую из ранее описанных формул. Просто вместо сегодняшней даты используйте функцию СЕГОДНЯ (TODAY):

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

Пример 4. Вычитание дат с помощью функции ДАТА

Если Вы предпочитаете вводить даты непосредственно в формулу, указывайте их с помощью функции ДАТА (DATE), а затем вычитайте одну дату из другой.

Функция ДАТА имеет следующий синтаксис: ДАТА(год; месяц; день).

Например, следующая формула вычитает 15 мая 2015 года из 20 мая 2015 года и возвращает разность – 5 дней.

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

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

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

Как рассчитать возраст в Excel

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

Команда контент-менеджеров wikiHow тщательно следит за работой редакторов, чтобы гарантировать соответствие каждой статьи нашим высоким стандартам качества.

Количество просмотров этой статьи: 63 521.

В Excel можно рассчитывать возраст посредством использования одной незадокументированной функции и формата даты для ячеек с данными. Самой программой Excel даты воспринимаются в виде порядковых чисел, начиная от 1 января 1900 года. Функция «РАЗНДАТ» умеет находить разницу между двумя указанными датами, что можно использовать для быстрого определения возраста человека.

Получайте дни, часы и минуты между датами

Чтобы вычислить и отобразить дни, часы и минуты между двумя датами, вы можете использовать ТЕКСТ функция с небольшой помощью со стороны Функция INT . В показанном примере формула в D5:

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

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

Значение дней рассчитывается с помощью функции INT, которая просто возвращает целую часть конечной даты за вычетом начальной даты:

Примечание. Хотя вы можете использовать «d» в произвольном числовом формате для дней, значение будет сброшено на ноль, если количество дней больше 31.

Включить секунды

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

Общее количество дней, часов и минут между датами

Чтобы получить общее количество дней, часов и минут между набором дат начала и окончания, вы можете адаптировать формулу с помощью СУММПРОИЗВ следующим образом:

где ‘конец’ представляет диапазон дат окончания, а ‘начало’ представляет диапазон дат начала. В показанном примере D11 содержит эту формулу:

EXCEL для чайников.2.Время

Мой предыдущий пост EXCEL для чайников.1.ВПР содержал несколько грубых педагогических ошибок. Почитав комментарии и сделав выводы, я решил двигаться дальше, побольше, так сказать, разъясняя. Готовьтесь, пост будет еще длиннее.

Сегодня я расскажу вам о времени. Ученые и философы испокон веков спорят о его происхождении, и даже о его существовании. В Excel оно точно есть и работать с ним можно и нужно. Итак, что же такое время в Excel? Возьмем число 42997,635216. Что оно может означать? Человек, работающий с датами в Excel, сразу поймет подвох. Дело в том что это дата и время, в Excel, взятые на момент написания поста функцией =ТДАТА() в формате обычного числа. Про форматы поговорим отдельно, сначала нужно разобраться с представлением дат и времени: 42997 – это количество дней, начиная с 1 января 1900 года, (так что если вы историк то работать с датами до 1900 года придется как обычным текстом, без вычислительных выкрутасов), Стоит так же отметить, что сейчас работать с датами можно вплоть до 31 декабря 9999 г. (тут писатели фантасты печально вздохнули и полезли за калькуляторами). Дробная часть 0,635216 это время от целых суток, то есть 1 – это 24 часа. Давайте проверим за Excel, все ли правильно он посчитал: 0,635216*24=15,245184 (15 это часы); 0,245184*60=14,71104 (14 это минуты); 0,71104*60=42,6624 (43 это секунды, округляем до целого). Теперь переводим ячейку с числом 42992,57046 в формат даты и времени и получаем 19.09.17 15:14:43, хотя функция =ТДАТА() уже изрядно убежала за время нашего расчета. Поверьте, нет смысла проверять за Excel. Не нужно тратить на это драгоценное время, он все считает правильно, ошибка может быть только по другую сторону монитора. Многим это покажется смешным, но у меня есть знакомые, которые проверяют Excel на калькуляторе.

Понимание того что целая часть — это дни, а дробное — это часы, минуты, и секунды, очень важно. Функция =ТДАТА() прекрасна и опасна. Ведь она выполняет расчеты относительно текущего времени, и, если это не нужно и об этом забыть, цифры в таблице поплывут. Они будут плыть пока мы не закрепим дату. Нужно взять ячейку с формулой, войти в режим редактирования и нажать F9, либо копировать её и вставить обратно в туже ячейку как значение. Ход времени в таблице остановится, данные перестанут обновляться.

Какие же еще функции есть в Excel для работы со временем? О, их великое множество, благо в основном названия функций в Excel «говорящие».

ТДАТА( ), СЕГОДНЯ( ) – текущее дата и время в первом случае, и дата без времени во втором.

ВРЕМЗНАЧ(“Текст”), ДАТАЗНАЧ(“Текст”) – переводит время либо дату из текста в числовой формат;

ВРЕМЯ(Часы; Минуты; Секунды), ДАТА(Год; Месяц; День) – «собирает» время либо дату из значений;

ГОД(Дата), МЕСЯЦ(Дата), ДЕНЬ(Дата), ЧАС(Дата), МИНУТЫ(Дата), СЕКУНДЫ(Дата) – «вырезаем» нужное из даты;

ДЕНЬНЕД(Дата, Тип) – выдает номер дня недели (наш тип недели 2, американской 1).

КОНМЕСЯЦА(Дата; Число_Месяцев) – это дата последнего дня месяца со смещением на нужное количество месяцев

ДАТАМЕС(Дата; Число_месяцев) – передвигает эту же дату на нужное количество месяцев вперед или назад

НОМНЕДЕЛИ(Дата; Тип) – номер недели с начала года (тип как в ДЕНЬНЕД)

РАБДЕНЬ(Дата; Количество; Праздники) – дата, которая будет или была через заданное количество дней (учитывая или нет праздники)Праздники задаются диапазоном ячеек

РАБДЕНЬ.МЕЖД(Дата; Количество; Выходной; Праздники) – то же самое, но с расширенной настройкой выходных данных. Можно задать строкой где 0-это рабочий день, 1-это выходной, на пример нормальная рабочая неделя выглядит так “0000011”

ЧИСТРАБДНИ (Дата1; Дата2; Праздники) – возвращает количество рабочих дней между 2 датами (с праздниками или без них)

ЧИСТРАБДНИ.МЕЖД (Дата1; Дата2; Выходной; Праздники) – то же самое, но с произвольным выбором выходных дней. (см. РАБДЕНЬ.МЕЖД)

ДНЕЙ360(Дата1; Дата2) – “Функция ДНЕЙ360 возвращает количество дней между двумя датами на основе 360-дневного года (двенадцать месяцев по 30 дней). Эта функция используется для расчета платежей, если система бухгалтерского учета основана на двенадцати 30-дневных месяцах.” © — взял из справки, в бухгалтерии не силен, ничего добавить не могу, кроме того, что это как-то связано с расчетом равномерности платежей в течении года. В общем, эти бухгалтера даже в году умудрились спереть 5 дней.

ДОЛЯГОДА(Дата1; Дата2; Базис) – это доля года между двух дат. Базис равен 1, если хотите считать по фактическим датам. В противном случае данное значение варьируется от 0 до 3, выбирайте то, что нужно, согласно пояснениям, содержащимся в справке.

Теперь давайте разберемся, как это работает. В столбце А я напишу формулу, а в столбце В, С, D я напишу результат этой формулы в разных форматах, в столбце E напишу комментарии.

Читайте также:  Инвестиционный проект в Excel c примерами для расчетов

EXCEL для чайников.2.Время Microsoft Excel, Для чайников, Длиннопост

EXCEL для чайников.2.Время Microsoft Excel, Для чайников, Длиннопост

EXCEL для чайников.2.Время Microsoft Excel, Для чайников, Длиннопост

Теперь посмотрим, какие формулы у нас стоят в ячейках в столбцах D-I, для удобства я их представил немного в другом виде, формулы представлены для 2-й строки, для остальных строк их нужно только «протянуть»

Время на работе (ч) =(C2-B2)*24 – тут мы вычитаем дату прихода из даты ухода, из суток переводим в часы, все просто.

Время на работе в рабочие дни (ч) =(ЧИСТРАБДНИ(B2;B2)*(C2-B2))*24 тут мы учитываем, был ли день рабочий. Я обнаружил, что если применить ЧИСТРАБДНИ с указанием одного дня эта функция в случае рабочего дня выдаст 1 и в случае выходного — 0, далее все как в формуле выше.

Время на работе в выходные (ч) =(НЕ(ЧИСТРАБДНИ(B2;B2))*(C2-B2))*24 тут мы «перворачиваем» функцию ЧИСТРАБДНИ логической функцией НЕ, которая из 1 делает 0 а из 0 делает 1, далее все как выше.

Недоработка/переработка (ч) =ЕСЛИ(ЧИСТРАБДНИ(B2;B2);D2-($K$2-$K$1)*24;F2) здесь используем функцию ЕСЛИ. Эта функция имеет 3 аргумента: логическое условие, результат при выполнении этих условий и результат, если логическое условие не выполняется. В данном случае мы проверяем по функции ЧИСТРАБНИ является ли день рабочим, если является вычитаем из фактически отработанного времени норму рабочего времени, которая в свою очередь получается из разницы конца и начала рабочего дня. Затем переводим все в часы, умножая на 24. если день выходной учитываем все время, проведенное на работе как переработку. Обратите внимание что ссылки на ячейки начала и конца рабочего дня мы «закрепили» символами $. Это делается нажатием клавиши F4 при нахождении курсора на ячейке, также есть возможность напечатать данный символ вручную. Смысл «закрепления» ячейки в том, что при протягивании формулы ссылки на «закрепленные» ячейки не будут смещаться относительно перемещения формулы по столбцам и строкам. Можно также закрепить отдельно либо столбец, либо строку, в нашем случае ссылка выглядела как $K2 при закрепленном столбце и как K$2 при закрепленной строке. При протягивании в таком случае меняется только незакрепленный фрагмент адреса ячейки, что бывает весьма полезно в некоторых случаях.

Опоздание (мин) =ЕСЛИ(ЧИСТРАБДНИ(B2;B2);ЕСЛИ(B2-ОКРУГЛВНИЗ(B2;0)<$K$1;»»;ОКРУГЛ(((B2-ОКРУГЛВНИЗ(B2;0))-$K$1)*24*60;0));»»). Также, как и выше, мы проверяем рабочий ли у нас день. Затем (это мое любимое) вычисляем время прихода сотрудника, без учета даты. Для этого я отнимаю из даты со временем значение той же даты со временем округленное вниз до целого значения с помощью функции ОКРУГЛВНИЗ. Выражение B2-ОКРУГЛВНИЗ(B2;0) у нас будет иметь значение 8:42 в формате времени, то есть время прихода сотрудника. В принципе мы могли бы написать =ВРЕМЯ(ЧАС(B2);МИНУТЫ(B2);СЕКУНДЫ(B2)), это аналогичное решение, которое собирает время из значений часов, минут и секунд, но первое решение мне нравится больше. Затем сравниваем это время с временем начала рабочего дня, если оно меньше — оставляем ячейку пустой (“”), если же больше — считаем что сотрудник опоздал и высчитываем опоздание в минутах: из времени фактического прихода отнимаем время начала рабочего дня и умножаем на 24 и на 60, чтобы получить минуты, затем округляем полученный результат до целого значения. В случае же если день выходной, то логическое условие функции ЕСЛИ не выполняется и ячейка остается пустая.

Ранний уход (мин) =ЕСЛИ(ЧИСТРАБДНИ(C2;C2);ЕСЛИ(C2-ОКРУГЛВНИЗ(C2;0)>$K$2;»»;ОКРУГЛ(($K$2-(C2-ОКРУГЛВНИЗ(C2;0)))*24*60;0));»»). Тут все аналогично предыдущему, за исключением того что учитывается время ухода, которое должно быть больше времени окончания рабочего дня.

Вот и все что я хотел рассказать про время, пост получился длинноватым. Надеюсь, Вы меня поняли. Буду думать, о чем рассказать в следующий раз. Помните, сначала Вы работаете в Excel, потом Excel работает за Вас!

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

Если бы проект был текстовым, вы бы изменили формулу так, чтобы она возвращала номер строки таблицы, чтобы найти ее с помощью функции INDEX .

Например: с заголовками столбцов таблицы в строке 1:

Если у вас более старая версия Excel, в которой нет функции AGGREGATE , вы можете использовать:

enter image description here

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

Вот еще одно решение, немного ручная , но все еще работает.

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

Учитывая, что столбцы Start и End являются столбцами B и C, а столбец A — это столбец с вашим номером (заголовки находятся в 1-й строке):

А другая таблица находится в столбцах B C и D (заголовки в 11-м ряду).

Если вы скопируете эту формулу куда-то, вы получите идентификационный номер проекта или 0 (любой)

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

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

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

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

A1=15.12.2000
A2=СЕГОДНЯ()
A3=РАЗНДАТ(A1;A2;»Y»)&» лет, «&РАЗНДАТ(A1;A2;»M»)&» месяцев, «&РАЗНДАТ(A1;A2;»MD») &» дней»

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

A1=01.01.2004
A2=01.01.2006
A3=РАЗНДАТ(A1;A2;»Y»)&» лет, «&РАЗНДАТ(A1;A2;»YM»)&» месяцев, «&РАЗНДАТ(A1;A2;»MD») &» дней»

[QUOTE=pashulka]. правильный вариант формулы, конечно же должен выглядеть так. [/QUOTE]
Или так, например:
[color=blue]=РАЗНДАТ(A1;A2;»Y»)&» лет, «&РАЗНДАТ(A1;A2;»M»)-РАЗНДАТ(A1;A2;»Y»)*12&» месяцев, «&РАЗНДАТ(A1;A2;»MD») &» дней»[/color]

Кстати, ни в 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».

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

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

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

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

    Прибавляем N недель к дате в Excel:

= A2 + N недель * 7

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

=A2+3*7
Вычитаем N недель из даты в Excel:

Чтобы вычесть 2 недели из сегодняшней даты, используйте эту формулу:

Разница дат и времени в Excel

Если вычислять разницу 2018-01-09 15:24:05 и 2018-01-08 20:06:25, то пишет только время, а если отнимать 2018-01-10 15:24:05 и 2018-01-08 20:06:25 то пишет 1 дней и время.

Я так понимаю, такое внутренними средствами Excel это не сделать? Пробовал через

Если разница только во времени, то все как положено считает и выводит только разницу во времени, а если будет разница превышающая 24 часа или один месяц?

Если я правильно Вас понял — то может что-то типа такого?

Дата в Excel — число дней от 01.01.1900г., время — дробная часть числа. Например, 10.01.2018 10:15:20 в числовом выражении будет 43110,4273148148 (выражение даты в десятичном формате можно посмотреть, задав формат ячейки Общий).

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

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

Значение дата/время сохранено в числовом формате. Недостаток: формат отобразит количество дней не более 31

Но дата/время текстом также применяется (например: в конечных документах, для отображения значения в составе другого текста):

Читайте также:  Автоформат заголовков столбцов таблицы в Excel

Как складывать и вычитать время в Excel

В Microsoft Excel складывать и вычитать время можно с помощью функции ВРЕМЯ (TIME). Она позволяет Вам обращаться с единицами времени (часами, минутами и секундами) так же, как и с годами, месяцами и днями в функции ДАТА (DATE).

Прибавить время в Excel:

A2 + ВРЕМЯ(часы ; минуты ; секунды )
= A2 + TIME(часы , минуты , секунды )

Вычесть время в Excel:

A2 — ВРЕМЯ(часы ; минуты ; секунды )
= A2 — TIME(часы , минуты , секунды )

где A2 – это ячейка с временем, которое нужно изменить.

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

  • Опыт использования планшетника-ультрабука Acer Iconia W700Опыт использования планшетника-ультрабука Acer Iconia W700
  • Гибридный планшет – Acer Iconia Tab W700 Acer iconia tab w700 игрыГибридный планшет – Acer Iconia Tab W700 Acer iconia tab w700 игры
  • Удаление Satanacript и восстановление файловУдаление Satanacript и восстановление файлов
  • IMazing — новое слово в управлении файловой системой iPhone и iPadIMazing — новое слово в управлении файловой системой iPhone и iPad

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

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

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

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

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

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

В этом случае нам поможет формула ЧИСТРАБДНИ.

  • запускаем мастер функций;
  • вводим название функции ЧИСТРАБДНИ;
  • нажимаем «ОК»;
  • указываем начальную дату;
  • указываем конечную дату;
  • если в диапазоне есть праздники указываем их в отдельных ячейках;
  • нажимаем «ОК».

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

Получим результат — 18 рабочих дней.

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

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

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

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

Ну и напоследок как нам извлечь из даты год, месяц и день. Для этого воспользуемся соответственно формулами ГОД, МЕСЯЦ, ДЕНЬ.

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

Если же нужно, чтобы месяц или день отображался словом, используем формулу ВЫБОР.

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

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

Календарный год в григорианском и юлианском календарях равен 365 суткам в невисокосные годы и 366 суткам в високосные годы. Средняя же продолжительность года составляет 365,2425 суток для григорианского календаря и 365,25 суток — для юлианского.

Вычитание чисел с помощью ссылок на ячейки

  1. Введите числа в ячейки C1 и D1. Например, введите 5 и 3.
  2. В ячейке E1 введите знак равенства (=), чтобы начать ввод формулы.
  3. После знака равенства введите C1-D1.
  4. Нажмите клавишу RETURN. Если использовать числа из примера, получается результат 2. Примечания:

Формула для расчета сколько прошло лет и месяцев с даты в Excel

2автор:
Для решения задачи надо сделать три движения:
1) любым способом получить интервал в годах-месяцах
2) прибавить этот интервал к первой дате
3) вычислить разность в днях между датой (2) и конечной датой.

тут можно и просто разностью дат обойтись

Держи для наглядной агитации

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

ок.бытовой упрощенный ответ (игнорирующий факт, что дни и месяцы не аддитивны)

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

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

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

Функция ЧИСТРАБДНИ. Реквизиты и пример

Начнем, как всегда, с состава функции и ее реквизитов.

Состав реквизитов можно увидеть на начальной картинке

нач_дата — дата, с которой начинаем расчет.

кон_дата — дата, до которой нужно произвести расчет.

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

Если вам нужно отсчитать количество рабочих дней с заданной даты, пользуйтесь РАБДЕНЬ.

Функция РАБДЕНЬ. Реквизиты и пример

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

Нач_дата — дата, с которой начинаем расчет.

число_дней — количество дней, которые должны пройти с начальной даты

праздники — такой же реквизит как и ЧИСТРАБДНИ

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

Как считать рабочие дни с учетом праздников?

Для обоих функций, как вы заметили, есть реквизит ‘Праздники’. В нем можно указать, какие даты будут считаться праздниками (соответственно, не учитывая выходные). Например так:

chistrabdni2

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

Калькулятор дат. Расчет времени(количества дней) между датами онлайн

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

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

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

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

Задача: посчитать сколько прошло дней с прошлого Нового Года и какое количество дней осталось до следующего Нового Года.

Прошлый Новый Год а следующий Что касается текущей даты, то выберите ее самостоятельно. Я выбрал 1 сентября.

Немного теории. Хочу напомнить, что Excel правильно работает с датами, начиная с Все даты можно представить в виде простого числа, например «1» соответствует дате а число «41640» соответствует дате Именно поэтому, если нам нужно посчитать дни между датами, достаточно от одной даты отнять другую. Стоит помнить, что если от большей даты отнять меньшую дату, то получим положительное число и наоборот.

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

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

Формулы записанные в строке 6 очень простые.

B6 = B4 — B5 C6 = C4 — C5 D6 = D4 — D5 E6 = E4 — E5

Со второго рисунка лучше видно как считается количество дней. В ячейку B6 записали формулу B4-B5.

Расчет разницы между датами с дкмонстрацие формулы

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

Подсчет если есть пустые ячейки

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

Здесь добавилось умножение на выражение (даты<>«») . То есть, когда значение не заполнено, это выражение возвращает ЛОЖЬ и весь элемент массива с результатами будет ЛОЖНЫМ, такая ячейка не будет учтена.

дни рождения с пустыми строками

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

Рассмотрим следующую пользовательскую функцию (UDF):

Годы, месяцы и дни будут возвращены с одним пробелом между ними.

Пользовательские функции (UDF) очень просты в установке и использовании:

  1. ALT-F11 открывает окно VBE
  2. ALT-I ALT-M открывает новый модуль
  3. вставьте материал и закройте окно VBE

Если вы сохраните книгу, UDF будет сохранен вместе с ней. Если вы используете версию Excel более поздней, чем в 2003 году, вы должны сохранить файл как .xlsm, а не .xlsx

Ссылка на основную публикацию