Excel: Choose a Random Workday

  But what if you want to generate a random workday from the year—specifically, a date that occurs on a Monday through Friday excluding company holidays? If your typical workweek is Monday through Friday, then the WORKDAY function gets the job done. If you have a different work schedule—such as Monday, Wednesday, Friday—then use WORKDAY.INTL.   The WORKDAY function takes a starting date and then a number of workdays into the future. It optionally accepts a range of holiday dates to exclude. In Figure 1, the company holidays are listed in C2:C14. A single formula in A2 generates a list of 247 workdays in 2023: =WORKDAY(C2,SEQUENCE(247),C2:C14).
Monday, January 2, 2023, is used in this formula as the starting date by reusing the first holiday of the year from C2. In other cases, you could store the day before the first workday in another cell or use DATE(2023,1,2) or even “01/02/2023” as the first argument. The second argument that contains the sequence of numbers from 1 to 247 represents the range of workdays in the year. The third argument points to a range of company holidays to skip.
In Figure 1, the first four workdays of the year are Tuesday, January 3, through Friday, January 6. The WORKDAY function automatically skips Saturday and Sunday, so the fifth workday, shown in A6, is Monday, January 9. The list skips Monday, January 16, 2023, jumping from Friday, January 13, 2023, to Tuesday, January 17, 2023.  

  Say that you need to randomly schedule 10 surprise audits throughout the year. A single formula in E2 generates the 10 random workdays: =SORT(WORKDAY(C2,RAND ARRAY(10,1,1,247,TRUE),C2:C14)).
Let’s work from the inside of this formula to the outside. The RANDARRAY formula is generating 10 rows by 1 column of numbers between 1 and 247. The final TRUE argument limits the results to integers, so you don’t get any fractional days. This formula might generate a set of numbers such as 36, 50, 158, 129, 183, 33, 57, 60, 98, 45.   The WORKDAY function is similar to the function shown in cell A2. But instead of all 247 workdays, you’re asking for 10 random workdays using the results of RANDARRAY. They’ll be in a random order, so wrapping the whole function within SORT makes sure they’re in order from earliest to latest.  

PREVENTING REPEAT DATES

  None of the dates were repeated in the example. But as you increase the number of selected dates, there’s a greater chance that the same date will be selected twice. An alternate formula approach can prevent repeats.   Figure 2 shows a formula that randomly selects 20 workdays without any repeats. Cell E2 contains the formula =SORT(INDEX(SORTBY(A2#, RANDARRAY(247)),SEQUENCE(20))). It starts with the 247 workdays that are generated by the formula in A2. The SORTBY function combined with a RANDARRAY of 247 random numbers puts the 247 dates into a random sequence. Then the INDEX function with SEQUENCE(20) asks for the first 20 dates from the random sequence. Finally, those 20 dates are sorted.

USING ALTERNATE WORKWEEKS

  The WORKDAY function assumes that the workweek is always Monday through Friday. The newer WORKDAY.INTL function adds a “Weekend” argument so you can specify which days should be excluded from the workweek. When you’re entering the formula, the tooltip will show 14 built-in choices, such as 1 for a weekend of Saturday and Sunday, 7 for Friday and Saturday, and 11 for Sunday only.   But the most flexible way to specify the weekend is with a seven-digit binary number. The first digit represents Monday, and the last digit represents Sunday. Use a 1 to specify that the company is closed this day and a 0 to indicate that the company is open.   For example, 0000111 would specify the workweek for a company that’s open Monday through Thursday and closed on Friday through Sunday. For a company open Monday, Wednesday, and Friday, the weekend is essentially Tuesday, Thursday, Saturday, and Sunday: 0101011.   To generate all possible workdays that fall on a Monday, Wednesday, or Friday, use this formula in A2: =WORKDAY.INTL(C2, SEQUENCE(146),"0101011",C2:C14). Then to randomly select 12 days with no repeated dates, use this formula: =SORT(INDEX(SORTBY(A2#, RANDARRAY(146)),SEQUENCE(12))).

FORMAT RESULTS AS DATES

  For many date functions, Excel will automatically format your results as dates. The exceptions are any functions that originated from the Analysis ToolPak add-in. For an unknown reason, WORKDAY, NETWORKDAYS, WORKDAY.INTL, and NETWORKDAYS.INTL aren’t programmed to show the results as dates.  

  When you see answers that look like 44953, you’re seeing the date serial number in an unformatted state. Make sure to apply a date format to display the results as dates.