Random Number from 1 to 30 that does not equal the Day of a date

[Stephen_IV]

Good morning,

I have a list of dates in Column A. What I need is a random number from 1 to 30 that does not equal the day number of that cell. For example if A1 is 12/4/2015 then I need a random number in B1 that does not equal 4. Another example is in A2 if I had 11/15/2013 then I need a random number that does not equal 15. Any help would be appreciated. Thanks in advance!

 

[barry houdini]

What if the date is 31st May? In that case is any number 1 to 30 OK?

If so try this "array formula"

=SMALL(IF(ROW(INDIRECT("1:30"))<>DAY(A1),ROW(INDIRECT("1:30"))),INT(RAND()*(29+(DAY(A1)=31)))+1)

confirmed with CTRL+SHIFT+ENTER

 

[etaf]

try in B1
=SMALL(IF(DAY(A1)={1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30},"",{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30}),RANDBETWEEN(1,29))

http://www.mrexcel.com/forum/excel-questions/157520-randbetween-excluding-values.html

 

[barry houdini]

=SMALL(IF(DAY(A1)={1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30},"",{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30}),RANDBETWEEN(1,29))

But in this case there's a possible "fly in the ointment" - If A1 is 31st of the month then B1 will only ever return a number in the range 1 to 29, never 30

 

[etaf]

it should give 30 - does in my test sheet, is looking in the array and when the day is one of the numbers it replaces that number with a "" and so only chooses from the 29 numbers on the array

i think thats how it works - going by the link

But it certainly includes 30

EDIT
MISREAD - 31st - good point

 

[Stephen_IV]

Thanks Guys! I appreciate the help and guidance! Barry I realize that there are some months that are 31 and there is also a leap year or 28 days in some cases. Instead of going crazy with a formula that takes all those things into account I just opted for 30 days although it would be cool to take all of that into account, Thanks again for your help!

 

[mikerickson]

Something like= Mod(Day(a1)+RandBetween(0,29),30)+1

 

[mikerickson]

Actually,
=MOD(DAY($A2)-1+RANDBETWEEN(1,29), 30)+1

 

[Rick Rothstein]

This is ugly as sin, but I believe this array-entered** formula meets your requirement of picking a random day number from 1 to the last day of the month in cell A1 without being able to pick the day number for the date in cell A1...

=INDEX(SMALL(IF(ROW(INDIRECT("1:"&DAY(A1-DAY(A1)-DAY(A1-DAY(A1)+32)+32)))<>DAY(A1),ROW(INDIRECT("1:"&DAY(A1-DAY(A1)-DAY(A1-DAY(A1)+32)+32)))),ROW(INDIRECT("1:"&DAY(A1-DAY(A1)-DAY(A1-DAY(A1)+32)+31)))),RANDBETWEEN(1,DAY(A1-DAY(A1)-DAY(A1-DAY(A1)+32)+31)))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

 

[shg]

Another way, just adapting Mike's approach for months of variable length:

=DATE(YEAR(A2), MONTH(A2), MOD(DAY(A2) + RANDBETWEEN(0, DAY(EOMONTH(A2, 0)) - 1), DAY(EOMONTH(A2, 0)) + 1) + 1)