Generate Dates & Workdays Between Two Dates (RANDBETWEEN & WORKDAY)

Set up real dates and an inclusive window

Use =DATE(2026,7,1) in B2 and =DATE(2026,7,10) in B3. E2 and E3 contain =DATE(2026,7,3) and =DATE(2026,9,7). DATE creates numeric dates without relying on locale-specific text parsing. Format the inputs as dates.

B4 is the one-based workday index, initially 3. B5 is a fixed fraction u, initially 0.4. C2 audits the inputs, C3 counts workdays, C4 selects by index, C5 selects a workday by fraction, and C6 selects a calendar date by fraction. C7 validates u.

The formulas use the 1900 date system and whole dates from 1900-03-01 through 9999-12-31, avoiding the historical fictitious leap-day boundary. Times and reversed windows are rejected. Holidays may be numeric whole dates or genuinely empty cells; adjust all holiday ranges together when adding holidays.

=IF(COUNT(B2:B3)<>2,"Enter numeric dates",IF(OR(B2<>INT(B2),B3<>INT(B3),B2<61,B3>2958465,B2>B3),"Use whole dates in ascending order",IF(COUNTA(E2:E3)<>COUNT(E2:E3),"Use numeric holiday dates or blank cells",IF(OR(IF(E2="",FALSE,OR(E2<>INT(E2),E2<61,E2>2958465)),IF(E3="",FALSE,OR(E3<>INT(E3),E3<61,E3>2958465))),"Use whole holiday dates","OK"))))

Count the available workdays

C3 counts both endpoints when eligible. The initial seven workdays are July 1, 2, 6, 7, 8, 9 and 10. July 3 is excluded only because it is explicitly in our example holiday list. September 7 is outside the window and has no effect.

Saturday and Sunday are excluded. This is not an automatically maintained national holiday calendar. For different weekend patterns, adapt both the counting and stepping functions together using the corresponding INTL functions.

=IF(C2<>"OK",C2,NETWORKDAYS(B2,B3,E2:E3))

Select the k-th workday

C4 returns July 6 for k=3. Starting from B2−1 makes k=1 select the first eligible day on or after B2. The last index selects the last eligible day on or before B3.

A weekend-only or fully excluded window produces No workdays in window. A non-integer or out-of-range index is rejected instead of being silently truncated. TEXT returns a display string; omit TEXT and apply a date format if the result must remain a numeric date for later arithmetic.

N(B4), N(B5) and N(C3) in the workday formulas provide numeric arguments during intermediate evaluation. The outer validation still rejects invalid inputs; these conversions do not authorize treating missing data as a valid selection. The random workday formula also supplies a minimum internal upper bound of 1 while the outer guard reports an empty pool.

=IF(C2<>"OK",C2,IF(C3=0,"No workdays in window",IF(NOT(ISNUMBER(B4)),"Enter numeric index",IF(OR(B4<>INT(B4),B4<1,B4>C3),"Sample Index Out of Window",TEXT(WORKDAY(B2-1,N(B4),E2:E3),"yyyy-mm-dd")))))

Repeatable selection with a fixed fraction

u must satisfy 0 <= u < 1. The earlier inclusive upper bound would produce index N+1 when u=1, outside the window. A fixed fraction is a deterministic selector, not a random-number-generator seed. It yields the same result only while the window and holiday list also stay unchanged.

Put =IF(NOT(ISNUMBER(B5)),"Use u in [0,1)",IF(OR(B5<0,B5>=1),"Use u in [0,1)","OK")) in C7. C5 uses the formula below: u=0.4 selects the third of seven workdays, July 6. u=0 selects the first; a value sufficiently close to 1 selects the last.

=IF(C2<>"OK",C2,IF(C3=0,"No workdays in window",IF(C7<>"OK",C7,TEXT(WORKDAY(B2-1,1+INT(N(B5)*N(C3)),E2:E3),"yyyy-mm-dd"))))

Repeatable calendar-date selection

C6 uses every calendar date, including weekends and holidays. With the initial ten-day window and u=0.4 it returns July 5. The +1 in the count includes both endpoints.

=IF(C2<>"OK",C2,IF(C7<>"OK",C7,TEXT(B2+INT(B5*(B3-B2+1)),"yyyy-mm-dd")))

Random calendar dates

Use this formula in an empty output cell. In Excel, RANDBETWEEN generates a new integer when the worksheet recalculates; the exact recalculation triggers can differ in Arte Sheets. Copy and paste values to freeze a draw.

Repeated draws allow duplicates. The weekend probability depends on the actual window; it is not always exactly 2/7. Uniform selection of the integer dates corresponds to uniform selection of days in that window.

=IF(C2<>"OK",C2,TEXT(RANDBETWEEN(B2,B3),"yyyy-mm-dd"))

Random workdays without leaving the window

Draw an index from 1 through the validated workday count, then map it to its date. The empty-pool guard prevents calling RANDBETWEEN(1,0). Each eligible date has one index; holidays and weekends are skipped rather than shifted after sampling.

The practice sheet uses fixed selections so its results can be checked. For live random draws use this formula in another cell. These examples sample with replacement, not a unique shuffled schedule.

=IF(C2<>"OK",C2,IF(C3=0,"No workdays in window",TEXT(WORKDAY(B2-1,RANDBETWEEN(1,MAX(1,N(C3))),E2:E3),"yyyy-mm-dd")))

Further reading

• Microsoft Support — RANDBETWEEN function
• Microsoft Support — WORKDAY function