Auditportal.ru

Аудит Портал
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как изменить приближающийся срок / крайний срок в Excel?

Интервалы дат в Excel – функции обработки

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

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

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

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

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

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

Используя эту формулу, вы можете решить приведенный выше пример: =DATAMES(A1;5). Я думаю, вы согласитесь, что это короче и проще для понимания.

Как определить день недели в Excel

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

Используйте функцию DAYNED(Date ; Type). Имеет два аргумента и возвращает порядковый номер дня недели:

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

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

Как определить количество дней между датами в Эксель

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

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

Как посчитать долю от года в Microsoft Excel

Если вам известен период и нужно узнать, какую часть календарного года он занимает, используйте функцию DOLLARGE(Start_date; End_date; Base). Как вы видите, функция имеет 3 аргумента:

  1. Дата начала — дата начала тестового периода — обязательный аргумент
  2. Дата окончания — дата окончания периода — обязательный аргумент
  3. База — базовые значения для периода года. После ввода параметра программа отображает инструкции по выбору этого аргумента.
Читайте так же:
Как избежать ошибок округления при расчетах в Excel?

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

Как получить последний день месяца

Чтобы получить дату последнего дня месяца — используйте функцию CONMEMBLE(Date ; Number of_months) . Эта функция возвращает последний день заданной даты или заданное количество месяцев от этой даты. Он использует 2 обязательных аргумента:

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

Как узнать номер недели в году

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

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

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

Excel как прибавить месяц к дате

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

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

С помощью функции DATE три числа объединяются и создается дата. Ее аргументы следующие: (год;месяц;день) . Формула будет выделена курсивом, когда мы набираем A1:

Поэтому формат ячейки изменится, и в ней появится штамп, например, 10 июля 2017 года.

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

Вы можете указать аргументы различными способами. Если вы выберете год 117, Excel также поймет его как 2017, поскольку отсчет начинается с 1900, а 1900+117=2017. День и месяц могут быть как положительными, так и отрицательными числами. Например, если из начала июля вычесть 5 дней, то получится 25 июня.

Отнимаем дни

Другими словами, если из начала 2017 года вычесть два месяца, получится октябрь 2016 года.

Вычитаем месяцы

Кроме того, последние два аргумента не обязательно должны быть между 1 и 12 и 1 и 31. Если значения больше, то вот что получается: 35-31=4 — это день, 13-12=1 и плюс 31 день из предыдущего расчета, получается второй месяц. А 12 месяцев — это год. Так, с 35.13.2017 будет 4 февраля 2018 года.

Читайте так же:
Как использовать countif для расчета процента в Excel?

Большие значения

Вы можете использовать функцию DATE, если все данные находятся в разных ячейках и имеют общий формат. В этом случае он объединяет все числа. Ставим знак равенства, пишем функцию и выбираем по очереди сначала год ( C3 ), затем месяц ( B3 ) и день ( A3 ). Теперь в ячейке D3 указан правильный день, и ячейка правильно отформатирована.

Объединили

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

Большое значение

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

Отсчет в числах

Рассматриваемая функция также поможет вам вычислить другую дату, основанную на любой выбранной дате. Запишем следующую формулу в E3:

Добавьте 2 к году, 1 к месяцу и 5 к дню из D3. Результат отображается в E3. Используя этот метод, можно рассчитать день годовщины или окончания контракта.

Прибавление

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

Если число имеет вид: 10072017 (ДДММГГГГ), тогда формула выглядит следующим образом

Первым аргументом является год — это 4 правых символа из A7 (2017). Затем берем месяц — вычитаем два числа из A7, начиная с третьего (07). А день — это 2 цифры слева (10).

По порядку

Формула, которую нужно использовать для числа, записанного как: 20170710, что является ГГГГММДД, это:

Сначала слева удалите 4 символа из A8, затем номер месяца и справа 2 цифры, обозначающие день.

Начиная с года

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

Как в экселе посчитать дату окончания срока

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

Используйте черточки

Применение дробей

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

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

Количество секунд также может быть введено через двоеточие:

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

Быстрый ввод даты и времени

Используя клавиши Ctrl + G, можно ввести сегодняшнюю дату в текущую ячейку.

Если вы скопируете ячейку с датой (перетащив правый нижний угол ячейки), удерживая нажатой правую кнопку мыши, вы можете выбрать точный способ копирования выделенной даты:

Если вам часто приходится вводить разные даты в ячейки листа — прочтите это .

Читайте так же:
Как найти / выбрать минимальные данные / значения в диапазоне в Excel?

Если вы хотите, чтобы в ячейке всегда была сегодняшняя дата, лучше воспользоваться функцией TODAY (Вставка — меню Функции, категория Дата и время):

Как Excel хранит и обрабатывает дату и время

Если выбрать ячейку с датой и установить для нее формат Общий (меню Формат — Ячейки — Число — вкладка Общий), то можно увидеть интересную картину:

Вот как это выглядит, например, 8.10.2006 15:42

