Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Расчет конечной даты и времени исполнения задачи, Расчет конечной даты и времени исполнения задачи с учетом длительности работ, рабочего времени и выходных дней
 
#1
19.05.2015 11:58:00
Добрый день! Очень нужна помощь!

Описание проблемы: есть 4 вида работ разной длительности.
1 вид - 1 час
2 вид - 8 часов
3 вид - 3 часа
4 вид - 24 часа
Рабочее время специалиста - с 8:30 до 17:30

Пример, специалист принял задачу в пятницу 22.05.15  в 15:30. Данная задача 2-го типа - 8 часов на исполнение. Т.о. целевая (конечная) дата исполнения задачи рассчитывается следующим образом: до конца рабочего дня в пятницу осталось 2 часа. Суббота и воскресение - выходные. Оставшиеся 6 часов на выполнение задачи переходят на понедельник  8:30 + 6 часов = 14:30. Конечный срок - 25.05.15 14:30

Прилагаю файл. Excel 2013. Сохраняю 97-2003
Буду бесконечно благодарна за помощь!!! Задача срочная, сейчас специалисты рассчитывают время на пальцах - очень много ошибок.

Пример 1 - расчет уже с учетом типа работы (тип - длительность). Пример 2 - расчет, где необходимо самостоятельно подставить длительность работы.
Удобнее, как мне кажется, Пример 1. Но буду благодарна за любую помощь в корректировке и прописании формул. В дальнейшем данный файл будет загружаться в Google Таблицы.
 
 
 
#2
20.05.2015 15:00:47
Пытаюсь разобраться с Вашими примерами.
Объясните как Вы получили значения в желтых ячейках?
По объекту Тип 1 работы начнутся 25-го в 08:00 и, по логике, должны закончиться 25-го в 09:00, но не в 09:30 (продолжительность работ - 1 час)
По объекту Тип 2 как Вы вообще на 2-е июня вышли?
Количество рабочего времени специалиста в день - 9 часов? ( 08:30 + 9 = 17:30)
Изменено: Sanja - 20.05.2015 17:33:18
Согласие есть продукт при полном непротивлении сторон
 
 
 
#3
20.05.2015 18:11:02
Да, рабочее время 9 часов, 8 - рабочих + 1 час обеденного перерыва. НО мы обеденный перерыв не учитываем в данном подсчете.


Ячейка E3:

Начало работ 22.05.15 г. в 18:00 - это уже вне рабочего дня специалиста (рабочий день до 17:30). Соответственно, к работе специалист приступит утром следующего рабочего дня т.е. в 8:30. На исполнение - один час. Т.о. целевая дата = следующий рабочий день  25.05.15. А целевое время = 8:30 +1 час =9:30.

Ячейка Е4:

Здесь, видимо, закралась ошибка. Прошу прощения.
Нужное значение: 25.05.15 17:30
 
 
 
#4
20.05.2015 18:20:05
Цитата
MarinaHR написал: А целевое время = 8:30
Понятно. Просто в файле, в ячейке B8 стоит время 08:00. Я от него отталкивался.
Дописываю UDF (пользовательская функция), скоро выложу.
Согласие есть продукт при полном непротивлении сторон
 
 
 
