Excel中SIM变量计算错误修正：基于130个工作日的日期区间统计

Let's break down how to correctly compute your SIM variable, since your current results are off (like those 1.0 values for March-April 2015 which don't make sense—those dates' 130 subsequent workdays haven't even hit the May window yet!).

The Problem Recap #

You need to calculate:

SIM = d / 130

Where d is the count of workdays within the next 130 workdays from a given date that fall between:

• The 2nd workday of May (of the year each workday belongs to)
• The 15th workday of October (of the same year)

Why Your Current Formula Fails #

Looking at your 2015 data, seeing SIM = 1.0 for dates in March-April means your formula is incorrectly counting all 130 workdays as part of the target window. That's a clear sign your interval logic isn't correctly checking if each workday falls between the May 2nd and October 15th workdays of its own year.

Correct Excel Formula Implementation #

Assuming your base dates are in column A (starting at A2), here's how to compute d and SIM properly:

Step 1: Calculate d (Number of qualifying workdays) #

Use this array formula (press Ctrl+Shift+Enter for pre-365 Excel; just Enter for Excel 365/2021):

=SUMPRODUCT(
  --(WORKDAY(A2,ROW(INDIRECT("1:130"))) >= WORKDAY.INTL(DATE(YEAR(WORKDAY(A2,ROW(INDIRECT("1:130")))),5,1),1,"0000011")),
  --(WORKDAY(A2,ROW(INDIRECT("1:130"))) <= WORKDAY.INTL(DATE(YEAR(WORKDAY(A2,ROW(INDIRECT("1:130")))),10,1),14,"0000011"))
)

Step 2: Calculate SIM #

Just divide the result from Step 1 by 130:

= [d-formula] / 130

Formula Breakdown #

Let's unpack what each part does:

1. Generate 130 subsequent workdays: WORKDAY(A2,ROW(INDIRECT("1:130"))) creates an array of the next 130 workdays starting from the date in A2. For Excel 365, use WORKDAY(A2,SEQUENCE(130)) for cleaner, more efficient code.
2. Find May's 2nd workday: WORKDAY.INTL(DATE(YEAR(wd),5,1),1,"0000011") calculates the 2nd workday of May for the year each workday (wd) belongs to. The "0000011" parameter sets a standard Saturday-Sunday weekend (adjust this if your region uses different rest days).
3. Find October's 15th workday: WORKDAY.INTL(DATE(YEAR(wd),10,1),14,"0000011") gets the 15th workday of October (we use 14 as the offset because we start counting from October 1st).
4. Count qualifying days: SUMPRODUCT with --(condition) converts boolean true/false values to 1/0, then multiplies the two condition arrays to count only workdays that meet both the lower and upper interval rules.

Key Adjustments for Your Data #

• Holidays: If you need to exclude company/government holidays, add a range of holiday dates as the 4th parameter in WORKDAY.INTL (e.g., WORKDAY.INTL(start_date, offset, "0000011", $C$2:$C$20) where $C$2:$C$20 is your holiday list).
• Weekend rules: Modify the "0000011" string if your workweek doesn't follow Saturday-Sunday weekends (each character represents a day from Sunday to Saturday; 1 = rest day, 0 = work day).

Example Check #

Take your 2015-04-20 date: the next 130 workdays will start including days from May 4th (2015's 2nd May workday) onwards. The count of days between that date and October's 15th workday divided by 130 should give you the ~0.992 value you see, which makes sense as only one day in the 130 might fall before the May window.

内容的提问来源于stack exchange，提问作者Évariste Galois