Randomly generate dates of the current month and excludes weekends

I am trying to create a form that randomly generate dates of the current month and excludes weekends.

I tried to us “=RANDBETWEEN(DATE(2016, 08, 01),(DATE(2016, 08, 31))” this resolves the date range but I have to change the formula each month and it does not exclude weekends.

I created a list of dates using the autofill option, selecting weekdays to remove weekend from my list but I can’t use the field location to substitute in the date field (DATE(2016, 08, 01).

This method does not work because it involves modify this every month.

I tried using “=WORKDAY(C2, 20, F2:F14)” which will exclude weekends and holidays I choose but I can’t seem to make random. I know I am close but…


I have tried to include an attachment but When I select the attachment icon all I get is a small box.Capture.JPG

Hi Jaycee,

You can do a Weekday() function and add a second parameter/argument. I chose 2 so Monday would come back as a 1. Then Saturday and Sunday would be day 6 and 7.
The logic goes like this. Pick a random day in a month. Then do the Weekday( ,2) on it. If it is 1 to 5 then it is a weekday and don't change it. If it is Saturday (6) then subtract a random between 1 and 5 days. If it is a Sunday (7) then subtract a random between 2 and 6.

I've created some helper columns in the attached and a double if statement to get those random "work" days. See if you can follow my formulas and make one of your own.

Random Dates without weekends.xlsx