excel - 如何获得周转周末和下类后的响应时间？

Question

我已经在这个项目上工作了一段时间，但我一无所获，所以我想我会在这里问你们。有一堆任务:报价、装订、发行……它们每个都有自己的响应时间。

报价必须在 3 小时内完成，而装订是 8 小时，签发有 2 天的周转时间。但是，问题在于响应时间仅基于 9:00 - 8:00 pm(EST)时间，不包括周末和节假日。我有一个假期查找表，以及从另一个查找表索引的任务时间。

我被卡住的部分是关于“停止时钟”并且如果它是在晚上 8:00 之后让任务响应时间转到第二天。

这是我为此创建的公式，但它不能正常工作，因为如果我将 Time 更改为 (48,0,0) 用于发布或 Time(8,0,0) 用于绑定(bind)，它将显示相同的时间. P3 列有开始时间。

=IF(AND(TEXT(P3,"dddd")="Friday",HOUR(P3)+MINUTE(P3)/60+SECOND(P3)/(60*60)>17),P3+TIME(15,0,0)+DAY(2),IF(HOUR(P3)+MINUTE(P3)/60+SECOND(P3)/(60*60)>17,P3+TIME(15,0,0),P3+Time(3,0,0)))

谢谢!任何帮助将不胜感激!

Answer 1

以下是一些未经测试且未完全实现的代码供您开始:

Function GetTurnaroundDateAndTime(TaskType As String, StartTime As Date, TaskTimeRange As Range, HolidayLookupRange As Range)
    Dim taskTime As Double
    Dim dayBegin As Double 'could be a parameter
    Dim dayEnd As Double 'could be a parameter
    Dim result As Date
    Dim isValid As Boolean
    Dim offset As Double

    dayBegin = 9 'could be a parameter
    dayEnd = 20 'could be a parameter
    offest = 0

    'Get Task Time in hours
    taskTime = GetTaskTime(TaskType, TaskTimeRange)

    'Calculate initial turnaround time (without regard to nights/weekends/holidays)
    result = DateAdd("h", taskTime + offset, StartTime)

    'check if it's a valid turnaround date and time, return if so
    isValid = False
    Do While isValid = False
        'check #1 - is the turnaround time before the day begins?
        If Hour(result) < 9 Then
            If Hour(StartTime) < 20 Then
                offset = offset - 20 + Hour(StartTime) 'check to see if a portion of the task time would be before end of day, subtract this amount from the offset
            End If
            offset = offset + 9 = Hour(result) 'gets the offset to the beginning of day
        ElseIf Weekday(result, vbSaturday) = 1 Then
            offset = offset + 48 'if we're on a Saturday, add two days
        ElseIf Weekday(result, vbSunday) = 1 Then
            offset = offset + 24 'if we're on a Sunday, add one day
        ElseIf IsHoliday(result, HolidayLookupRange) Then
            offset = offset + 24 'if we're on a holiday, add one day
        Else
            isValid = True
        End If

        result = DateAdd("h", taskTime + offset, StartTime) 're-evaluate result
    Loop

    GetTurnaroundDateAndTime = result
End Function

Function GetTaskTime(TaskType As String, TaskTimeRange As Range) As Double
    'TODO: implement function to lookup the task time from the table
    GetTaskTime = 3
End Function

Function IsHoliday(DateToLookup As Date, HolidayLookupRange As Range) As Boolean
    'TODO: implement function to lookup if date is a holiday
    IsHoliday = False
End Function

以下是一些可以帮助您开始使用 VBA 的链接:

https://support.office.com/en-us/article/create-custom-functions-in-excel-2f06c10b-3622-40d6-a1b2-b6748ae8231f

https://www.fontstuff.com/vba/vbatut01.htm

在对代码感到满意之前，您需要测试很多不同的场景。我只是把一些东西放在一起!