在我的工作表中,我想根据工作日的时间范围来计算流程的预计结束时间。
假设工作日为 8:00 - 17:00,不包括周六和周日。
因此,例如,当我将 4 小时加到 14:00 时,我不希望结果为 18:00,而是 9:00(3 小时带您到该工作日的结束,最后一小时开始下一个工作日)。
谁能帮我吗?
编辑 -
在 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
GoTo Ret
Hell:
EndDayTimeM = Err.Description
Ret:
End Function
答案1
在 Excel 中,转到“开发人员功能区”选项卡并单击“Visual Basic”。
右键单击 VBA 项目(红色轮廓),然后选择“插入”,再选择“模块”。这会将 Module1 添加到 VBAProject 下方的项目中。双击 Module1。
选择右侧的面板并粘贴以下代码:
Option Explicit
Public Function EndDayTime(StartTime As String, Hours As Double)
On Error GoTo Hell
' start and end hour are fixed here.
' could put them in cells and look them up
Dim startHour As Long, endHour As Long
startHour = 8
endHour = 17 ' was 18
Dim calcEnd As Date, start As Date
start = CDate(StartTime)
calcEnd = DateAdd("h", Hours, 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
EndDayTime = calcEnd
GoTo Ret
Hell:
EndDayTime = Err.Description
Ret:
End Function
然后,您可以在工作表中使用它,如下所示: 在此示例中,我在 A2 中输入了日期时间,在 B2...B8 中输入了小时数,C 列具有使用 Ax 和 Bx 的 EndDayTime 函数。在 A 列第 2 行之后,它只是复制 C[row - 1],因此上一个结束时间将成为下一个开始时间
欢呼 -
编辑:更正代码,结束时间从 17 变为 18。