#5
20.05.2015 18:27:51
Вторая моя ошибка, не усмотрела... :(
Спасибо огромное!!!
 
 
 
#6
20.05.2015 18:31:21
Цитата
Sanja написал: Дописываю UDF (пользовательская функция), скоро выложу.
А смысл?
Цитата
MarinaHR написал: В дальнейшем данный файл будет загружаться в Google Таблицы.
Там работать не будет.
 
 
 
#7
20.05.2015 19:43:38
Цитата
MarinaHR написал: В дальнейшем данный файл будет загружаться в Google Таблицы.
а это я не дочитал. Но раз уж сделано...
Пример использования и описание аргументов в прикрепленном файле. Погоняйте на реальных данных
Код ниже
Код
Function ОК_РАБОТ(начало_работ As Date, тип_работ As Integer, Optional начало_раб_дня As Date = 0.3541666, Optional конец_раб_дня As Date = 0.7291666)
vrnrv = Round(начало_раб_дня, 7)
vrorv = Round(конец_раб_дня, 7)
prod = Round(vrorv - vrnrv, 7)
nr = Round(начало_работ, 7)
    
    'продолжительность работ в соответствии с типом
    Select Case тип_работ
        Case 1: dur = Round(1 / 24, 7)
        Case 2: dur = Round(8 / 24, 7)
        Case 3: dur = Round(3 / 24, 7)
        Case 4: dur = 1
    End Select
    'подгоняем время начала работ к рабочему времени
    If nr - Fix(nr) < vrnrv Then nr = Fix(nr) + vrnrv
    If nr - Fix(nr) >= vrorv Then nr = Fix(nr) + 1 + vrnrv
        
    'подгоняем дату начала работ к рабочему дню
    If Weekday(nr, vbMonday) = 6 Then nr = nr + 2
    If Weekday(nr, vbMonday) = 7 Then nr = nr + 1
    
    'Вычисляем фактическую дату и время окончания работ
    orb = nr + Fix(dur / prod) + (dur - Fix(dur / prod) * prod)
    
    'передвигаем время окончания в рабочее время
    If Round(orb - Fix(orb), 7) > vrorv Then orb = Fix(orb) + 1 + vrnrv + Round(orb - Fix(orb), 7) - vrorv
    If Round(orb - Fix(orb), 7) < vrnrv Then orb = Fix(orb) + vrnrv + Round(orb - Fix(orb), 7)
    
    'передвигаем дату окончания на рабочий день
    If Weekday(orb, vbMonday) = 6 Then orb = orb + 2
    If Weekday(orb, vbMonday) = 7 Then orb = orb + 1
    
    ОК_РАБОТ = orb
        
End Function
Согласие есть продукт при полном непротивлении сторон
 
 
 
#8
21.05.2015 10:57:25
Спасибо!!!
Я готова отказаться от размещения на Google ради одного корректно  работающего инструмента!!!

Сейчас протестирую!
 
 
 
#9
21.05.2015 12:01:30
Подсчет времени и даты по 1,2 и 3 типу работ (т.е. по длительности равной 1 часу, 8 часам и 3 часам) работает все корректно.
Подсчет по 4 типу = длительности 24 часа - дает сбой.

Проверяли по дате 21.05.15 (четверг) в 11:40 поступила заявка. На работу 24 часа. Подсчет следующий:
21.05. до конца дня осталось еще 5 часов 50 минут
22.05 в пятницу - 9 часов.
23.05. и 24.05 - не рабочии дни.
25.05. - 9 часов
Итоговое время завершения 26.05.15 в 8:40


В таблице - 25.05.15 8:40 т.к. в подсчете потерялся один день.

Помогите, пожалуйста!
 
 
 
#10
21.05.2015 15:28:29
Цитата
'передвигаем дату окончания на рабочий день    
If Weekday(orb, vbMonday) = 6 Then orb = orb + 2    
If Weekday(orb, vbMonday) = 7 Then orb = orb + 1
На мой взгляд в обоих случаях надо добавлять два дня
 
 
 
#11
21.05.2015 17:16:25
Цитата
Kuzmich написал: в обоих случаях надо добавлять два дня
Точно! И в подгонке даты начала работ проверка на воскресенье тоже лишняя. Поправил, попробуйте.
Код
Function ОК_РАБОТ(начало_работ As Date, тип_работ As Integer, Optional начало_раб_дня As Date = 0.3541666, Optional конец_раб_дня As Date = 0.7291666)
vrnrv = начало_раб_дня
vrorv = конец_раб_дня
prod = vrorv - vrnrv
nr = начало_работ
    
    'продолжительность работ в соответствии с типом
    Select Case тип_работ
        Case 1: dur = 1 / 24
        Case 2: dur = 8 / 24
        Case 3: dur = 3 / 24
        Case 4: dur = 1
    End Select
    
    'подгоняем время начала работ к рабочему времени
    If nr - Fix(nr) < vrnrv Then nr = Fix(nr) + vrnrv
    If nr - Fix(nr) >= vrorv Then nr = Fix(nr) + 1 + vrnrv
        
    'подгоняем дату начала работ к рабочему дню
    If Weekday(nr, vbMonday) = 6 Then nr = nr + 2
    
    'Вычисляем фактическую дату и время окончания работ
    orb = nr + Fix(dur / prod) + (dur - Fix(dur / prod) * prod)
    
    'передвигаем время окончания в рабочее время
    If (orb - Fix(orb)) > vrorv Then orb = Fix(orb) + 1 + vrnrv + (orb - Fix(orb)) - vrorv
    If (orb - Fix(orb)) < vrnrv Then orb = Fix(orb) + vrnrv + (orb - Fix(orb))
    
    'передвигаем дату окончания на рабочий день
    If Weekday(orb, vbMonday) = 6 Or Weekday(orb, vbMonday) = 7 Then orb = orb + 2
    
    ОК_РАБОТ = orb
        
End Function
Согласие есть продукт при полном непротивлении сторон
 
 
 
#12
21.05.2015 17:44:51
У меня не получается изменить аргумент...
Есть возможность прикрепить файл?
 
 
 
#13
21.05.2015 18:09:33
Про какой аргумент речь?
Согласие есть продукт при полном непротивлении сторон
 
 
 
#14
21.05.2015 18:18:54
Огромное спасибо за файл!!!
Вы даже не представляете на сколько упростили жизнь и осчастливили людей!!! :)
 
 
 
