如何基于基准日期、优先级及请求类型前缀计算到期日

Alright, let's figure out how to calculate that due date in column J based on your requirements. Here's a step-by-step breakdown tailored to your setup:

First, let's align on what we're working with to avoid missteps:

• We start with the base date in column N
• We adjust this date using two key factors:
  1. Priority level in column L (values 2, 3, 4, 5), which maps to corresponding adjustment values in column K
  2. The prefix of the request type in column C (three categories: DR, S, and one other unspecified type)

I’ll use reasonable placeholder values to build a usable formula—you can swap these out for your actual numbers:

• Column K stores the number of days to add for the matching priority in column L (e.g., if row 2 has L=2, K2 is the day count for priority 2)
• Request type prefix adjustments:
  • "DR" prefix: add 3 days
  • "S" prefix: add 5 days
  • All other prefixes: add 7 days

Basic Date Calculation (Includes Weekends) #

If you just need to add calendar days (no exclusion of weekends/holidays), use this formula in cell J2, then drag it down the column:

= N2 + K2 + 
  IF(LEFT(C2, 2) = "DR", 3, 
     IF(LEFT(C2, 1) = "S", 5, 7))

Breakdown of the formula: #

• N2: Your starting base date
• K2: The priority-specific day adjustment (tied directly to the priority in L2)
• LEFT(C2, 2) = "DR": Checks if the first 2 characters of the request type are "DR" (adjust the number in LEFT() if your "DR" prefix is longer/shorter)
• LEFT(C2, 1) = "S": Checks if the first character is "S" (tweak the LEFT() number if your "S" prefix is longer, like "SR")

Priority Mapping via Lookup (If Priorities Are Stored in a Separate Table) #

If your priority levels (2-5) and their corresponding day adjustments are stored in a fixed range (e.g., L2:K5 where L has 2/3/4/5 and K has the days), use VLOOKUP instead:

= N2 + VLOOKUP(L2, $L$2:$K$5, 2, FALSE) + 
  IF(LEFT(C2, 2) = "DR", 3, 
     IF(LEFT(C2, 1) = "S", 5, 7))

The $L$2:$K$5 locks the lookup range so it doesn't shift when you drag the formula down, and FALSE ensures we get an exact match for the priority level.

Business Day Calculation (Exclude Weekends/Holidays) #

If you need to calculate only working days (no weekends or company holidays), use the WORKDAY function:

= WORKDAY(N2, K2 + 
  IF(LEFT(C2, 2) = "DR", 3, 
     IF(LEFT(C2, 1) = "S", 5, 7)), $P$2:$P$10)

• Replace $P$2:$P$10 with the range where you've listed your company holidays (this is optional—omit it if you only want to exclude weekends)

1. Swap the numbers 3, 5, 7 with your actual day adjustments for each request type prefix
2. Adjust the LEFT() character counts if your prefixes are longer/shorter (e.g., use LEFT(C2,3) for a "DRS" prefix)
3. If your priority mapping is different (e.g., priority 2 maps to a fixed value instead of the same row's K column), adjust the VLOOKUP range or replace K2 with the fixed value

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