calculando horários de término com horário de trabalho e pulando fins de semana

Question

Na minha planilha, quero calcular os tempos estimados de término dos processos.

No entanto, quero restringir isso a uma restrição de tempo predeterminada. Por exemplo, quando adiciono 4 horas às 14h, não quero que o resultado seja 18h, mas sim 9h!

Supondo dias úteis das 8h00 às 17h00. E omitindo sábado e domingo

Alguém pode me ajudar?

Com a ajuda do Simon da rcl consegui adaptar sua solução para calcular com minutos também. No entanto, parece haver um problema. Quando eu adiciono

960 minutos para 22-05-15 16:00 a função dá um resultado correto de 26-05-15 14:00

no entanto, por mais uma hora (60 minutos) o resultado volta para 25-05-15 09:00.

Alguém vê o problema aqui?

Option Explicit

Public Function EndDayTimeM(StartTime As String, Minutes As Double)

On Error GoTo Hell
' start and end hour are fixed here.
' could put them in cells and look them up
Dim startMinute As Long, endMinute As Long, startHour As Long, endHour As Long

startMinute = 480

endMinute = 960 ' was 18

startHour = 8

endHour = 16

Dim calcEnd As Date, start As Date
start = CDate(StartTime)
calcEnd = DateAdd("n", Minutes, start)

If DatePart("h", calcEnd) > endHour Or DatePart("h", calcEnd) <= startHour Then
    ' add 15 hours to get from 17+x to 8+x
    calcEnd = DateAdd("h", 15, calcEnd)  ' corrected

End If

If DatePart("w", calcEnd) = 7 Or DatePart("w", calcEnd) = 1 Then
    ' Sat or Sun: add 2 days
    calcEnd = DateAdd("d", 2, calcEnd)
End If

If DatePart("h", calcEnd) > endHour Or DatePart("h", calcEnd) <= startHour Then
    ' add 15 hours to get from 17+x to 8+x
    calcEnd = DateAdd("h", 15, calcEnd)  ' corrected
End If

EndDayTimeM = calcEnd

Answer 1

O seguinte fará o que você deseja e é totalmente configurável, além de suportar qualquer formato de entrada ou saída, desde que o Excel ainda o entenda como uma data + hora numérica. Você pode definir qualquer início ou término para suas horas/dias de trabalho.

Public Function EndDayTimeM(StartTime As Double, Minutes As Long)
Dim rangeH, numH, rangeD, numD, startD, durW, durD, durH, durM, startW, endW, remTime As Long
Dim startH, endDate As Double

rangeH = 8 ' Starting hour of working day
numH = 9 ' Length of working day in hours
rangeD = 2 ' Starting day of working week
numD = 5 ' Length of working week in days

' Calculates offset from 00:00 Monday in starting week
startW = Fix(StartTime) - DatePart("w", StartTime)
startD = DatePart("w", StartTime) - rangeD
startH = (StartTime - Fix(StartTime)) * 24

' Calculates end time in working weeks, hours, minutes
remTime = Minutes + (startD * numH * 60) + ((startH - rangeH) * 60)
durW = Fix(remTime / 60 / numH / numD)
remTime = remTime - (durW * numD * numH * 60)
durD = Fix(remTime / 60 / numH)
remTime = remTime - durD * 60 * numH
durH = Fix(remTime / 60)
remTime = remTime - durH * 60
durM = remTime

' Converts working weeks into calendar weeks
endDate = startW + durW * 7 + rangeD + durD + (rangeH + durH) / 24 + durM / 1440
EndDayTimeM = endDate
End Function

Answer 2

Você ficaria melhor com algo assim -

Public Function EndDayTimeM(StartTime As String, Minutes As Double)   

Dim begintime As Date
begintime = CDate(starttime)

Dim startminutes As Double
startminutes = Hour(starttime) * 60 + Minute(starttime)
Dim x As Integer
x = startminutes + minutes

Dim endtime As Date

If x < 1020 Then
endtime = DateAdd("n", minutes, begintime)
MsgBox (endtime)
End If

If x > 1020 Then

        If Weekday(begintime, vbMonday) = 5 Then
            endtime = DateAdd("y", 3, begintime)
            Else: endtime = DateAdd("y", 1, endtime)
        End If

    endtime = DateAdd("n", minutes, endtime)
    endtime = DateAdd("n", -480, endtime)
    MsgBox (endtime)
End If

End function

Answer 3

O que eu estava falando na minha resposta anterior que foi rejeitada, na prática:

Public Function EndDayTimeM(StartTime As String, Minutes As Double)
Dim start As Date, starthour As Date, endhour As Date, minutes2 As Date

start = CDate(StartTime)
minutes2 = DateAdd("n", Minutes, 0)
starthour = 8 / 24 'working day starts at 8
endhour = 16 / 24  'working day ends at 16, wasn't it 17?

While minutes2 > 0 'while we have time remaining
    If Weekday(start, vbMonday) < 6 Then 'if it's a weekday
        EndDayTimeM = start + minutes2 'it ends at the date (soonest possible)
        minutes2 = start + minutes2 - CDate(Int(start) + endhour) 'the remaining minutes as a difference between the sum of start and norm minus the end of the day
        start = Int(start) + 1 + starthour 'next start is tomorrow's starting
    Else
        start = start + 1 'if weekend, skip a day
    End If
Wend
End Function