使用Excel公式生成不重复随机日期时如何排除周末？

Got it, let's fix your formula to exclude weekends while keeping the non-repeating random date functionality. Here's how to do it, depending on your Excel version:

For All Excel Versions (Including Older Ones) #

We'll modify your existing LARGE-based formula to first filter out weekends, then pick non-repeating values from the remaining workdays.

Modified Formula (Array Formula) #

Enter this in cell B1, then drag down to your desired number of rows (e.g., 50 rows):

=IFERROR(LARGE(IF(WEEKDAY(ROW(INDIRECT($A$1&":"&$A$2)),2)<6,ROW(INDIRECT($A$1&":"&$A$2)))*NOT(COUNTIF($B$1:B1,IF(WEEKDAY(ROW(INDIRECT($A$1&":"&$A$2)),2)<6,ROW(INDIRECT($A$1&":"&$A$2)))),RANDBETWEEN(1,SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT($A$1&":"&$A$2)),2)<6))-ROW(B1)+1)),"")

Note: For pre-365 Excel versions, you need to enter this as an array formula by pressing Ctrl + Shift + Enter after typing it.

How It Breaks Down #

• WEEKDAY(...,2)<6: Checks if a date is a workday (Monday = 1, Friday = 5; excludes Saturday=6 and Sunday=7)
• SUMPRODUCT(--(...)): Counts the total number of workdays between A1 and A2 to set a valid upper limit for RANDBETWEEN
• NOT(COUNTIF($B$1:B1,...)): Ensures we don't repeat any dates already generated above the current cell
• LARGE(...): Picks a random remaining workday from the filtered list of valid dates

For Excel 365/2021 (Dynamic Array Version) #

If you have the latest Excel with dynamic array support, you can use a simpler one-shot formula that doesn't require dragging down:

=TAKE(SORTBY(FILTER(ROW(INDIRECT($A$1&":"&$A$2)),WEEKDAY(ROW(INDIRECT($A$1&":"&$A$2)),2)<6),RANDARRAY(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT($A$1&":"&$A$2)),2)<6)))),50)

How It Works #

1. FILTER: Pulls all workdays between your start date (A1) and end date (A2)
2. RANDARRAY: Generates a list of random numbers matching the count of workdays
3. SORTBY: Shuffles the workday list using the random numbers to ensure randomness
4. TAKE: Grabs the first 50 values from the shuffled list (adjust the 50 to your desired number of random dates)

Key Notes #

• Make sure cells A1 and A2 are formatted as dates (not plain text) — otherwise the ROW function won't interpret them correctly.
• If the number of workdays between A1 and A2 is less than the number of values you're trying to generate (e.g., 50), the formula will return empty cells or errors. Double-check your date range first!
• Press F9 to refresh and generate a new set of non-repeating random workdays.

内容的提问来源于stack exchange，提问作者J.Mapz