На самом деле Excel хранит и обрабатывает все даты совершенно одинаково — как число с целой и дробной частью. Целая часть этого числа (38998) — это количество дней с 1 января 1900 года (по умолчанию) до текущей даты. Дробная часть (0,65417) — это доля дня (1 день = 1,0).

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

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

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

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

Здесь ситуация намного сложнее. Субботы, воскресенья и праздники не могут быть включены. Этот расчет можно выполнить с помощью функции NUMBERBUS в дополнении Analysis Package.

Сначала необходимо подключить Add-Ins пакета Analysis Toolpak. Перейдите в Tools — Add-Ins и выберите Analisys Toolpak . После этого NETWORKDAYS появится в категории Дата и время Мастера функций.

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

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

Лучший способ рассчитать его — прочитать о том, как это сделать правильно.

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

Перенести дату на указанное количество рабочих дней

Для этого используется функция WORKDAY в дополнении Analysis Package. Эта функция позволяет рассчитать дату впереди или позади начальной даты на желаемое количество рабочих дней (включая субботы, воскресенья и праздники). Использование этой функции полностью аналогично использованию функции WORKDAY, описанной выше.

Расчет дней недели

Разве вы не родились в понедельник? Нет, вы уверены? Вы можете легко проверить это с помощью функции День недели в категории Дата и время.

Выберите ячейку с датой, затем перейдите в Формат — Ячейки, вкладка Число и Все форматы, а затем введите новый формат DDDD :

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

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

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

Читайте так же:
Как запустить макрос, когда лист выбран из книги?

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

Как определить дату через заданное количество рабочих дней

Стандартная задача: мы знаем дату начала работы и количество рабочих дней, отведенных на ее выполнение. Нам необходимо определить дату окончания работы. С помощью функции RABDEN(Дата начала ; Количество дней ; [Праздники] ) . Эта функция имеет три аргумента:

  1. Дата начала — дата, с которой мы отсчитываем срок. Это обязательный аргумент
  2. Количество дней — сколько рабочих дней нужно добавить к дате начала — обязательный аргумент
  3. Государственные праздники — список государственных праздников, если таковые имеются, которые должны быть приняты во внимание. Это необязательный аргумент. Лучше всего указать его со ссылкой на список праздничных дней.

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

Что делать, если у вас другой график выходных? У вас выходной только в воскресенье? Используйте аналогичную функцию — RABDAY. (Дата начала ; Кол-во дней ; [выходные ; праздники ) . Здесь есть еще один необязательный параметр — Праздники. Именно он позволяет установить желаемый график выходных. Если вы введете этот параметр, программа подскажет вам, что выбрать, чтобы праздники были правильно установлены:

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

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

Другая похожая задача — подсчет количества рабочих дней, прошедших между двумя датами. Как и в предыдущих двух случаях, в этом подсчете используются те же функции. Мы можем легко подсчитать, сколько рабочих дней находится в интервале дат, используя функцию CLEARRABDNY(Начальная дата ;Конечная дата ; [Праздники]). Здесь используются три аргумента:

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

Как пользоваться функцией:

По умолчанию выходными днями являются суббота и воскресенье. Выберите другие выходные дни с помощью функции ЧЕТРАБДНИ. MEAN(Начальная дата ;Конечная дата ;[Выходные] ;[Праздники] ) . В ответ на ввод аргумента Weekend программа выводит всплывающую подсказку, содержащую список параметров. Сделайте выбор и запишите его:

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

  • Функции и
  • Преобразование текста в дату.

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

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

11 комментариев

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

Читайте так же:
Как защитить форматирование рабочего листа только в Excel?

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

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

Функция CLEAN в Microsoft Excel описана в этой статье.

Описание

Возвращает количество рабочих дней между датами «начало_даты» и «конец_даты». Государственные праздники и выходные дни не включены в эту цифру. Функция ЧИСЛО может использоваться для расчета заработной платы сотрудника на основе количества дней, отработанных за указанный период.

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

Синтаксис

Аргументы функции ЧИСТРАБДНИ описаны ниже.

Начальная_дата Обязательно. Дата начала работы.

Con_date Обязательно. Дата окончания.

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

Важно: Даты должны вводиться с помощью функции DATE или в результате вычисления других формул и функций. Например, если вы хотите ввести данные за 23 мая 2012 года, используйте выражение DATE(2012,5,23). При вводе дат в тексте могут возникнуть проблемы.

Замечания

В Microsoft Excel даты хранятся в виде последовательных чисел, чтобы их можно было использовать в вычислениях. По умолчанию дата 1 января 1900 года равна 1, а 1 января 2012 года составляет 40 909 дней. — По умолчанию дата 1 января 1900 года равна 1, а дата 1 января 2012 года равна 40 909, поскольку интервал между этими двумя датами составляет 40 909 дней.

C LEARRABDNY возвращает #CALL, если любой из аргументов является недействительной датой!

Пример

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector