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!

 

[shg]

That's wrong ...

 

[Rick Rothstein]

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)

That's wrong ...

Your formula may not have worked correctly, but it did remind of the EOMONTH function which can simplify the formula I posted in Message #9 somewhat, so thank you for having posted the formula so that it could, in fact, remind me. Here is my formula with EOMONTH used to calculate the last day of the month (using cell A2 for the date this time)...

=INDEX(SMALL(IF(ROW(INDIRECT("1:"&DAY(EOMONTH(A2,0))))<>DAY(A2),ROW(INDIRECT("1:"&DAY(EOMONTH(A2,0))))),ROW(INDIRECT("1:"&DAY(EOMONTH(A2,0))-1))),RANDBETWEEN(1,DAY(EOMONTH(A2,0)-1)))

The formula is still "ugly", just a little less so. Oh, and by the way, I forgot to mention in my first post that I used Barry's formula (posted in Message #2) as base and modified it to limit the day selection... just wanted to make sure proper credit went where it was deserved.

 

[shg]

Ah:

=DATE(YEAR(A$2), MONTH(A$2), MOD(DAY(A$2) + RANDBETWEEN(0, DAY(EOMONTH(A$2, 0) - 2)), DAY(EOMONTH(A$2, 0))) + 1)

 

[mikerickson]

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)

The request was for a random number between 1 and 30. (That didn't match...)
There is no need to adapt the formula for different length months. If the date given is in February and 30 is the random number returned, the OP's conditions are met.

 

[shg]

No argument, but the numbers no longer have a uniform probability distribution.

 

[barry houdini]

There is no need to adapt the formula for different length months. If the date given is in February and 30 is the random number returned, the OP's conditions are met.

Hello Mike,

I think Stephen_IV revised his requirement in his second post, so I think shg's suggestion addresses that revised requirement

=DATE(YEAR(A$2), MONTH(A$2), MOD(DAY(A$2) + RANDBETWEEN(0, DAY(EOMONTH(A$2, 0) - 2)), DAY(EOMONTH(A$2, 0))) + 1)

I like it, shg,

....but I think only a number is required so you only really need the day part as per Mike's initial setup, i.e. just

=MOD(DAY(A$2) + RANDBETWEEN(0, DAY(EOMONTH(A$2, 0) - 2)), DAY(EOMONTH(A$2, 0))) + 1

 

[mikerickson]

How about =DAY(RANDBETWEEN(A1+1, EDATE(A1,1)-1))

 

[barry houdini]

I think that would fall down on dates like 31-Jan-2015 - your formula wouldn't generate a number higher than 27

 

[mikerickson]

I think that would fall down on dates like 31-Jan-2015 - your formula wouldn't generate a number higher than 27

I guess the question reverts back to what the OP is looking for.
If they are looking for a number between 1 and 30
or if they are looking for a date between now and a month from now (excluding end points).

 

[Rick Rothstein]

I guess the question reverts back to what the OP is looking for.
If they are looking for a number between 1 and 30
or if they are looking for a date between now and a month from now (excluding end points).

It was my understanding that for a given date, he wanted a random valid day number for that month as long as that number was not the given date's day value. So, if the given date were February 13, 2016 (a leap year), then the random number could be an number from 1 to 29 so long as it was not 13.