Excel中基于9/80工作制的任务完成日期计算问题求助

Alright, let's solve this 9/80 work schedule calculation headache in Excel. You're spot-on that the WORKDAY() function can't handle this because it treats every workday as identical (8 hours by default) and doesn't account for your alternating Friday off schedule. Here are two solid solutions tailored to your setup:

Solution 1: Dynamic Array Formula (Excel 365/2021) #

If you're on a modern Excel version with dynamic array support, this formula will calculate your finish date without needing macros. We'll generate a sequence of dates, filter out holidays/rest days, calculate daily work hours, and track cumulative hours until we hit your task's total duration.

Assumptions: #

• Your task's start date is in cell A2
• Your task's total required hours are in cell B2
• Your pre-built list of holidays + alternating rest Fridays is in range $G$2:$G$100

The Formula: #

=LET(
    start_date, A2,
    total_hours, B2,
    holidays, $G$2:$G$100,
    date_seq, SEQUENCE(100,,start_date),
    work_dates, FILTER(date_seq, ISNA(MATCH(date_seq, holidays, 0))),
    daily_hours, IF(WEEKDAY(work_dates,2)=5,8,9),
    cumulative, SCAN(0, daily_hours, LAMBDA(acc,hr,acc+hr)),
    XLOOKUP(TRUE, cumulative>=total_hours, work_dates,,1)
)

How It Works: #

1. LET() lets us name variables for readability, so we don't repeat references.
2. SEQUENCE() generates a list of 100 dates starting from your task's start date (adjust the 100 to a larger number if you have extra-long tasks).
3. FILTER() removes any dates that match your holiday/rest day list.
4. IF(WEEKDAY(...)) assigns 8 hours to workdays that are Fridays, 9 hours to Monday-Thursday workdays.
5. SCAN() calculates running total hours for each workday.
6. XLOOKUP() finds the first date where the cumulative hours meet or exceed your task's total duration.

Solution 2: VBA Custom Function (All Excel Versions) #

If you're using an older Excel version without dynamic arrays, a custom VBA function is the way to go. This will loop through each day, skip holidays/rest days, and subtract daily hours from your total until we reach the finish date.

Step 1: Add the VBA Code #

1. Press Alt + F11 to open the VBA Editor.
2. Right-click your workbook in the Project Explorer > Insert > Module.
3. Paste this code into the module:

Function CalculateFinishDate(startDate As Date, totalHours As Double, holidays As Range) As Date
    Dim currentDate As Date
    Dim remainingHours As Double
    Dim isHoliday As Boolean
    
    currentDate = startDate
    remainingHours = totalHours
    
    Do While remainingHours > 0
        ' Check if current date is in the holiday/rest day list
        isHoliday = Not IsError(Application.Match(currentDate, holidays, 0))
        
        ' Only count weekdays that aren't holidays
        If Weekday(currentDate, vbMonday) <= 5 And Not isHoliday Then
            ' Subtract the correct hours for the day
            If Weekday(currentDate, vbMonday) = 5 Then
                remainingHours = remainingHours - 8
            Else
                remainingHours = remainingHours - 9
            End If
            
            ' If we've covered the total hours, return this date
            If remainingHours <= 0 Then
                CalculateFinishDate = currentDate
                Exit Function
            End If
        End If
        
        ' Move to the next day
        currentDate = currentDate + 1
    Loop
End Function

Step 2: Use the Function in Excel #

In any cell, enter:

=CalculateFinishDate(A2, B2, $G$2:$G$100)

Replace the cell references with your actual data range.

Extra Tips #

• To generate your alternating rest Friday list automatically (instead of typing them manually), use this formula (adjust the start date to your first rest Friday):

  =DATE(2024,1,5) + 14*SEQUENCE(26)
  

  This will generate 26 rest Fridays for a full year (since 9/80 uses every other Friday off).
• If you need to calculate partial days (e.g., a task finishes halfway through a workday), you can modify the VBA function to return a date-time value instead of just a date.

内容的提问来源于stack exchange，提问作者Marodr