Random Workday Between Two Dates

[ExcelKnut]

Hi,

I'm trying to generate a random work day between two dates. I tried using =WORKDAY(RANDBETWEEN([@FWDOM]-1,[@LWDOM]-1),1,Holidays) where FWDOM is the first work day of the month and LWDOM is the last work day of the month. Unfortunately, this formula gives me too many Mondays since a random Friday, Saturday or Sunday returns Monday.

Any assistance would be greatly appreciated.

Jeff

 

[shg]

Maybe ...

=WORKDAY("9/1/2013", RANDBETWEEN(1, NETWORKDAYS("9/1/2013"-1, "9/30/2013")))

 

[Redwolfx]

[COLOR=#0000ff]=WORKDAY("9/1/2013", RANDBETWEEN(1, NETWORKDAYS("9/1/2013"-1, "9/30/2013")))[/COLOR]

Returns some Values of 10/1/13 if the 9/1/13 is 9/2/13

If
A1 = Start Date
A2 = End Date (This needs to be a weekday)

=MIN($A$2,DATE(YEAR(TODAY()),1,RANDBETWEEN(WEEKNUM($A$1-1,2),WEEKNUM($A$2-1,2))*7+RANDBETWEEN(0,4)))

Mine Will create a few more End of Month Days though if the end of the month falls on a Monday (Like this month)

 

[ExcelKnut]

Actually I think a slight modification of shg's solution will work. Please see the formula below and let me know if I'm incorrect.

B4 is the first date of the month (09/01/13)
B5 is the last date of the month (09/30/13)

=WORKDAY($B$4-1, RANDBETWEEN(1, NETWORKDAYS($B$4, $B$5,Holidays)))

I subtracted one day from the first date of the month for the WORKDAY start date (so it starts on 8/31/13) and instead of subtracting one day from the first date of NETWORKDAY I simply found the NETWORKDAYS between the two dates which gives me the number of work days in the month (I.E. 09/01/13 to 09/30/13 has 20 workdays excluding Labor Day 9/2).

Here's how it appears to work....it starts with the last date of previous month and randomly chooses a workday between the two dates (in my example from the 1st work day to the 20th work day) and it seems to always work which of course is the goal. Further, it appears to be actually random and doesn't select dates outside of the two dates.

Please let me know if I'm missing something.

Jeff

 

[shg]

If the start and end dates are always workdays, I think all you need is

=WORKDAY(dateBeg, RANDBETWEEN(0, NETWORKDAYS(dateBeg, dateEnd, Holidays) - 1), Holidays)