Функции даты и времени в Excel
В документах Microsoft Excel может потребоваться указать на листе день и время его создания; или Вы работаете с отрезками времени, между которыми нужно посчитать количество дней, месяцев; а может нужно из проставленной даты понять, какому дню недели она соответствует, или добавить к ней определенное значение.
Во всем этом нам могут помочь функции, которые находятся в категории «Дата и время» в Экселе. Их там чуть больше двадцати, и с каждым разом разработчики добавляют новые. В данной статье мы на примерах рассмотрим практически все. И, думаю, после этого у Вас отпадут вопросы: как поставить актуальную дату, как посчитать рабочие дни за определенный период, как узнать последнее число месяца и много других.
Начнем с функции ДАТА – она выводит заданное число в числовом виде ( А1 ). Аргументы у нее такие: (год;месяц;день). При этом формат ячейки автоматически меняется на Дата.
С ее помощью можно прибавить дни (месяца, года) к дате или отнять. Например, в А2 вставим текущую, используя ТДАТА, а затем в В2 пропишем формулу:
Что она значит: из А2 поочередно вытягивается каждый аргумент, а к дням добавляется 7. И теперь в В2 всегда будет показано значение на 7 дней больше текущего.
Подробнее прочесть про функцию ДАТА можете, перейдя по ссылке.
Следующая функция РАЗНДАТ . Ее в списке нет, но пользоваться ей можно. Она поможет рассчитать дни, месяцы, годы между заданными значениями. Аргумента у нее три: начальная, конечная дата и единица. В качестве последнего указывается: y, m, d, md, ym, yd . Так, например, если нужно посчитать месяцы между датами ( А3 и В3 ), выбирайте m . Формула выглядит так:
Подробнее о РАЗНДАТ прочтите в статье: как посчитать дни между датами в Эксель.
ДАТАЗНАЧ – делает из указанной даты порядковый номер. Дело в том, что Эксель каждую дату понимает как определенное число. Отсчет начинается с 01.01.1900 года – это 1, 02.01.1990 – это 2 и так далее. В качестве аргумента – дата, записанная в виде текста ( Е1 ), или объединенные значения из нескольких ячеек ( D4 ).
В примере видно, что 13 ноября 2017 года соответствует число 43052. Его можно использовать для расчетов, применения фильтров, форматирования.
ДЕНЬ – достает из определенной даты только день. В качестве аргумента – указанная в числовом формате дата.
Например, применим формулу к ячейке А1 . Результат будет 13 ( А5 ).
ДАТАМЕС – вернет дату на заданное количество месяцев больше или меньше текущей. Аргументы : начальная дата и количество месяцев. Второй может быть как отрицательным, так и положительным числом.
Если к А1 прибавить 2 мес, получится 13 января ( А6 ): =ДАТАМЕС(А1;2) . Если нужна дата в прошлом, тогда в скобках укажите отрицательное значение: (А1;-2) – выйдет 13 сентября.
КОНМЕСЯЦА – возвращает последний день указанного месяца. В качестве аргументов : дата начала и число месяцев.
Например, нужно узнать последний день декабря. Начальной датой возьмем А1 , значит мне нужно прибавить 1, чтобы из ноября получить декабрь. Формула такая:
Чтобы узнать, сколько дней в другом месяце, например, сентябре, формула получится:
То есть мы отняли 2 месяца от начальной даты ( А1 ), чтобы получился сентябрь.
Функция ЧАС преобразует заданное десятичное число в часы или выделяет из ячейки с датой и временем только час. В Экселе время обозначается десятичными числами, так 12:00 – это 0,5 ( А8 ), а если написать 0,46 ( А9 ), то выделится 11 часов. Соответствующие формулы записаны в Е8 и Е9 .
Если же у Вас в ячейке показываются текущие дата и время ( А10 ), то из нее можно выделить число соответствующее часам: =ЧАС(А10) .
Функция МИНУТЫ похожа на предыдущую, только возвращает соответствующее значение. Например, в А10 стоит 17:45. Записав формулу: =МИНУТЫ(А10) , в В11 покажется только число 45. Если работаете с десятичными числами ( А9 ), то можно указать и их в качестве аргумента. Так из 0,46 выделяется 2 минуты.
Чтобы показать количество секунд, используется функция СЕКУНДЫ . В ячейке А10 стоит 26 сек, хотя они и не отображаются; а числу 0,46 ( А9 ) соответствует 24 сек.
Если из установленного в ячейке дня необходимо выделить только МЕСЯЦ , используется соответствующая функция. Формула: =МЕСЯЦ(А13) вернет в В13 число 11, которое относится к ноябрю.
Если нужно посчитать дни между двумя датами, причем только рабочие, используется ЧИСТРАБДНИ . У нее 3 аргумента : начальная и конечная даты и праздники (необязательный). Подробнее о ней рассказано в статье, ссылка на которую приведена в абзаце про РАЗНДАТ.
Возьмем такую формулу:
Она посчитает все рабочие даты за период с 13 ноября по 13 января, и при этом вычтет 2 праздника, 1 и 7 января.
Верхняя функция считает за выходные все сб и вс. Если у Вас это другие дни, лучше использовать ЧИСТРАБДНИ.МЕЖД . Ее аргументы : начальная дата, конечная, выходные, праздники.
Аргументу «выходные» соответствует определенное число. При их заполнении появится выпадающий список, из которого нужно его выбрать. Так, например, у меня выходной только в среду, значит, пишу в формуле «14». Указываю праздники, и выходит 51 рабочий дней.
Можно записать выходные и по-другому – в виде 1 и 0, взятых в кавычки. Нули – это рабочие, а единицы – выходные. Запись «0010010» означает, что мы отдыхаем в среду и субботу. Итого, работаем всего 42 дня.
Если на листе Excel в определенной ячейке нужна сегодняшняя дата, тогда используйте ТДАТА . Она в числовом формате отобразит дату и время ( А18 ), установленные на компьютере. Аргументов здесь нет.
ВРЕМЯ – позволит вывести не целое число, соответствующее заданному времени. Аргумента здесь три – часы, мин и сек. Например, 18:14:57 – это 0,760381944.
ВРЕМЗНАЧ тоже вернет числовое значение для времени, только в качестве аргумента используется число в текстовом формате, заключенное в кавычки: =ВРЕМЯЗНАЧ(«18:14:57») .
Функцию СЕГОДНЯ я уже описывала. Она отобразит текущую дату. Аргументы здесь не указываются.
Кстати именно с ее помощью можно посчитать возраст человека по дате рождения. Для этого из текущего дня выделяется год и отнимается г/р человека: =ГОД(СЕГОДНЯ())-1945 .
Если нужно определить день недели по заданной дате, тогда воспользуйтесь функцией ДЕНЬНЕД . У нее первый аргумент – это дата, второй – тип, какое число нужно вернуть. При вводе формулы развернется список, из которого его можно выбрать. Например, я хочу, чтобы пн соответствовала единица, вт – двойка и так далее, значит ставлю «11».
В результате, для 13 ноября 2017 года показано – 1. Правильно – это понедельник.
Чтобы вывести номер недели в году, на которую припадает определенное число, используется НОМНЕДЕЛИ . Здесь аргументы те же – дата и тип. Второй выбирается из выпадающего списка. Первая неделя в году может считаться или с 1 января (это 1 система), или по стандарту ISO 8601 с первого четверга (2 система).
Выделяйте в списке значения и смотрите в небольшой области рядом пояснения. Например, возьмем за первую неделю ту, которая начинается с понедельника, значит, ставим для второго аргумента «2».
В примере, 13 ноября в 2017 году ( А21 ) припадает на 47 неделю.
И последняя функция, которую мы рассмотрим – РАБДЕНЬ . Она возвращает дату, которая больше или меньше на заданное число дней, но именно рабочих. Здесь не учитываются выходные и праздники. Аргумента у нее три: дата начала, дни, праздники.
Чтобы избежать ошибок в расчетах, начальное значение введем с помощью функции ДАТА ( А24 ). Теперь давайте отсчитаем 80 дней ( В24 ):
Если нужно, последним аргументом указываются адреса ячеек с праздниками. Ячейка, в которую выводится результат, должна быть отформатирована как дата ( С24 ).
На этом буду заканчивать. Надеюсь, Вы нашли нужную функцию из того списка, который я описала в статье, и теперь сможете вывести или посчитать правильно данные в ячейках с датой и временем в Экселе.
Функции управления датами
Excel предлагает не так много функций, предназначенных для работы с датой. Эти функции представлены в раскрывающемся списке Формулы→Библиотека функций→Дата и время.
В таблице приведены функции, связанные с использованием даты, которые доступны в Excel.
Отображение текущей даты
Функция, приведенная ниже, отображает в ячейке текущую дату (учитывайте, что если установлен текстовый или общий формат ячейки, будет отображено порядковое число сегодняшней даты).
Можно отобразить не только текущую дату, но и объединить ее с любым текстом. Например, следующая формула будет выводить на экран такой текст: Сегодня понедельник, 9 Апрель, 2010.
=“Сегодня ”&ТЕКСТ(СЕГОДНЯ();“дддд, д ММММ, гггг”)
Помните, что результат функции СЕГОДНЯ изменяется каждый раз при расчете рабочего листа. Например, если ввести в ячейку рабочего листа любую из рассмотренных формул, они будут отображать текущую дату. Но откройте этот же рабочий лист на следующий день, и вы увидите, что формулы отображают совсем другую текущую дату.
Чтобы ввести в ячейку штамп даты (значение даты, которое не будет изменяться каждый последующий день), нажмите клавиши . Текущая дата будет введена в ячейку в текстовом формате без использования формулы, поэтому она останется неизменной.
Отображение произвольных дат
Дату можно добавить в ячейку, непосредственно введя ее в любом формате, поддерживаемом в приложении Excel. Дату также можно создать; для этого воспользуйтесь функцией ДАТА. Эта функция использует три аргумента: год, месяц и день. Например, приведенная ниже формула возвращает дату, состоящую из года, взятого из ячейки А1, месяца, взятого из ячейки В1, и дня – из ячейки С1.
Примечание
Обратите внимание, что функция ДАТА принимает также недопустимые аргументы и приводит результат в соответствие ожидаемому. Например, следующая формула использует число 13 как аргумент месяца и возвращает 1 Январь, 2011. Аргумент месяца автоматически преобразуется в первый месяц следующего года.
Достаточно часто в функции ДАТА в качестве аргументов используются другие функции. Например, в приведенной ниже формуле функции ГОД и СЕГОДНЯ, используемые как аргументы, возвращают День Независимости США (4 Июля) текущего года.
Функция ДАТАЗНАЧ преобразует текстовую строку, представляющую собой дату, в порядковое число даты. Следующая формула возвращает значение порядкового числа даты 22 Августа 2010 года – 40412:
Для того чтобы отобразить результат этой формулы в виде даты, достаточно применить к ячейке формат даты.
Предупреждение
Будьте особо внимательны при использовании функции ДАТАЗНАЧ. Текстовая строка, которая может быть использована как дата в одной стране, совершенно не подходит для ввода даты в другой стране. Предыдущий пример работает прекрасно в том случае, если система настроена для форматов даты России или Украины. Однако при использовании других региональных форматов даты (в частности, форматов США) формула может возвращать ошибку, поскольку приложение Excel будет искать восьмой день двадцать второго месяца.
Создание последовательности дат
В отдельных случаях может возникнуть необходимость вставить в рабочий лист целый ряд последовательных дат. Например, при еженедельном отслеживании продаж требуется ввести несколько последовательных дат с интервалом в семь дней. Эти даты будут служить основой для систематизации коммерческой информации.
Самый эффективный способ ввода ряда последовательных дат не требует никаких формул. Просто воспользуйтесь инструментом, встроенным в Excel, – автозаполнением. Для этого введите в ячейку первую дату, а затем, удерживая нажатой правую кнопку мыши, перетащите в необходимом направлении маркер автозаполнения (маленький черный квадрат в правом нижнем углу контура выделения). Отпустите правую кнопку мыши и выберите в контекстном меню команду Заполнить по дням.
Функция автозаполнения весьма гибкая. Например, можно создать последовательность дат, разделенных десятью днями. Для этого введите две первые даты в две ячейки, выделите эти ячейки, перетащите правой кнопкой мыши маркер автозаполнения и выберите в контекстном меню команду Заполнить по дням.
Использование формул для создания последовательности дат все же имеет свои преимущества. В этом случае достаточно указать первую дату, после чего остальные автоматически приводятся ей в соответствие. Вначале введите в ячейку начальную дату, а затем добавьте формулы, скопировав их вниз по столбцу, чтобы получить даты, следующие за исходной.
В представленном далее примере мы введем первую дату ряда в ячейку А1, а формулу – в ячейку А2. Скопируйте формулу в ячейке вниз по столбцу столько раз, сколько это необходимо.
Данная формула позволяет создать ряд последовательных дат, следующих одна за другой с интервалом в семь дней.
Чтобы создать ряд последовательных дат, отстоящих друг от друга на месяц, воспользуйтесь такой формулой:
Формула, приведенная ниже, возвращает даты, следующие одна за другой с интервалом в год.
Чтобы создать ряд, который состоит из дат, относящихся только к рабочим дням (т.е. исключая субботы и воскресенья), воспользуйтесь приведенной ниже формулой. Имейте в виду: она предполагает, что дата, введенная в ячейку А1, – рабочий день.
Преобразование строки в дату
Даты, импортируемые как текстовые строки, тоже можно преобразовать в дату. Предположим, в результате импорта из другого приложения получена приведенная ниже текстовая строка, которая в действительности представляет собой дату 21 августа 2010 года. Этот текст состоит из четырех знаков, относящихся к году, двух знаков месяца и двух знаков дня.
Чтобы преобразовать эту строку в фактическую дату, воспользуйтесь формулой, приведенной ниже. Эта формула предполагает, что исходные данные занесены в ячейку А1.
Представленная формула использует текстовые функции ЛЕВСИМВ, ПСТР и ПРАВСИМВ для извлечения из заданной строки определенных чисел. После этого она применяет их как аргументы функции ДАТА.
Вычисление количества дней между двумя датами
Самый распространенный тип вычислений, связанный с датами, – это определение количества дней между двумя заданными датами. Предположим, что на одном из листов рабочей книги вычисляется процент, получаемый по срочному вкладу. Размер полученного процента зависит от количества дней, на которое открыт счет. Если рабочий лист содержит дату открытия и дату закрытия счета, то можно легко определить срок в днях, на который был открыт счет.
Поскольку даты на рабочем листе, как правило, сохраняются в виде последовательных значений, то для вычисления количества дней можно использовать простое вычитание. Предположим, что ячейки А1 и В1 содержат некоторые даты. Следующая формула возвращает количество дней между этими датами:
Вероятнее всего, Excel автоматически отформатирует результирующую ячейку как дату, а не как числовое значение. Вам потребуется вручную изменить формат этой ячейки на числовой. Если ячейка В1 содержит более позднюю дату, чем ячейка А1, то результат, возвращаемый формулой, будет отрицательным.
Примечание
Если приведенная выше формула отображает некорректное значение, убедитесь, что ячейки A1 и В1 действительно содержат даты, а не текстовую информацию, похожую на даты.
В некоторых случаях вычисление разницы между двумя датами может потребовать значительных усилий. Чтобы продемонстрировать это, проведем аналогию с “телеграфными столбами”. Например, если требуется определить количество телеграфных столбов, то задачу их подсчета можно решить двумя способами: непосредственно сосчитав количество столбов или определив количество пролетов между ними. При этом имейте в виду, что количество столбов всегда на один больше, чем пролетов.
Чтобы рассмотреть эту ситуацию применительно к некоторому диапазону дат, в качестве примера приведем кампанию по продвижению на рынок некоторого товара. Предположим, что начальная дата кампании по продвижению товара – 1 февраля, а завершающая дата – 9 февраля. Просто вычитая одну цифру из другой, мы получим ответ: восемь дней. Но в действительности срок продвижения товара – девять дней. В этом случае, чтобы получить правильный ответ, необходимо сосчитать дни “включительно”, а не определять количество дней между датами. Ниже приведена формула, позволяющая вычислить длительность кампании по продвижению товара (предположим, что у нас есть ячейки, соответствующие этим названиям).
Вычисление количества рабочих дней между двумя датами
Excel умеет не только вычислять количество дней между двумя датами. В случае необходимости можно исключать из числа этих дней выходные и праздничные дни. Например, вычислим, сколько рабочих дней выпадает на ноябрь месяц. Для этого необходимо исключить из расчета субботы, воскресенья и праздничные дни (если таковые имеются), воспользовавшись функцией ЧИСТРАБДНИ.
Примечание
Название функции ЧИСТРАБДНИ часто вводит в заблуждение. Она не имеет никакого отношения к чистоте или рабству, а, вместо этого, возвращает количество рабочих дней между двумя заданными датами.
Итак, функция ЧИСТРАБДНИ вычисляет разницу между двумя датами, исключая выходные дни (субботы и воскресенья). Кроме того, как дополнительное условие можно, определить диапазон ячеек, содержащий даты, на которые приходятся праздничные дни. Эти даты также могут быть исключены при расчете. Естественно, Excel не имеет никакой возможности самостоятельно определять праздничные дни, поэтому вы должны обеспечить программу этой информацией, указав соответствующий диапазон ячеек.
На рисунке показан рабочий лист, на котором вычисляется количество рабочих дней между определенными датами. В диапазоне А2:А8 содержится перечень дат, приходящихся на праздничные дни. Формулы, которые введены в столбце С, вычисляют количество рабочих дней, расположенных между датами в столбцах А и В. Например, формула в ячейке С11 будет иметь такой вид:
Формула возвращает значение 3. Это означает, что семидневный период, начинающийся с 1 января, содержит три рабочих дня. Другими словами, при расчете исключаются два праздничных и два выходных дня. Ячейка С12 содержит формулу, которая вычисляет общее количество рабочих дней в первом полугодии 2010 года.
Новинка
В Excel 2010 добавлена усовершенствованная версия функции ЧИСТРАБДНИ, которая называется ЧИСТРАБДНИ.МЕЖД. Она полезна, когда нужно считать выходными днями не субботу и воскресенье, а другие дни недели.
Вычисление даты по графику рабочих дней
Функция РАБДЕНЬ является прямой противоположностью функции ЧИСТРАБДНИ. Например, при запуске в работу определенного проекта, на выполнение которого требуется десять дней, функция поможет вычислить дату окончания проекта.
В приведенной ниже формуле с помощью функции РАБДЕНЬ определяется дата, следующая через десять рабочих дней после 8 января 2010 года. Рабочими днями считаются будние дни недели (с понедельника по пятницу).
Формула возвращает значение 20 января 2010 года (между 8 и 20 января выпадает два выходных дня).
Предупреждение
Имейте в виду, что результат, возвращаемый данной формулой, полностью зависит от региональных форматов даты. В других странах приведенная выше жестко закодированная дата может интерпретироваться как 1 августа 2010 года. Чтобы избежать этого, используйте следующую формулу:
Интересно, что второй аргумент функции РАБДЕНЬ может быть отрицательным. Как и в случае с функцией ЧИСТРАБДНИ, функция РАБДЕНЬ может использовать третий не обязательный аргумент, который определяет диапазон ячеек с датами, относящимися к праздничным дням и дням отпуска.
Вычисление количества лет между двумя датами
Следующая формула вычисляет количество лет между двумя датами. В данном случае предполагается, что ячейки А1 и В1 содержат даты.
Для извлечения года из каждой заданной даты в этой формуле используется функция ГОД, после чего один год вычитается из другого. Имейте в виду следующее: если В1 содержит более близкий к текущей дате год, чем дата в ячейке А1, результат будет отрицательным.
Следует обратить внимание, что данная функция не вычисляет часть года. Например, если ячейка А1 содержит дату 31.12.2010, а ячейка В1 – дату 01.01.2011, то формула вернет разницу в один год, несмотря на то, что эти даты отличаются всего лишь на один день.
Для вычисления количества лет между двумя датами можно также использовать функцию ДОЛЯГОДА. Эта функция возвращает количество лет, включая неполные, например:
Так как функцию ДОЛЯГОДА часто используют в финансовых приложениях, в ней существует необязательный третий аргумент, представляющий “базис” года. Значение 1 этого аргумента указывает на текущий год.
Вычисление возраста человека
Возраст человека – это количество полных лет, которые человек уже прожил. Формула, приведенная для вычисления количества лет между двумя датами в предыдущем разделе, не сможет корректно определить это значение. В этом случае нужно использовать две другие формулы.
Следующая формула возвращает возраст человека, дата рождения которого содержится в ячейке А1. Эта формула использует функцию ДОЛЯГОДА.
Ниже приведена формула, которая применяет для вычисления возраста функцию РАЗНДАТ.
Где найти функцию РАЗНДАТ
В этом разделе неоднократно упоминалась функция РАЗНДАТ. Обратите внимание, что ее нет в числе доступных в диалоговом окне Мастер функций, также она не содержится в списке автозавершения формулы. Поэтому для того, чтобы использовать эту функцию, ее необходимо ввести вручную.
Функция РАЗНДАТ была известна еще со времен Lotus 1-2-3, и, очевидно, с целью обеспечения совместимости Excel поддерживает ее. По определенным причинам Microsoft старается сохранить эту функцию в секрете. Интересно, что в версиях программы ниже Excel 2000 функция РАЗНДАТ даже не упоминалась. Из интерактивной справки Excel 2002 были удалены все ссылки на нее, хотя сама функция в этой версии приложения все еще доступна.
Тем не менее, функция РАЗНДАТ очень удобна. Она позволяет вычислить количество дней, месяцев и лет, находящихся между двумя датами. В ней используется три аргумента: начальная_дата, конечная_дата и определенный код_единицы_измерения, с помощью которого задается единица времени. Код в функции всегда заключается в кавычки. Ниже приведена таблица, в которой представлены допустимые коды для третьего аргумента.
Имейте в виду, что аргумент начальная_дата должен находиться левее, чем аргумент конечная_дата, в противном случае функция возвращает ошибку. Кроме того, в русифицированной Excel коды единиц измерения должны вводиться английскими буквами.
Вычисление дня года
Итак, 1 января – это первый день года, а 31 декабря – последний. Как определить, какой по счету день приходится на произвольную дату года? Следующая формула возвращает порядковое число дня года для даты, содержащейся в ячейке А1:
Если задать аргумент день равным нулю, функция ДАТА интерпретирует его как последний день предыдущего месяца. Можно задавать отрицательные значения, они интерпретируются аналогично.
Приведенная ниже формула возвращает день года текущей даты:
Следующая формула возвращает количество дней в году, которые остались после наступления даты, заданной в ячейке А1:
Обратите внимание, что при вводе любой из этих формул Excel применяет к ячейке формат даты, и чтобы увидеть полученный результат, необходимо применить к ячейке числовой формат.
Следующую формулу рекомендуется использовать для того, чтобы преобразовать заданное порядковое число дня года (например, девяностый день) в определенную дату и год, которые соответствуют этому дню. В этой формуле предполагается, что содержимое ячейки А1 – это год, а содержимое ячейки В1 – это день года.
Вычисление дня недели
Для вычисления дня недели часто используют функцию ДЕНЬНЕД. Она принимает в качестве аргумента дату и возвращает целое число, соответствующее дню недели, в диапазоне между 1 и 7. Следует отметить, что в данном случае отсчет дней начинается с воскресенья, как это принято в англоязычных программных продуктах. Например, следующая формула возвращает значение 5, поскольку первый день 2010 года выпадает на пятницу:
Функция ДЕНЬНЕД может принимать и второй, необязательный аргумент, который определяет систему нумерации дней недели для вывода результата. Если в качестве второго аргумента введено значение 2, то функция возвращает значение 1 для понедельника, 2 – для вторника и т.д. Если же в качестве второго аргумента ввести значение 3, то функция вернет 0 для понедельника, 1 – для вторника и т.д.
Для того, чтобы вычислить день недели даты в ячейке, можете использовать собственный числовой формат. Выберите команду Главная→Число→Числовой формат→Другие числовые форматы. В поле Тип диалогового окна Формат ячеек введите дддд и щелкните на кнопке ОК. Введите в ячейку дату (например, 22.07.2010). В результате в ячейке будет отображено четверг – день недели введенной даты.
Вычисление даты прошлого воскресенья
Хотите узнать, на какую дату приходилось прошедшее воскресенье? Воспользуйтесь формулой, приведенной ниже. Но имейте в виду, если текущая дата выпадает на воскресенье, то формула возвращает текущую дату.
Для того чтобы найти дату другого дня, а не воскресенья, измените аргумент 1 в формуле на любой другой в диапазоне между 2 (найти понедельник) и 7 (суббота).
Вычисление дня недели, следующего после заданной даты
Приведенная ниже формула возвращает дату заданного дня недели, следующего за определенной датой. Например, эту формулу можно использовать, чтобы вычислить дату первого понедельника после 1 июня 2010 года. Предположим, что ячейка А1 содержит дату, а ячейка А2 – число между 1 и 7 (1 соответствует воскресенью, 2 – понедельнику и т.д.).
Для этой формулы приняты следующие условия:
• ячейка А1 содержит значение года;
• ячейка А2 содержит значение месяца;
• ячейка А3 содержит значение дня (1 для воскресенья, 2 – для понедельника и т.д.);
• ячейка А4 содержит номер вхождения (к примеру, 2 для выбора второго вхождения дня недели, заданного в ячейке А3).
Например, если использовать эту формулу для определения даты второй пятницы в июне 2010 года, формула вернет дату 8 июня 2010 года.
Примечание
Если значение, содержащееся в ячейке А4, не принадлежит к диапазону дней указанного месяца, то формула вернет дату, относящуюся к следующему месяцу. Например, если задать определение даты шестой пятницы июня 2010 года (а такой даты не существует), то формула вернет дату первой пятницы июля.
Вычисление количества заданных дней недели в месяце
Формула, приведенная ниже, позволяет вычислить количество определенных дней недели заданного месяца. Предположим, ячейка А1 содержит дату, а ячейка А2 – порядковое число дня недели (значение 1 соответствует воскресенью, 2 – понедельнику и т.д.). Имейте в виду, что это формула массива, поэтому, чтобы ввести ее, используйте клавиши .
Если ячейка А1 содержит дату 8 января 2010 года, а ячейка В1 – значение 3, т.е. вторник, формула возвращает значение 4. Это значит, что январь 2010 года содержит четыре вторника.
Приведенная выше формула массива вычисляет год и месяц, используя функции ГОД и МЕСЯЦ. Однако ее можно немного упростить. Для этого введите год и месяц заданной даты в отдельные ячейки. Для приведенной ниже формулы, которая тоже является формулой массива, приняты условия, что ячейка А1 содержит значение года, ячейка А2 – месяца и ячейка В1 – значение дня недели.
На рисунке показан результат использования этой формулы на рабочем листе. В этом случае используются и абсолютные, и смешанные ссылки на ячейки, поэтому можно скопировать эту формулу. К примеру, формула в ячейке СЗ выглядит следующим образом:
Итоговые формулы на этом листе используют функцию СУММ. Они вычисляют общее количество дней в месяце (столбец J) и количество каждого дня недели в году (строка 15).
Отображение даты в текстовом виде
Иногда может потребоваться отобразить дату в виде, не предусмотренном ни в одном встроенном формате дат. К примеру, с помощью приведенной ниже формулы дату 16.3.2010 можно отобразить как 16 марта 2010 г. Предполагается, что стандартная запись даты находится в ячейке А1.
Примечание
Результат этой формулы имеет текстовый формат, а не формат даты.
Конечно, такое преобразование может быть полезным, только если предусмотреть выбор суффикса с помощью ряда функций ЕСЛИ (например, для апреля нужно применить суффикс я и удалить ь).
Вычисление праздничных дат
Вычисление дат, выпадающих на специфические праздничные дни, требует использования специальных методов. Безусловно, это не касается таких дат, как Новый год или День Независимости США, поскольку они определены издавна. Для них можно просто использовать функцию ДАТА, о которой речь шла ранее. Следующая формула отображает дату Нового года. Новый год всегда приходится на 1 января; нужный год введите в ячейку А1.
На самом деле проблем не возникает только с праздниками, постоянно отмечаемыми по строго определенным датам. А как быть с праздничными днями, дату которых определяет временной период? К таким праздникам в США, например, относится День труда, который отмечается в первый понедельник сентября.
На рисунке показана рабочая книга Excel, в которой вычисляются даты нескольких праздничных дней. Все формулы содержат ссылку на ячейку А1 – год, для которого вычисляются даты. Поскольку такие праздники, как Новый год и День Независимости, отмечаются в строго определенные дни года, для вычисления их дат используется только функция ДАТА.
Новый год
Новый год отмечается первого января.
День Мартина Лютера Кинга
Этот праздничный день приходится на третий понедельник января. Далее мы узнаем, на какую дату выпадет этот день в 2010 году, определенном в ячейке А1.
Основные принципы работы с датами и временем в Excel
Как обычно, кому надо быстро — смотрим видео. Подробности и нюансы — в тексте ниже:
Как вводить даты и время в Excel
Если иметь ввиду российские региональные настройки, то Excel позволяет вводить дату очень разными способами — и понимает их все:
С использованием дефисов
С использованием дроби
Внешний вид (отображение) даты в ячейке может быть очень разным (с годом или без, месяц числом или словом и т.д.) и задается через контекстное меню — правой кнопкой мыши по ячейке и далее Формат ячеек (Format Cells) :
Время вводится в ячейки с использованием двоеточия. Например
По желанию можно дополнительно уточнить количество секунд — вводя их также через двоеточие:
И, наконец, никто не запрещает указывать дату и время сразу вместе через пробел, то есть
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:
Ссылки по теме
Посмотрите http://planetaexcel.ru/techniques/6/44/
Не оно?
Продублирую вопросик из форума — для функций РАБДЕНЬ и ЧИСТРАБДНИ можно ли:
1. ввести функцию сслыки на список праздников
2. сделать эту функцию доступной для любого файла Excel?
Спасибо Вам огромное Николай за очень полезные и познавательные ролики которые Вы выкладываете\
Просто большое человеческое Спасибо.
Спасибо на добром слове, Анатолий! Будем стараться и дальше вас радовать
Николай,
доброго времени суток. Я сейчас изучаю excel интересным способом: у меня есть товарищ, гуру эксель. =) он даёт задачи, а я их решаю. В решениии помогают Google и всякие книжки. но есть вещи которых я просто не могу там найти. А каждый раз обращаться по «тупому» вопросу с товарищу тоже неудобно.
Сейчас я «встал» на такой вот задаче.
Есть 3 ячейки. 1 ячейка начальная дата (любая), 2 ячейка конечная дата (тоже любая). В 3 ячейке надо дать сумму рабочих дней между этими 2 датами. Однако выходные дни СРЕДА и СУББОТА или только СРЕДА.
Я так понимаю, что это надо сделать через формулу массивов и функции счётесли. Однако просто не понимаю, как можно из двух дат создать массив (двссыл тут не работает).
очень прошу помочь.
Чуть не забыл, для расчётов надо использовать только 1 ячейку.
Решение найдено
=SUM(IF(WEEKDAY(ROW(INDIRECT(A3&»:»&B3));2)<>3;1;0))
всеравно спасибо
Не за что
Добрый день! Помогите, пожалуйста!
Нужно вычислить кол-во часов между двумя ячейками, формат время.
Если в 2х ячейках время АМ
А1=ВРЕМЯ(2;15;0)
В1=ВРЕМЯ(8;0;0)
то =В1-А1 вычисляет нормально = 5,45 часов
но если в одной яч. время РМ после полудня, а в др. АМ до полудня
А1=ВРЕМЯ(23;0;0)
В1=ВРЕМЯ(7;0;0)
то =В1-А1 = ################ — отрицательные или слишком большие дата и время
почему так происходит и как исправить формулу, чтобы получить часы?
Марина, за 6 лет вам так никто и не ответил, но я нашел решение! Такая же проблема была.
Вот во всех этих ячейках, где ############# прописать формулу: =1-ABS(A1-B1) тогда будет реально вычисляться сколько часов прошло.
PS Надеюсь ещё не позно
Александр, если в ячейке А1 стоит дата (например 13.01.2014), а в ячейке А2 количество календарных дней (например 14, предполагается что дата из ячейки А1 тоже учитывается), то формула перекидывающая выходной день на предшествующую пятницу может выглядеть так:
Добрый день, помогите пожалуйста, составить формулу для следующего: имеется долгий лист с датами истечения контракта: нужна формула чтобы от TODAY+5 предположим сигнализировала например изменением цвета
Спасибо! А вы не могли бы объяснить, пожалуйста, как её применять? Потому что 1900 год, после подключения надстройки, он всё равно объявляет номером 1. Возможно, где то должна была появиться доп функция, но я её не углядел
Спасибо!
Это вопрос, скорее, к автору Но, насколько я могу судить по описанию, после подключения надстройки у вас должны появиться новые функции в категории Определенные пользователем .
first of all, thank you very much for your work! It helps A LOT!
one question regarding time format:if time es exported as text like the following
| 08090439 |
how can i convert into time format?
Thank you in advance!
Доброго всем дня, подскажите пожалуйста в формулой по вычислению суммы пени по просрочке
Условие: есть дата оплаты план (П.Дата) и есть дата платы Факк (Ф.Дата), есть сумма на оплату к примеру 10 000(Сумма) и коэф пени например 0,01%(Пени)
Примерно вычисление должно выглядеть след образом : (П.Дата — Ф.Дата ) * Сумма * Пени , т.е.
26 авг — 30 авг= -4 ( Дни просрочки)
4* 10 000*0,01%
Все бы хорошо, но есть одно НО. если формулу протягивать на н-ое количество листов , то она считает все подряд и просроченнные и не просроченные платежи. Т.к. разница дат выходит как с минусом — это и есть просрочка, так и с плюсом — эти платежи еще в перспективе.
Пытался прикрутить условие «Если» , т.е. если дни в разнице дат меньше нуля, то показывается значение с минусом, что собственно и должно идти в расчет, а если дни в разнице дат больше нуля то должен отображаться ноль и все перемножения коэфицентов будут равны нулю, что тоже есть правильно. но при вставке данной формулы выходит ошибка , мол разные еденицы измерения в расчетах. и в графе где по условию должны отображаться дни со значением минус -отображается значение ячейки в которой все это прописано
Всем затык, понять не могу, по логике вроде все верно, а по факту не получается, может у кого есть решение данного вопроса или аналогичные приемы для решения?
Какая из формул выводит дату следующего дня
То есть 12.07.2016 12:50:30 для Excel значение — 42563,5350694,
Где 42563 — это порядковый номер дня с 1 января 1900 года, а часть после запятой — это время.
С датами можно производить вычисления. Например, вычитать или складывать даты.
Основные ошибки с датами и их решение
Перевод разных написаний дат
Разные системы в выгрузках выдают даты по-разному, например: 12.07.2016 12-07-16 16-07-12 и так далее. Иногда месяца пишут текстом. Для того, чтобы привести даты к одному формату мы используем функцию ДАТА:
Данный вариант подходит, когда количество символов в дате одинаковое. Если вы работаете с однотипными выгрузками еженедельно, создание дополнительного столбца и протягивание формулы будет простым решением.
Дата определяется как текст
Проблемы с датами начинаются, когда мы импортируем данные из других источников. При этом, даты могут выглядеть нормально, но при этом являться текстом. С ними нельзя проводить вычисления, группировки в сводных таблицах, сортировать. Есть 2 решения, которые позволяют сделать это автоматически: 1. Получение значения даты; 2. Умножение текстового значения на единицу.
Получение значения даты
С помощью формулы ЗНАЧ мы выводим текстовое значение даты, потом его форматируем как Дату:
Умножение текстового значения на единицу
Принцип аналогичный предыдущему, только мы текстовое значение умножаем на единицу и после этого форматируем как дату:
Примечание: если дата определилась как текст, то вы не сможете делать группировки. При этом дата будет выровнена по левому краю. Excel выравнивает числа и даты по правому краю.
Очистка дат от некорректных символов
Чтобы привести к нужному стандарту, часть дат можно очистить с помощью функции Найти и Заменить. Например, поменять слэши («/») на точки: