Как в Excel вставить текущую дату и время, как найти количество дней между датами (в т. ч. рабочих)

Как в Excel вставить текущую дату и время, как найти количество дней между датами (в т.ч. рабочих)

excel-segodnyaДоброго дня!

В некоторых случаях при работе с документами Excel очень нужно, чтобы проставлялась текущая дата: например, вы часто делаете отчеты по одной и той же форме (глаз со временем «замыливается» и их можно перепутать) ; или, скажем, у вас есть десяток-другой задач и вы каждый день по ним просматриваете как продвигается дело, и сколько дней осталось до завершения.

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

Примечание: скриншоты в статье приведены из Excel 2019 (материал актуален также для версии 2016, 2013).

Дополнение!

Если с вашими документами иногда работают посторонние люди, рекомендую некоторые ячейки защищать от изменения — https://ocomp.info/zashhita-yacheek-v-excel.html

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

ШАГ 1: вставка текущей даты

Самый простой и быстрый вариант поставить в ячейку текущую дату и время — это воспользоваться функцией » ТДАТА() » (достаточно скопировать в любую ячейку Excel, разумеется, без кавычек) . См. пример ниже.

Формула "ТДАТА()"

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

Формула "СЕГОДНЯ()"

ШАГ 2: сколько всего дней между двумя датами

Это еще один популярный вопрос.

Определить общее количество дней между двумя датами можно с помощью формулы » =ДНИ(B2;A2) » (где B2 и A2 — конечная и начальная дата, соответственно) . Разумеется, зная значение в днях — легко узнать и значение в годах (достаточно разделить число на 365. ).

Ко-во дней посчитано

Ко-во дней посчитано

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

Второй вариант

ШАГ 3: определение кол-ва рабочих дней между датами

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

В этом случае может помочь формула » =ЧИСТРАБДНИ(A2;B2) » (где A2 и B2 — начальная дата и конечная, соответственно) .

51 рабочий день

51 рабочий день

Кстати, если вам нужно подсветить ячейки красно-зеленым цветом (чтобы сразу же видеть те строки, где, например, горят сроки — рекомендую использовать инструмент «Условное форматирование» в разделе «Главная» ). Пример ниже.

Правила выделения ячеек

Правила выделения ячеек

В общем-то, выглядит это все достаточно наглядно (например, те задачи, которым осталось времени на выполнение меньше 4 дней — у меня в отчете подсвечиваются красным. Удобно!)

Примечание : для обновления текущей даты и времени — нажмите сочетание кнопок Ctrl+R.

В чем отличия от табеля учета рабочего времени

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

К табелю не установлено каких-либо особенных требований об ознакомлении в определенный срок и нет необходимости согласовывать его с представительным органом служащих. Работодателю остается только не превышать установленную продолжительность рабочего дня и предоставлять работающим отдых в соответствии с требованиями ст. 110 ТК РФ.

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

готовые таблицы для заполнения графика работы

Функция ЧИСТРАБДНИ в Excel

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

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

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

  • Дата начала — Дата начала.
  • Дата окончания — Дата окончания.
  • каникулы — [необязательно] Список нерабочих дней в виде дат.

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

Функция ЧИСТРАБДНИ также может исключать праздники, указанные в списке дат. Например, с праздничными датами в C1: C5:

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

NETWORKDAYS рассчитывает весь рабочих дней без учета временных значений.

NETWORKDAYS автоматически исключает субботу и воскресенье. Если у вас есть собственный график выходных, см. NETWORKDAYS.INTL функция.

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

Функция РАБДЕНЬ для расчета количества рабочих дней в Excel

А нажать F1 и задать вопрос -слабо?

Это выдержка из файла помощи на запрос "рабочие дни". Видите как просто.

Толку в ней мало, переносы и праздники ты всё равно, фактически, учитываешь руками.

Не надо быть столь категоричным.

Государственных празников не так уж много. Сделать список исходных праздников просто.

Параллельно с ним "сдвинутые" даты, получаемые путем анализа на какой день недели

приходится праздник (все встроенными функциями). И уже этот "паралельный" Range

Функция РАБДЕНЬ.

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

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

РАБДЕНЬ — это встроенная функция в Excel 2007, 2010, 2013 и 2016. Ее можно ввести руками либо при помощи мастера функций. В более ранних версиях вам нужно специально включать пакет инструментов анализа.

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

РАБДЕНЬ(дата_начала; дни; [праздничные_дни])

Первые два обязательны, а последний – по желанию:

  • Дата_начала – с нее начинается подсчет дней.
  • Дни — рабочие дни, которые нужно добавить или вычесть из начальной точки отсчета. Положительное число возвращает будущий момент времени, отрицательное — возвращает в прошлое.
  • Праздничные_дни — необязательный список дат, которые следует считать нерабочими днями. Это может быть либо диапазон ячеек, содержащих даты, которые вы хотите исключить из расчетов, либо константа массива.

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

Синтаксис функции:

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

Аргумент начальная_дата должна быть раньше аргумента конечная_дата.

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

Значение

Описание

разница в полных месяцах

разница в полных годах

разница в полных месяцах без учета лет

разница в днях без учета месяцев и лет
ВНИМАНИЕ! Функция для некоторых версий EXCEL возвращает ошибочное значение, если день начальной даты больше дня конечной даты (например, в EXCEL 2007 при сравнении дат 28.02.2009 и 01.03.2009 результат будет 4 дня, а не 1 день). Избегайте использования функции с этим аргументом. Альтернативная формула приведена ниже.

разница в днях без учета лет
ВНИМАНИЕ! Функция для некоторых версий EXCEL возвращает ошибочное значение. Избегайте использования функции с этим аргументом.

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

В файле примера значение аргумента начальная_дата помещена в ячейке А2, а значение аргумента конечная_дата – в ячейке В2.

Задание 2. Создать таблицу изменения количества рабочих дней наемных работников и произвести расчет средних значений. Построить график по данным таблицы.

1. Запустите редактор электронных таблиц Microsoft Excel(при стандартной установке MS Officeвыполните Пуск/Программы/Microsoft Excel).

2. Откройте файл «Расчеты», созданный в Практических работах 1 и 2 (Файл/Открыть).

3. Переименуйте ярлычок Лист 5, присвоив ему имя «Динамика цен».

4. На листе «Динамика цен» создайте таблицу по образцу, как на рис.1.

5. Произведите расчет изменения цены в колонке «Е» по формуле

Изменение цены = Цена на 01. 06.2003/Цена на 01. 04.2003.

Не забудьте задать процентный формат чисел в колонке «Е» (Формат/ Ячейки/ Число/ Процентный).

6. Рассчитайте средние значения по колонкам, пользуясь мастером функций fx. Функция СРЗНАЧнаходится в разделе «Статистические». Для расчета функции среднего значения установите курсор в соответствующей ячейке для расчета среднего значения (В 14), запустите мастер функций (кнопкой Вставка функции fxили командой Вставка/ Функция) и на первом шаге мастера выберите функцию СРЗНАЧ(категория Статистические/СРЗНАЧ) (рис. 2).

После нажатия на кнопку ОК откроется окно для выбора диапазона данных для вычисления заданной функции. В качестве первого числа выделите группу ячеек с данными для расчета среднего значения В6:В13 и нажмите кнопку ОК(рис..3). В ячейке В14 появится среднее значение данных колонки «В».

Читайте также:  Примеры формулы ГПР для выборки по строкам с условием в Excel

Аналогично рассчитайте средние значения в других колонках.

7.В ячейке А2 задайте функцию СЕГОДНЯ, отображающую текущую дату, установленную в компьютере <Вставка/Функция/Дата и Время/Сегодня).

8.Выполните текущее сохранение файла <Файл/Сохранить).

Задание 2. Создать таблицу изменения количества рабочих дней наемных работников и произвести расчет средних значений. Построить график по данным таблицы.

Исходные данные представлены на рис.4.

Порядок работы

1. На очередном свободном листе электронной книги «Расчеты» создайте таблицу по заданию. Объединение выделенных ячеек произведите кнопкой панели инструментов Объединить и поместить в центреили командой меню (Формат/Ячейки/вкладка Выравнивание/отображениеОбъединение ячеек).

Краткая справка. Изменение направления текста в ячейках производится путем поворота текста на 90° в зоне Ориентацияокна Формат ячеек, вызываемого командой Формат/ Ячейки/вкладка Выравнивание/Ориентация поворот надписи на 90° (рис.5).

2. Произвести расчет средних значений по строкам и столбцам с использованием функции СРЗНАЧ.

3. Построить график изменения количества рабочих дней по; годам и странам. Подписи оси «X» задайте при построении графика на втором экране мастера диаграмм (вкладка Ряд, область Подписи оси «X»).

4. После построения графика произведите форматирование вертикальной оси, задав минимальное значение 1500, максимальное значение 2500, цену деления 100 (рис. 6). Для форматирования оси выполните двойной щелчок мыши по ней и на вкладке Шкаладиалогового окна Формат осизадайте соответствующие параметры оси.

5. Выполните текущее сохранение файла «Расчеты» (Файл/Сохранить).

Задание 3. Применение функции ЕСЛИ при проверке условий. Создать таблицу расчета премии за экономию горючесмазочных материалов (ГСМ).|

Исходные данные представлены на рис.7.

Порядок работы

1. На очередном свободном листе электронной книги «Расчеты» создайте таблицу по заданию.

2. Произвести расчет Премии (25 % от базовой ставки) по формуле:

Премия = Базовая ставка*0,25 при условии, что|

План расходования ГСМ > Фактически израсходовано ГСМ.

Для проверки условия используйте функцию ЕСЛИ.

Для расчета Премии установите курсор в ячейке F4, запустите мастер функций (кнопкой Вставка функции fxили командой Вставка/Функция) и выберите функцию ЕСЛИ(категория — Логические/ ЕСЛИ).

Задайте условие и параметры функции ЕСЛИ (рис. 8).

ü В первой строке «Логическое выражение» задайте условие С4 > D4.

ü Во второй строке задайте формулу расчета премии, если условие выполняется Е4 * 0,25.

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

3. Произведите сортировку по столбцу фактического расходования ГСМ по возрастанию. Для сортировки установите курсор на любую ячейку таблицы, выберите в меню Данныекоманду Сортировка, задайте сортировку по столбцу «Фактически израсходовано ГСМ» (рис. 9).

4. Конечный вид расчетной таблицы начисления премии приведен на рис.10.

5. Выполните текущее сохранение файла «Расчеты» <Файл/Сохранить).

Стандартная задача: нам известна дата начала работ и отведенное на это количество рабочих дней. Нужно определить дату окончания работ. Как мы это сделаем? С помощью функции РАБДЕНЬ(Начальная дата;Количество дней;[Праздники]) . У этой функции три аргумента:

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

Вот пример использования этой функции:

Функция РАБДЕНЬ

Но что если у вас другой график выходных? Например, вы отдыхаете только в воскресенье? Используйте похожую функцию – РАБДЕНЬ.МЕЖД(Начальная дата;Количество дней; [Выходные];[Праздники]) . Здесь появился еще один необязательный параметр – Выходные. Он как раз и позволяет задать нужный график выходных. Когда вы вводите этот параметр, программа подсказывает что выбрать для правильной настройки выходных дней:

Функция РАБДЕНЬ.МЕЖД

В остальном, она работает так же, как и предыдущая.

Учет выходных и праздничных дней

На следующем этапе нам предстоит создать формулу, которая при соблюдении одного из условий (праздничный день, выходной день или праздничный день, выпадающий на выходной) возвращает значение ИСТИНА. Мы предполагаем разместить эту формулу в ячейке СЗО.

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

Функция ИЛИ имеет следующий синтаксис:

Здесь логииеское_значение1, логическое_значение2, . — это от 1 до 30 проверяемых условий, которые могут иметь либо значение ИСТИНА, либо значение ЛОЖЬ.

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

Формула в ячейке СЗО должна быть такой:

Произведем вложение функций:

  • Формулу из ячейки С25 вложим в формулу из ячейки С26: =ЕНД(ВПР(С9;ДатаПраздн;1;ЛОЖЬ))
  • — Полученную формулу вложим в формулу из ячейки С27: =НЕ(ЕНД(ВПР(С9;ДатаПраздн;1;ЛОЖЬ)))
  • Формулу из ячейки С28 вложим в формулу из ячейки С29: =ЕСЛИ(ДЕНЬНЕД(С9)=7/ИСТИНА;ЕСЛИ(ДЕНЬНЕД(С9)=1;ИСТИНА;ЛОЖЬ))
  • Полученную формулу вложим в формулу из ячейки СЗО: =ИЛИ(ЕСЛИ(ДЕНЬНЕД(С9)=7;:ИСТИНА;ЕСЛИ(ДЕНЬНЕД(С9)=1;ИСТИНА;ЛОЖЬ)) ;С27)

Автоматизация табеля с помощью формул

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

Для примера возьмем такие варианты:

  • В – выходной;
  • Я – явка (рабочий день);
  • ОТ – отпуск;
  • К – командировка;
  • Б – больничный.

Сначала воспользуемся функцией «Выбор». Она позволит установить нужное значение в ячейку. На этом этапе нам понадобится календарь, который составляли в Поле Оператора. Если на какую-то дату приходится выходной, в табеле появляется «В». Рабочий – «Я». Пример: =ВЫБОР(D$3+1;»Я»;»В»). Формулу достаточно занести в одну ячейку. Потом «зацепить» ее за правый нижний угол и провести по всей строке. Получается так:

Теперь сделаем так, чтобы в явочные дни у людей стояли «восьмерки». Воспользуемся функцией «Если». Выделяем первую ячейку в ряду под условными обозначениями. «Вставить функцию» – «Если». Аргументы функции: логическое выражение – адрес преобразуемой ячейки (ячейка выше) = «В». «Если истина» — «» или «0». Если в этот день действительно выходной – 0 рабочих часов. «Если ложь» – 8 (без кавычек). Пример: =ЕСЛИ(AW24=»В»;»»;8). «Цепляем» нижний правый угол ячейки с формулой и размножаем ее по всему ряду. Получается так:

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

Теперь подведем итоги: подсчитаем количество явок каждого работника. Поможет формула «СЧЁТЕСЛИ». Диапазон для анализа – весь ряд, по которому мы хотим получить результат. Критерий – наличие в ячейках буквы «Я» (явка) или «К» (командировка). Пример: . В результате мы получаем число рабочих для конкретного сотрудника дней.

Посчитаем количество рабочих часов. Есть два способа. С помощью функции «Сумма» — простой, но недостаточно эффективный. Посложнее, но надежнее – задействовав функцию «СЧЁТЕСЛИ». Пример формулы: . Где AW25:DA25 – диапазон, первая и последняя ячейки ряда с количеством часов. Критерий для рабочего дня («Я»)– «=8». Для командировки – «=К» (в нашем примере оплачивается 10 часов). Результат после введения формулы:

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

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

Функция РАБДЕНЬ для расчета количества рабочих дней в Excel

Внешний вид (скрин-сейв) графика смен

График смен скачать

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

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

Предложенная форма-образец графика работы сотрудников разработана на основе графика работы магазина (торговой точки). Форма-образец графика автоматически строит графики работ сотрудников с учетом указанных критериев:

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

Суммарное количество рабочих часов соответствует требованиям Трудового кодекса.

Файлы содержат программу (макрос) для автоматических расчетов. При открытии файла включайте макросы!

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


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

Для начала, вы должны знать, что Excel видит любую дату как обыкновенное числовое значение, которое начинается с цифры 1 и соответствует дате 1 января 1900 г. К примеру, 1 января 2020 г. будет равняться числу 43101 и разница между датами составит 43100 дней. А ввиду этого мы очень просто можем манипулировать датами, так же, как и числами, а это значительно делает проще нашу работу.

Читайте также:  Изменяемые диапазоны и функция промежуточные итоги в Excel

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

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

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

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

Синтаксис этой функции достаточно прост и понятен:

= ЧИСТРАБДНИ(_нач_дата_; _кон_дата_; _[праздники]_), где:

  • Начальная дата – является обязательным аргументом и указывается дата начала отсчёта;
  • Конечная дата – также обязательный аргумент и указывает дату окончания периода расчётов;
  • Праздники – не обязательный аргумент, являет собой список дат или ссылку на них, которые следует изъять из расчётов, например, государственные или религиозные праздники.

Простой пример:

У нас есть даты начала периода в диапазоне ячеек А2:А5 и даты конца периода – В2:В5. Используя нашу функцию, мы получаем нужный нам интервал в рабочих днях.


Сложный пример:

Усложним нашу задачу добавив несколько праздничных дней в диапазоне Е2:Е5. Тогда наша формула пересчитает рабочие дни и отминусует праздники, попавшие в вычитаемый период.


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

А на этом у меня всё! Я очень надеюсь, что всё вышеизложенное вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями, прочитанным и ставьте лайк!

Посчитать количество выходных дней в excel. Определение выходных дней в табеле

Разберем, как посчитать рабочие дни в Excel без учета праздничных дней. Как определить, какой рабочий день будет впереди, или был раньше через указанное количество дней. Для этого есть функция «РАБДЕНЬ» в Excel, которая определяет дату вперед или назад на количество рабочих дней от указанной даты.
Внимание!
В ячейках с датой должен стоять формат «Дата». Про формат чисел смотрите «Число Excel. Формат».
Прибавить рабочие дни к дате Excel .
Например, нам нужно узнать, какая будет дата через 28 рабочих дней. У нас такая таблица с данными. Внимание!
В ячейках, в которых пишем даты, нужно установить формат ячеек – «Дата». Иначе, функция работать не будет.
Нажимаем на ячейку A3. На закладке «Формулы» в разделе «Библиотека функций» нажимаем на функцию «Дата и время». Из появившегося списка выбираем функцию «РАБДЕНЬ». Диалоговое окно функции заполняем так.

Нажимаем «ОК». Получилось так.
К дате 12.02.2000 г. прибавили 28 рабочих дней, и получилась дата 21.02.2000г.
Если нужно посчитать с дополнительными праздничными днями, то запишем их в таблице. Официальные праздники считаются автоматически. Тогда диалоговое окно функции заполним так.

Ячейки с праздниками мы записали как диапазон – через двоеточие. Нажимаем «ОК». Получилось так.

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

Это глава из книги Билла Джелена .

Задача: подсчитать, сколько рабочих дней попадает между двумя датами. Функция Excel ЧИСТРАБДНИ прекрасно справляется с задачей, если у вас 5-дневная рабочая неделя с субботой и воскресеньем в качестве выходных. Эта заметка покажет вам, как выполнить расчет рабочей недели любой продолжительности и любым набором выходных.

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

ЧИСТРАБДНИ вычисляет количество рабочих дней между двумя датами, включая начальную и конечную даты. Следует указать более раннюю дату в качестве первого аргумента, более позднюю дату в качестве второго аргумента, и дополнительно определить массив праздничных дней в качестве третьего аргумента (рис. 1). Формула в ячейки C3: =ЧИСТРАБДНИ(A3;B3;$E$2:$E$11). 17.02.09 вычесть 10.02.09 дает 8 календарных дней, минус два выходных и один праздничный день. Итого, 5 рабочих дней. Однако, если вам «посчастливилось» работать с понедельника по субботу, стандартная формула не справится с задачей.

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

