расчет отработанного времени

[Romanka]

Помогите написать формулу в Excel
Нобходимо высчитать временя в часах и минутах с момента поступления завки до его отрабоки, время работы с понедельника по пятницу с 9-до 18 (т.е. если заявка поступила в субботу, то считать время с понедельника, если после 18:00 то считать что заявка поступила на следующий день в 9:00.

[pashulka]

Romanka, Посмотрите этот пример, возможно он окажется полезен ...

[Avsha]

Вот такой вариант подготовил,
тщательно не тестировал, но вроде работает...

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

Код: Выделить всё

Public Function Len_DateTime(dt1 As Date, dt2 As Date, Min0_Hour1_Day2 As Integer)
Application.Volatile True

Dim i As Long
Dim S As Double
Dim Report_Str As String
Dim StartWorkDay As Date, EndWorkDay As Date
    StartWorkDay = "09:00:00"
    EndWorkDay = "18:00:00"

'Проверка на достоверность исходных данных
'-------------------------------------------------------------
If dt1 > dt2 Then Len_DateTime = 0: Exit Function


'Приведение начала и конца диапазона к границам рабочего дня
'-------------------------------------------------------------
If TimeValue(dt1) < StartWorkDay Then
                                    dt1 = DateValue(dt1) + TimeValue(StartWorkDay)
                                 Else
If TimeValue(dt1) > EndWorkDay Then dt1 = DateValue(dt1) + TimeValue(EndWorkDay)
End If


If TimeValue(dt2) < StartWorkDay Then
                                    dt2 = DateValue(dt2) + TimeValue(StartWorkDay)
                                 Else
If TimeValue(dt2) > EndWorkDay Then dt2 = DateValue(dt2) + TimeValue(EndWorkDay)
End If


'Начало и конец временного диапазона относятся к одному дню
'-------------------------------------------------------------
If Int(dt1) = Int(dt2) Then

If Weekday(dt1, vbMonday) <> 6 And Weekday(dt1, vbMonday) <> 7 Then
    S = S + (TimeValue(dt2) - TimeValue(dt1))
    'в отчет
    Report_Str = Report_Str & DateValue(dt1) & " / " _
                    & Weekday(dt1, vbMonday) & " / " _
                    & Format(TimeValue(dt2) - TimeValue(dt1), "hh:mm:ss") _
                    & " " & Chr(13)
End If
        GoTo End_work
End If


'Начало и конец временного диапазона относятся к разным дням
'-------------------------------------------------------------
' Обработка дня - начала временного диапазона
If Weekday(dt1, vbMonday) <> 6 And Weekday(dt1, vbMonday) <> 7 Then
    S = S + (EndWorkDay - TimeValue(dt1))
    'в отчет
    Report_Str = Report_Str & DateValue(dt1) & " / " _
                    & Weekday(dt1, vbMonday) & " / " _
                    & Format(EndWorkDay - TimeValue(dt1), "hh:mm:ss") _
                    & " " & Chr(13)
End If

' Обработка дней - между началом и концом временного диапазона
For i = Int(CDbl(dt1)) + 1 To Int(CDbl(dt2)) - 1

If Weekday(CDate(i), vbMonday) <> 6 And _
   Weekday(CDate(i), vbMonday) <> 7 Then
      S = S + (EndWorkDay - StartWorkDay)
      'в отчет
      Report_Str = Report_Str & CDate(i) & " / " _
                    & Weekday(CDate(i), vbMonday) & " / " _
                    & Format(EndWorkDay - StartWorkDay, "hh:mm:ss") _
                    & " " & Chr(13)
End If
Next i

' Обработка дня - конца временного диапазона
If Weekday(dt2, vbMonday) <> 6 And Weekday(dt2, vbMonday) <> 7 Then
    S = S + (TimeValue(dt2) - StartWorkDay)
    'в отчет
    Report_Str = Report_Str & DateValue(dt2) & " / " _
                    & Weekday(dt2, vbMonday) & " / " _
                    & Format(TimeValue(dt2) - StartWorkDay, "hh:mm:ss") _
                    & " " & Chr(13)
End If



End_work:
    
    Select Case Min0_Hour1_Day2
    Case 0
        Len_DateTime = S * 24 * 60
    Case 1
        Len_DateTime = S * 24
    Case 2
        Len_DateTime = S
    Case Else
        Len_DateTime = Report_Str
    End Select
    
    'вывод отчета
    'MsgBox Report_Str
    
End Function

[pashulka]

судя по алгоритму на формулах будет сложновато реализовать

Нет, поставленную задачу можно реализовать с помощью весьма простых и банальных формул (см. мой предыдущий пост) Да, и лично мне сложно поверить в то, что разница между 15.01.07 09:00:00 и 16.01.07 09:00:00 должна составлять не один день, а 0.38

[Avsha]

Да, и лично мне сложно поверить в то, что разница между 15.01.07 09:00:00 и 16.01.07 09:00:00 должна составлять не один день, а 0.38

Может конечно я не правильно понял задачу, но время между началом и концом я считал именно то, что входит в рабочее время, т.е. 9/24 = 0,375, а не абсолютное.

[Romanka]

Спасибо!!! Супер! Все получилось!!

[pashulka]

Может конечно я не правильно понял задачу, но время между началом и концом я считал именно то, что входит в рабочее время, т.е. 9/24 = 0,375, а не абсолютное.

Насколько мне известно, рабочий день насчитывает восемь часов, а девять это уже нарушение законодательства РФ, со всеми вытекающими событиями. А если серьёзно, то подобный подход, который будет применён не в отношении расчёта заработной платы, а при расчёте времени обработки заявок, может привести к ситуации, при которой, заказав некий товар с обязательным условием его доставки, например, в течении трёх дней, я могу получить этот товар через семь дней. Причём, фирма-поставщик будет утверждать, что это законно, т.к. у них используется именно такая система рассчётов. И честно говоря, меня, подобная вакханалия, совсем не обрадует ...

[Avsha]

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

Типа - "сколько рабочего времени уже прошло, а ты такой-сякой ничего до сих пор еще не сделал!"
Можно выкидывать еще аларменное сообщение, когда это расчетное время превышает определенное критическое значение

[pashulka]

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

[Avsha]

для полного архива решения задачки...