Формула для расчета остатка на конец квартала либо любого дн

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Формула для расчета остатка на конец квартала либо любого дн (Формулы/Formulas)

annetfd

Дата: Среда, 10.10.2018, 12:04 | Сообщение № 1

Группа: Пользователи

Ранг: Прохожий

Сообщений: 2

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

Доброго дня!
Прошу помочь в решении проблемы, попробую объяснить.
Табличку прикрепила и там еще в примечании расчет.

Суть таблицы в том, что она отражает учет отпускных дней сотрудников.
Второй столбец содержит период рабочего года сотрудника, за который предусмотрено 28 дней (столбец 3). У каждого сотрудника он свой. И расчет должен вестись опираясь на это.
в 4 столбце просто отмечается количество дней, которые сотрудник использует на протяжении года, он по сути не нужен для расчета вообще.
а в 5 столбце уже сумма этих дней. Эту сумму нужно учитывать при расчете остатка на конкретную дату.
6 столбец - остаток дней за весь период, то есть до окончания рабочего года сотрудника ( в первом случае до 20.07.19)

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

еще загвоздка в том, что месяц полный считается если прошло 15+ дней.
например, принят сотрудник 2 марта, а считаем на 30 сентября:
2 апреля - месяц, 2 мая - 2 месяца, 2 июня - 3 месяца, 2 июля - 4 месяца, 2 августа - 5 месяцев, 2 сентября - 6 месяцев и до 30.09 с 02.09 прошло больше 15 дней, значит это еще +1 месяц.

итого считаем 6 полных месяцев + 1 мес за полные 15 дней.
1 мес = 2,33 дня отпуска
следовательно = 7 мес * 2,33 = 16,31 дн => округляем до 16

8 дней уже отгуляно ( столбец 5) значит должно получится 16-8 = 8

К сообщению приложен файл: 0559009.xlsx (13.4 Kb)

Ответить

СообщениеДоброго дня!
Прошу помочь в решении проблемы, попробую объяснить.
Табличку прикрепила и там еще в примечании расчет.

Суть таблицы в том, что она отражает учет отпускных дней сотрудников.
Второй столбец содержит период рабочего года сотрудника, за который предусмотрено 28 дней (столбец 3). У каждого сотрудника он свой. И расчет должен вестись опираясь на это.
в 4 столбце просто отмечается количество дней, которые сотрудник использует на протяжении года, он по сути не нужен для расчета вообще.
а в 5 столбце уже сумма этих дней. Эту сумму нужно учитывать при расчете остатка на конкретную дату.
6 столбец - остаток дней за весь период, то есть до окончания рабочего года сотрудника ( в первом случае до 20.07.19)

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

еще загвоздка в том, что месяц полный считается если прошло 15+ дней.
например, принят сотрудник 2 марта, а считаем на 30 сентября:
2 апреля - месяц, 2 мая - 2 месяца, 2 июня - 3 месяца, 2 июля - 4 месяца, 2 августа - 5 месяцев, 2 сентября - 6 месяцев и до 30.09 с 02.09 прошло больше 15 дней, значит это еще +1 месяц.

итого считаем 6 полных месяцев + 1 мес за полные 15 дней.
1 мес = 2,33 дня отпуска
следовательно = 7 мес * 2,33 = 16,31 дн => округляем до 16

8 дней уже отгуляно ( столбец 5) значит должно получится 16-8 = 8

Автор - annetfd
Дата добавления - 10.10.2018 в 12:04

sboy

Дата: Среда, 10.10.2018, 12:35 | Сообщение № 2

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2566

Репутация: 724 ±

Замечаний: 0% ±

Excel 2010

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

Код

=ОКРУГЛ((РАЗНДАТ(--ЛЕВБ(B4;8);--ПРАВБ($G$1;10);"m")+(РАЗНДАТ(--ЛЕВБ(B4;8);--ПРАВБ($G$1;10);"md")>14))*2,33;)-E4