Для начала определите диапазон, и назовите его Праздники. Для этого выделите диапазон вместе с заголовком и пройдите по меню ФОРМУЛЫ –> Создать из выделенного (рис. 2). Убедитесь, что выбрана опция в строке выше . Кликните Ok в окне Создание имени из выделенного диапазона .

Определив диапазон, снова введите формулу в С3. Теперь она примет вид: =ЧИСТРАБДНИ(A3;B3;Праздники)

Теперь перейдем к расчету числа рабочих дней при 6-дневной рабочей недели (рис. 3). Формула в С3: =СУММПРОИЗВ(- — (СЧЁТЕСЛИ(Праздники;СТРОКА(ДВССЫЛ(A3& » : » &B3)))=0); — — (ДЕНЬНЕД(СТРОКА(ДВССЫЛ(A3& » : » &B3));2)

Как посчитать количество рабочих дней в Экселе (Excel)?

Как посчитать количество рабочих недель в программе эксель (Excel) автоматически?

Для подсчета рабочих дней используется функция

Нач.дата — ячейка или выражение, содержащая начальную дату

Конеч.дата — ячейка или выражение, содержащая конечную дату

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

Сумму четных или нечетных чисел диапазона можно подсчитать с помощью формул массива.

Если числа записаны в ячейках с A1 по A6, то тестируем на нечетность функцией ОСТАТ и вводим как формулу массива, чтобы операция умножения применялись к каждому числу.

Сумма нечетных чисел диапазона:

=СУММ( A1:A6*ОСТАТ( A1:A6*ОСТАТ( A1:A6 ;2))

Сумма четных чисел диапазона:

=СУММ( A1:A6*ОСТАТ( A1:A6*ОСТАТ( A1:A6+1 ;2))

=СУММ( A1:A6*ОСТАТ( A1:A6*ОСТАТ( A1:A6-1 ;2))

Эти формулы вводят в строку редактора формул как формулы массива, обязательно завершая ввод комбинацией клавиш CTRL+Shift+Enter (после этого Эксель ставит фигурные скобки вокруг формулы) .

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

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

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

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

Я очень много работаю в MicroSoft Excel и не перестаю поражаться его возможностям, поэтому изложить какие-то конкретные "фишки" очень сложно — всё зависит от поставленной задачи, которую нужно решить.

В изучении, так называемых, новых "фишек" мне помогает вот этот сайт planetaexcel.ru. Здесь Вы найдете уйму "фишек" и прочих "фокусов", а также видеоуроков по Excel.

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

Первый шаг. Открываете таблицу excel с вашим массивом данных.

Второй шаг. Выделите строки, которые вы желаете скрыть. Выделять можно, как каждую строку по отдельности. Либо сразу все строки, напомню, если у вас строки идут не подряд, то необходимо зажимать клавишу "ctrl".

Третий шаг. Когда все строки выделены, наведите курсор на название строк и нажмите правой кнопкой мыши, появится следующая таблица:

Четвертый шаг. Выберите в этой таблице строку "Скрыть". После этого ваши строки будут скрыты от вас.

Способ 2: использование функции

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

Общий

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

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

Ставим в В3 знак равенства, пишем РАЗНДАТ и открываем скобку. Затем выделяем раннюю дату ( А1 ), потом позднюю ( В1 ), ставим в кавычках подходящую единицу и закрываем скобку. Между всеми аргументами ставьте «;» . Для расчета, нажмите «Enter» .

У меня получилась такая формула:

Функция разницы

Выбрав в качестве единицы «d» , я получила результат – 111.

Если изменить данное значение, например, на «md» , тогда формула посчитает разницу между 5 и 24 без учета месяцев и годов.

Меняя таким образом данный аргумент, получится вывести точный возраст человека. В одной ячейке будут года «y» , второй месяцы «ym» , третьей дни «md»

Только дни

  1. В этом примере формула в D5: …
  2. Чтобы добавить или вычесть дни рабочих дней к дате, соблюдая праздники, но при условии 7-дневной рабочей недели, вы можете использовать функцию РАБДЕНЬ.МЕЖД. …
  3. =РАБДЕНЬ.МЕЖД(B6;7;»0000011″) …
  4. = B5 + C5. …
  5. В этом примере формула в D5: …
  6. В показанном примере формула в D5:

Пример 1. Прибавление дней к дате в Excel

  1. Cсылкой на ячейку: =A2+10.
  2. Обращением к функции ДАТА (DATE): =ДАТА(2015;5;6)+10. =DATE(2015,5,6)+10.
  3. Обращением к другой функции. Например, чтобы прибавить несколько дней к текущей дате, используйте функцию СЕГОДНЯ (TODAY): =СЕГОДНЯ()+10. =TODAY()+10.

Developing.ru

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

готовой функции на этот счет нет, а вот формулу написал

P.s. убедитесь что у Вас устновлен пакет анализа

да уж вынужден признать, формула — дерьмо. подошел к вопросу легкомысленно

вот новая версия решения поставленной проблемы
сначала на листе 2 делаете табличку
Час Час_отр
1 0
2 0
3 0
4 0
5 0
6 0
7 0
8 0
9 0
10 1
11 2
12 3
13 4
14 4
15 5
16 6
17 7
18 8
19 8
20 8
21 8
22 8
23 8

А вот и сама формула
=ЕСЛИ(ДАТА(ГОД(Конец);МЕСЯЦ(Конец);ДЕНЬ(Конец))-ДАТА(ГОД(Начало);МЕСЯЦ(Начало);ДЕНЬ(Начало))=0;0;ЧИСТРАБДНИ(Начало;Конец-1)*8)+ВПР(ЧАС(Конец);Лист2!$a$1:$b$24;2;1)-ВПР(ЧАС(Начало);Лист2!$a$1:$b$24;2;1)

P.s. обращаю внимание, что погрешность +/- 1 час — т.к. не учитываются минуты.
таблицу с данным сделал на основании Вашего примера: рабочий день 9 — 18, перерыв в 13:00 — 1 час

приведена функция пользователя на VBA.
Но там обед не учтен, ведь с 9 до 18 — 9 часов, а не 8 часов.
Вам «вырезка» обеда нужна?
хотя для практики считаю это не принципиальным,
кто-то в обед вообще не кушает

Вообще, вам для работы вопрос, или для обучения?

приведена функция пользователя на VBA.
Но там обед не учтен, ведь с 9 до 18 — 9 часов, а не 8 часов.
Вам «вырезка» обеда нужна?
хотя для практики считаю это не принципиальным,
кто-то в обед вообще не кушает

Определить количество рабочих (будних) дней в конкретном месяце и года

Помощь в написании контрольных, курсовых и дипломных работ здесь.

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

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

Определить количество дней для определенного месяца и определенного года
Составить программу, которая по заданным году и номеру месяца m определяет количество дней в этом.

Определить, сколько дней осталось до нового года
Создайте процедуру, определяющую, сколько дней осталось до нового года.

Это у кого какая работа*! Очевидно, вы ищете субботы и воскресенья . Будем думать (в сторону Weekday(Date) ).

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

Какие прошедшие года нужны:2009-2011 или 2000-2011 ?
В каком приложении это должно работать и как это должно использоваться?

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

Trika,
может пригодиться.

Скачено с другого сайта:

Есть ли у кого-нибудь производственный календарь на 2012 год по аналогии с таким вот чудом?
Очень нужно, заранее спасибо.
_________________________________________________
_________________________________________________

вычисляет количество рабочих дней между датой начала @S и датой окончания @E.

предполагается, что дата окончания (@E) не предшествует дате начала (@S). Совместимость с DATEDIFF в том же дата начала и дата окончания дает ноль рабочих дней. Игнорирует праздники.

строка цифр построена следующим образом. Создать таблицу начальные и конечные дни, строки должны начинаться с понедельника (будний день 0) и столбцы должны начните с понедельника. Заполнить диагональ сверху слева вниз справа со всеми 0 (т. е. есть 0 рабочие дни между понедельником и понедельником, вторником и вторником и т. д.). Для каждого дня начните по диагонали (всегда должно быть 0) и заполните колонны справа, день за днем. Если вы приземляетесь на столбец день выходных (нерабочий день), количество рабочих дней не меняется, его несут слева. В противном случае число рабочих дней увеличивается на один. Когда ты достигнешь конца . этот цикл строк возвращается к началу той же строки и продолжается до тех пор, пока вы снова диагональ. Затем переходите к следующему ряду.

например. Если предположить, что суббота и воскресенье-не рабочие дни . —5—>

затем объедините 49 значений в таблице в строку.

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

-редактировать улучшенная таблица:

улучшена строка: ‘0123444401233334012222340111123400001234000123440’

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

вам, конечно, нужно заполнить его всеми датами за любой период времени, с которым вы можете работать в своем приложении. Поскольку в году всего 365 (или 366) дней, переход с 1900 на 2100 не имеет большого значения. Просто убедитесь, что вы загружаете его со всеми датами, а не только праздники.

в этот момент запросы, как тот, который вам нужно стать тривиально:

предостережение: я работаю в основном с MS SQL и не работал с MySQL в течение длительного времени, поэтому вам может потребоваться настроить выше. Например, я даже не помню, имеет ли MySQL битный тип данных.

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

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

основное различие между этим и решением Роджера заключается в том, что матрица и результирующая строка цифр построены сложным алгоритмом, который я не включил. Выход этого алгоритма проверяется модульным тестом (см. входы и выходы теста ниже). В матрице пересечение любой заданной пары значений x и y (WEEKDAY(@S) и WEEKDAY(@E)) дает разницу в рабочих днях между двумя значениями. Порядок назначения на самом деле неважно, как эти два сложены вместе, чтобы построить позицию.

рабочие дни понедельник-пятница

49 значений в таблице объединены в следующую строку:

В конце концов, правильное выражение:

Я проверил следующие входы и выходы, используя это решение:

просто для дальнейшего использования. Ничто из вышеперечисленного не работало для меня, но модифицированная версия @Jeff Kooser:

excel-2007В прошлой статье ("Как вычислить количество дней между датами") я рассказала о том, как вычислить количество дней между двумя датами. При этом считались все дни – и рабочие, и выходные, и праздники. Но Excel дает возможность высчитывать не только все дни, но и просто рабочие дни.

В этом случае нам потребуется функция ЧИСТРАБДНИ. Строится эта функция так: ЧИСТРАБДНИ(начальная дата; конечная дата; праздники)

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

Шаг 1.

Запишите начальную дату и конечную дату в таблицу.

Электронная таблица

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

Электронная таблица

Шаг 2.

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

Выделить ячейку

Шаг 3.

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

Откройте окно Мастера функций. Если вы не знаете, как это сделать, то прочитайте вот эту статью ("Логические функции. Функция ЕСЛИ")

В списке «Категория» выберите категорию «Дата и время». В списке функций этой категории выберите ЧИСТРАБДНИ. Нажмите ОК.

Окно "Мастер функций"

Шаг 4.

Появится следующий шаг: окно «Аргументы функции».

В поле «Нач_дата» укажите ячейку с датой начала работы. В поле «Кон_дата» укажите ячейку с датой окончания работы. В поле «Праздники» укажите тот диапазон ячеек, в который вы записали праздничные дни. Нажмите ОК.

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