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

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

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

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sebation" wrote in message
...
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

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

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(IN DIRECT(A1&":"&B1))),1+INT(RAND()*SUM(--(DAY(ROW(INDIRECT(A1&":"&B1))+1)2))))

confirmed with CTRL+SHIFT+ENTER

"Max" wrote:

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sebation" wrote in message
...
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

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

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,"")
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"daddylonglegs" wrote in message
...
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.