how to get the random date between the start date and the end date?

[Sebation]

hi,
how can get the random date if i give a start date(2007-01-01) to the end
date(2010-12-31)?futher,how can i achieve that the random date is not the
first date of month or the end date of month.
i.e start date 01-01-2007 end date 12-31-2007 i want get 02-01-2007 or
02-29-2008

can anybody give me a guide?

regards,
sebation

 

[Max]

One approach ..

In A1: =RANDBETWEEN(--"1-Jan-2007",--"31-Dec-2010")
In B1:
=IF(AND(DAY(A1)<>1,A1<>DATE(YEAR(A1),MONTH(A1)+1,0)),A1,A1+2)
B1 returns the random date screened off first/end day of month

 

[Guest]

But that would skew the results, dates which are the 2nd or 3rd of the month
would be more likely to appear....and you could also generate dates outside
the range.

A little more complex but......

If you have your start date in A1 and end date in B1 then this formula will
give a random date from that range which isn't the 1st or last of any month


=SMALL(IF(DAY(ROW(INDIRECT(A1&":"&B1))+1)>2,ROW(INDIRECT(A1&":"&B1))),1+INT(RAND()*SUM(--(DAY(ROW(INDIRECT(A1&":"&B1))+1)>2))))

confirmed with CTRL+SHIFT+ENTER

 

[Max]

Agreed.

OP: If you still want to play this tack, change B1 to:
=IF(AND(DAY(A1)<>1,A1<>DATE(YEAR(A1),MONTH(A1)+1,0)),A1,"")