Добавить дни
Добавить рабочие дни до даты
= РАБДЕНЬ (начало_даты; дни; праздники)
Если вам нужно добавить или вычесть рабочие дни (рабочие дни) для даты, чтобы можно было рассчитать дату в будущем или прошлом, которая пропускает выходные дни (и праздники), вы можете использовать функцию РАБДЕНЬ. Праздники не являются обязательными.
В этом примере формула в D5:
=РАБДЕНЬ(B5;C5;$B$8:$B$10)
Это добавляет 7 дней во вторник, 22 декабря и возвращает вторник, 5 января 2016 года. По умолчанию функция РАБДЕНЬ исключает выходные дни (суббота и воскресенье). В этом случае мы также предоставили список из трех праздничных дней, все из которых относятся к расчетному диапазону дат, что означает, что выходные и праздничные дни будут исключены.
Чтобы вычесть выходные из даты (вместо добавления рабочих дней), просто используйте отрицательное значение для дней. Например, чтобы получить дату 3 рабочих дня до даты в A1, вы можете использовать:
= РАБДЕНЬ (A1; -3)
Добавить рабочие дни без выходных
= РАБДЕНЬ.МЕЖД (дата начала; дни; «0000000»; праздничные дни)
Чтобы добавить или вычесть дни рабочих дней к дате, соблюдая праздники, но при условии 7-дневной рабочей недели, вы можете использовать функцию РАБДЕНЬ.МЕЖД. В этом примере формула в D6:
=РАБДЕНЬ.МЕЖД(B5;C5;»0000000″;B8:B10)
Эта формула добавляет 7 дней работы во вторник, 22 декабря. Три отпуска предоставляются с использованием названного диапазона «праздники» (B9: B11), а выходные устанавливаются с использованием специального синтаксиса «0000000», что означает, что все дни недели являются рабочими днями. Результат: Вт, 31 декабря 2015 года.
РАБДЕНЬ.МЕЖД может вычислять дату в будущем или прошлом, что касается праздников и выходных. Чтобы указать, какие дни считаются выходными, вы можете использовать специальный код (полный список кодов здесь) или использовать «маску» для обозначения выходных дней с единицами и нулями. Метод маски является более гибким, поскольку он позволяет назначать любой день недели в выходные (т. е. Нерабочий день). Например:
= РАБДЕНЬ.МЕЖД (A1;3; «0000000») // без выходных
= РАБДЕНЬ.МЕЖД (A1;3; «1000000») // выходной = Пн
= РАБДЕНЬ.МЕЖД (A1;3; «1100000») // выходные= понедельник + вторник
= РАБДЕНЬ.МЕЖД (A1;3; «1110000») // выходные = Пн + Вт + Ср
Аргумент выходных дней предоставляется в виде 7 символов, представляющих понедельник-воскресенье. Используйте один (1), чтобы указать выходные, и ноль (0), чтобы указать рабочий день.
Поскольку мы хотим, чтобы все дни недели считались рабочими днями, мы используем «0000000».
Добавить дни и исключить определенные дни недели
= РАБДЕНЬ.МЕЖД (дата; дни; «образец»)
Чтобы добавить дни в дату, исключая определенные дни (исключая вторники и четверги, только среду и т. д.), вы можете использовать функцию РАБДЕНЬ.МЕЖД со специальным кодом шаблона.
=РАБДЕНЬ.МЕЖД(B6;7;»0000011″)
Эта формула добавляет 7 дней к дате в B7, за исключением суббот и воскресений.
Функция РАБДЕНЬ.МЕЖД основана на функции РАБДЕНЬ, которая предназначена для добавления рабочих дней к дате. РАБДЕНЬ автоматически исключает субботу и воскресенье, а также может исключать список пользовательских праздников. РАБДЕНЬ.МЕЖД делает то же самое, но позволяет исключить любые дни недели, в дополнение к праздникам.
Чтобы исключить определенные дни недели, вы можете использовать предварительно сконфигурированный код или предоставить свой собственный код шаблона. Код шаблона должен состоять из 7 цифр, либо иметь ноль для каждого дня недели, начинающийся в понедельник и заканчивающийся в воскресенье. Значения, равные 1, исключаются, а дни с нулевыми значениями обрабатываются обычным образом.
Итак, если вы хотите добавить 7 дней к дате в ячейке A1, вы можете написать такие формулы:
= РАБДЕНЬ.МЕЖД (A1;7; «0000011») // исключить Сб; Вс
= РАБДЕНЬ.МЕЖД (A1;7; «0010011») // исключить Сб; Вс; Ср.
= РАБДЕНЬ.МЕЖД (A1;7; «0101011») // исключаем Сб; Вс; Вт; Чт
Добавить дни до даты
= Дата + дни
Чтобы добавить заданное количество лет к дате, вы можете использовать функцию ДАТАМЕС.
= B5 + C5
Даты в Excel — это только серийные номера. Число 1 представляет 1 января 1900 года, число 1000 — 26 сентября 1902 года и так далее.
Когда у вас есть действительная дата в Excel, вы просто добавляете дни непосредственно. Значения дня могут быть как положительными, так и отрицательными.
Например, с датой в A1 вы можете добавить 100 дней следующим образом:
= A1 + 100
В показанном примере формула решается следующим образом:
= B5 + C5
= 36861 + 7
= 36868
Когда отформатировано как дата, 36868 — 8 декабря 2000 года.
Добавить рабочие дни на персонализированные выходные дни
= РАБДЕНЬ.МЕЖД (начало_даты; дни; выходные; праздники)
Чтобы добавить или вычесть дни рабочих дней для даты, чтобы можно было рассчитать дату в будущем или прошлом, которая пропускает выходные дни (настраиваемые) и праздники, можно использовать функцию РАБДЕНЬ.МЕЖД. Праздники и выходные дни являются необязательными.
В этом примере формула в D5:
=РАБДЕНЬ.МЕЖД(B5;C5;11;B8:B10)
Эта формула добавляет 7 дней работы во вторник, 22 декабря. Три отпуска предоставляются, а выходные устанавливаются с использованием 11 аргументов в выходные, что означает «только воскресенье».
По умолчанию функция РАБДЕНЬ.МЕЖД исключает выходные дни (суббота и воскресенье). В этом случае, однако, мы поставили 11 для этого аргумента, что означает «только воскресенье».
Мы также предоставили список из 3 праздников, которые все выпадают в диапазоне дат, который рассчитывается, что означает, что праздники и воскресенья будут исключены.
Добавить время
= Время + (часы / 24)
Чтобы добавить определенное количество часов за раз, вы можете добавить часы, деленные на 24, или использовать функцию ВРЕМЯ.
В показанном примере формула в D5:
= B5 + (C5 / 24)
Примечание. Убедитесь, что результаты форматируются как время.
Время в Excel составляет 24 часа. Один час времени равен 1/24, а 1 минута времени равна 1 / (24 * 60) = 1/1440.
В результате, если у вас есть десятичное значение в течение 6 часов и время в A1, вы можете добавить 6 часов времени к значению в A1 следующим образом:
= A1 + (6/24)
Вы также можете добавить временные значения с помощью функции ВРЕМЯ. Чтобы добавить 15 часов к времени в A1, используйте:
= A1 + ВРЕМЯ (6;0;0)
Функция ВРЕМЯ избавляет вас от необходимости запоминать формулу для преобразования десятичных часов в Excel. Однако обратите внимание на то, что функция ВРЕМЯ будет возвращаться к нулю, когда значения превысят 24 часа.
Например, обратите внимание, как два подхода возвращают разные результаты в течение 25 часов:
= ВРЕМЯ(25;0;0) = 0;041667 = 1:00 в тот же день (1 час)
= 25/24 = 1.041667 = 1:00 на следующий день (25 часов)
Функция ВРЕМЯ возвращает эквивалент 1 часа, а 25/24 возвращает полное значение.
Вы можете получить ошибку, если попытаетесь вычесть часы из времени, когда результат будет отрицательным, поскольку Excel не допускает отрицательных значений времени.
Один из способов избежать этой проблемы — использовать формулу, подобную этой:
= ОСТАТ (время — (часы / 24); 1)
Здесь функция ОСТАТ заботится о негативной проблеме, используя функцию ОСТАТ для «переворачивания» отрицательных значений в требуемое положительное значение.
Другой способ избежать этой проблемы — начать с времени, которое включает значение даты. Это позволяет вам вычитать очень большое количество часов без какой-либо опасности получить отрицательный результат. Если вы не хотите видеть дату, отображаемую в результате, просто применяйте формат чисел только для времени.
Добавить минуты к часу
= Время + (минуты / 1440)
Чтобы добавить определенное количество минут за раз, вы можете добавить минуты, деленные на 1440, или использовать функцию ВРЕМЯ.
В показанном примере формула в D5:
= B5 + (C5 / 1440)
Примечание. Убедитесь, что результаты форматируются как время.
Время в Excel составляет 24 часа. Один час времени равен 1/24, а 1 минута времени равна 1 / (24 * 60) = 1/1440.
В результате, если у вас есть десятичное значение в течение 15 минут и время в A1, вы можете добавить 15 минут времени к значению в A1 следующим образом:
= A1 + (15/1440)
Вы также можете добавить временные значения с помощью функции ВРЕМЯ. Чтобы добавить 15 минут к времени в A1, используйте:
= A1 + ВРЕМЯ (0;15;0)
Функция ВРЕМЯ избавляет вас от необходимости запоминать формулу для преобразования десятичных минут в Excel. Однако обратите внимание на то, что функция ВРЕМЯ будет возвращаться к нулю, когда значения превысят 24 часа.
Например, 25 часов времени составляет 1500 минут. Обратите внимание на отличия:
= ВРЕМЯ (0;1500;0) = 0;041667 = 1:00 в тот же день
= 1500/1440 = 1.041667 = 1:00 на следующий день
Вы можете получить ошибку, если попытаетесь вычесть минуты из времени, когда результат будет отрицательным, поскольку Excel не допускает отрицательных значений времени.
Один из способов избежания данной проблемы — использовать формулу, подобную этой:
= ОСТАТ(время — (минуты / 1440); 1)
Здесь функция ОСТАТ заботится о негативной проблеме, используя функцию ОСТАТ для «переворачивания» отрицательных значений в требуемое положительное значение.
Другой способ избежания этой проблемы — начать со времени, которое включает значение даты. Это позволит вам вычитать очень большое количество минут без какой-либо опасности получить отрицательный результат. Если вы не хотите видеть дату, отображаемую в результате, просто применяйте формат чисел только для времени.
Добавить месяцы до даты
= ДАТАМЕС (дата; месяцы)
Чтобы добавить заданное количество лет к дате, вы можете использовать функцию ДАТАМЕС.
В показанном примере формула в D5:
= ДАТАМЕС(B5; C5)
Функция ДАТАМЕС полностью автоматическая. Просто введите действительную дату и количество месяцев, а ДАТАМЕС вернет новую дату. Чтобы вычесть месяцы из даты, поставьте отрицательное значение.
Чтобы двигаться вперед и назад в годах с определенной даты, вы можете умножить на 12 внутри ДАТАМЕС следующим образом:
= ДАТАМЕС (A1;12 * 10) // 10 лет
=ДАТАМЕС (A1;12 * 50) // 50 лет
Добавить года
= ДАТА(ГОД (дата) + годы; МЕСЯЦ(дата); ДЕНЬ(дата))
Чтобы добавить определенное количество лет к дате, вы можете использовать формулу, основанную на функции ДАТА, с помощью функций ГОД, МЕСЯЦ и ДЕНЬ.
В показанном примере формула в D5:
= ДАТА(ГОД (B5) + C5; МЕСЯЦ (B5); ДЕНЬ (B5))
Работая изнутри, функции ГОД, МЕСЯЦ и ДЕНЬ извлекают соответствующие компоненты даты:
= ГОД(B5) // 1960
= МЕСЯЦ (B5) // 3
= ДЕНЬ(B5) // 8
На внешнем уровне функция ДАТА просто пересобирает значения компонентов обратно в действительную дату Excel. Чтобы добавить годы к дате, нам просто нужно добавить значение в C5 к компоненту года до повторной сборки:
= ДАТА(ГОД (B5) + C5; МЕСЯЦ (B5); ДЕНЬ (B5))
Формула затем решается следующим образом:
= ДАТА(1960 + 10;3;8)
= ДАТА(1970;3;8)
= 8-мар-1970
Примечание. Если вам нужно добавить «четную» кратность 12 месяцев к дате (например, 12, 24, 36, 48 и т. д.), вы можете использовать гораздо более простую формулу, основанную на функции ДАТАМЕС.
Назначать баллы на основе позднего времени
= ЕСЛИ(время <ЗНАЧЕН («0:05»); 0; ЕСЛИ(время <ЗНАЧЕН(«0:15»); 1;
ЕСЛИ(время <ЗНАЧЕН(«0:30»); 2; ЕСЛИ(время <ЗНАЧЕН(«0:60»); 3;
ЕСЛИ(время <ЗНАЧЕН(«4:00»); 4; 5)))))
Чтобы назначить штрафные очки за опоздание, вы можете использовать вложенную формулу ЕСЛИ. В показанном примере формула в E5:
= ЕСЛИ (D5 <ЗНАЧЕН («0:05»); 0; ЕСЛИ (D5 < ЗНАЧЕН («0:15»); 1;
ЕСЛИ (D5 < ЗНАЧЕН («0:30»); 2; ЕСЛИ (D5 < ЗНАЧЕН («0:60»); 3;
ЕСЛИ (D5 < ЗНАЧЕН («4:00»); 4; 5)))))
Примечание. Разделители строк добавлены для удобства чтения.
Эта формула является классическим примером вложенной формулы ЕСЛИ, которая проверяет пороговые значения в порядке возрастания. Чтобы соответствовать графику, показанному в G4: G10, формула сначала проверяет по времени в D5, чтобы увидеть, не меньше ли она составляет 5 минут. Если это так, присваиваются нулевые точки:
ЕСЛИ(D5 <ЗНАЧЕН(«0:05»); 0;
Если результат логического теста выше ЛОЖЬ, формула проверяет, меньше ли D5, чем следующий порог, который составляет 15 минут:
ЕСЛИ(D5 < ЗНАЧЕН («0:15»); 1;
Такая же картина повторяется на каждом пороге. Поскольку тесты выполняются по порядку, от самого маленького до самого большого, нет необходимости в более сложных скобках.
Функция ЗНАЧЕН используется для того, чтобы значение времени обработки Excel обрабатывалось на каждом пороге как число вместо следующего.