H.G asks:

I need to create lots of random dates within a certain year, for which I simply use =randbetween().
However, only Mondays to Fridays are required, no weekend days. How could I possibly adapt a function / formular to bring just those about ?

Answer:

Random dates, Mon to Fri, within a year (duplicates allowed).

Array formula in cell A3:

=DATE(2010, 1, 1)+LARGE(IF(WEEKDAY(DATE(2010, 1, 1)+ROW($1:$365)-1, 2)<6, ROW($1:$365)-1, ""), RANDBETWEEN(1, SUM(--(WEEKDAY(DATE(2010, 1, 1)+ROW($1:$365)-1, 2)<6)))) + CTRL + SHIFT + ENTER.  Copy cell A3 and paste it down as far as needed.

Random unique dates, Mon to Fri, within a year

Array formula in cell E3:

=DATE(2010, 1, 1)+(LARGE(IF(WEEKDAY((DATE(2010, 1, 1)+ROW($1:$365)-1)*((COUNTIF($E$2:E2, DATE(2010, 1, 1)+ROW($1:$365)-1))=0), 2)<6, ROW($1:$365)-1, ""), RANDBETWEEN(1, SUM(--(WEEKDAY((DATE(2010, 1, 1)+ROW($1:$365)-1)*((COUNTIF($E$2:E2, DATE(2010, 1, 1)+ROW($1:$365)-1))=0), 2)<6))))) + CTRL + SHIFT + ENTER. Copy cell E3 and paste it down as far as needed.

This formula is surprisingly slow.

Download excel sample file for this tutorial.

Generate-random-dates-within a year-with-a-criterion.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

ROW(reference) returns the rownumber of a reference

WEEKDAY(serialnumber, [return_type])
Returns a number from 1 to 7 identifing the day of the week of a date

IF(logical_test, [value_if_true], [value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

LARGE(array,k) returns the k-th largest row number in this data set

RANDBETWEEN(bottom, top)
Returns a random number between the numbers you specify

DATE(year,month,day) returns the number that represents the datein Microsoft Office Excel date-time code