#15
21.05.2015 18:24:51
Все-таки некоторое время выборочно проверяйте, может какие нюансы всплывут. Там, например, праздничные дни не учитываются
Согласие есть продукт при полном непротивлении сторон
 
 
 
#16
21.05.2015 18:54:05
Цитата
праздничные дни не учитываются
И еще в пятницу укороченный на 1 час рабочий день
 
 
 
#17
21.05.2015 19:25:55
Цитата
Kuzmich написал: укороченный на 1 час рабочий день
Ну это вряд-ли :D, там даже
Цитата
MarinaHR написал: мы обеденный перерыв не учитываем
Согласие есть продукт при полном непротивлении сторон
 
 
 
#18
21.05.2015 19:29:07
КЗОТ нарушают
 
 
 
#19
21.05.2015 22:29:23
Немного модернизировал файл, относительно своего понимания удобства использования с точки зрения пользователя и с точки зрения понимания, написания формул.
Но суть в том, что используется функция РАБДЕНЬ.МЕЖД, что позволяет гибко управлять выходными и праздничными днями, без использования макросов.

Разбил на отдельные ячейки дату и время начала работ, чтобы пользователю было проще вводить данные.
Точно также разбил дату и время окончания, если не надо разбивать, то формула будет выглядеть так:
Цитата
=РАБДЕНЬ.МЕЖД(D16;ЕСЛИ(E16+C16>КонРабДня;ЧАСТНОЕ(C16-(КонРабДня-E16);КонРабДня-НачРабДня)+1;ЧАСТНОЕ(C16-(КонРабДня-E16);КонРабДня-НачРабДня));1)+НачРабДня+ОСТАТ(C16-(КонРабДня-E16);КонРабДня-НачРабДня)
 
 
 
#20
22.05.2015 13:08:31
Спасибо за помощь!

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

Цитата
Kuzmich написал: КЗОТ нарушают
- КЗОТ регулирует трудовые отношения, когда специалисты работают в штате по "трудовому договору". В случае, если специалисты работают по "гражданско-правовому договору" - это регулируется другими нормами (в том числе, влияющими на учет сокращенных рабочих дней, праздничных дней и обеденных перерывов).
 
 
 
#21
22.05.2015 13:13:17
В таблице "Пример 1" не работает учет выходных дней :(

Но сама форма очень удобная!  Выпадающие списки - всегда понятнее в использовании.
 
 
 
#22
22.05.2015 21:28:16
MarinaHR, в смысле не работает? У меня все работает. Хотя я делал на Excel 2013.

Если у вас Excel 2007 попробуйте заменить на
Цитата
=РАБДЕНЬ(D16;ЕСЛИ(E16+C16>КонРабДня;ЧАСТНОЕ(C16-(КонРабДня-E16);КонРабДня-НачРабДня)+1;ЧАСТНОЕ(C16-(КонРабДня-E16);КонРабДня-НачРабДня)))
РАБДЕНЬ - тут по умолчанию выходные суббота, воскресенье.
Специально поменял формат ячеек, чтобы было видно день недели. Корректно переносит.

РаботникТИП ОБЪЕКТАДлительностьДата НачалоВремя НачалоДата КонецВремя Конец
Сотрудник1Тип   424:0021.05.2015 Чт16:0026.05.2015 Вт13:00
Сотрудник2Тип   28:0021.05.2015 Чт8:3021.05.2015 Чт16:30
Сотрудник3Тип   28:0022.05.2015 Пт13:2525.05.2015 Пн12:25

ps Проверил в ГуглДокс - тоже работает
Изменено: StepanWolkoff - 22.05.2015 22:05:04
 
 
 
Страницы: 1
Читают тему
Наверх