☰

计算工作时间结束时间并跳过周末

tag-icon • tag-icon microsoft-excel worksheet-function vba
计算工作时间结束时间并跳过周末

在我的工作表中，我想计算流程的预计结束时间。

但是，我想将其限制在预定的时间限制内。例如，当我将 4 小时添加到 14:00 时，我不希望结果为 18:00，而是 9:00！

假设工作日为 8:00 - 17:00。不包括周六和周日

谁能帮我吗？

在 rcl 的 Simon 的帮助下，我设法调整了他的解决方案，使其也能用分钟来计算。然而，似乎有一个问题。当我添加

960 分钟到 22-05-15 16:00 该函数给出的正确结果是 26-05-15 14:00

然而，由于额外一小时（60 分钟），结果将变回 25-05-15 09:00。

有人看到这里的问题吗？

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

答案1

以下内容将满足您的要求，并且完全可配置，此外，它还支持任何输入或输出格式，只要 Excel 仍将其理解为数字日期+时间。您可以为工作时间/天数设置任何开始或结束时间。

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

答案2

你最好这样做 -

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

答案3

我在之前的回答中谈到了被否决的内容，实际上：

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

相关内容