# Thread: Is there a simple way to generate dates based of of todays date but 18 year

1. ## Is there a simple way to generate dates based of of todays date but 18 year

ago eg 6/22/2011 -18 years ago would give me 6/22/1993.

Marc

2. Hi Marc - Try this ....
=DATE(YEAR(TODAY())-18,MONTH(TODAY()),DAY(TODAY()))

3. Will do thanks!

4. Works!!!!

Thanks again!

5. Originally Posted by tfspry
Hi Marc - Try this ....
=DATE(YEAR(TODAY())-18,MONTH(TODAY()),DAY(TODAY()))
Just a question and certainly not a correction, but shouldn't we be using the vba.date(vba.year(vba.today....etc? to make this global?

Just a question?

Thanks,
Darryl.

6. Not sure what you mean by "vba.year"... This is a formula, it is not using VBA at all, but built-in excel functions.

Steve

7. Next problem all the things that I want to do are based on using RANDBETWEEN which isn't an option in Excel 2002. Is there a work around?

Marc

8. =RAND()*(Max-Min)+Min

Where Max and min are the values, named ranges, or cells representing those values

Steve

9. Steve even if that data is zip codes for example?

10. If the zip codes are numbers it should work, though you may want to include something to make the final an integer:
=int(RAND()*(Max-Min)+Min)

Steve

11. Steve it works great on the dates! What I need is for it to randomly pick a zip code out of a list of 10 zip codes. Using the formula creates a zip code that not on the list but is in the range.

12. Steve thanks for your help. I did a little more searching and found this:

Assume names listed in A1:A10

Put in B1: =INDEX(A:A,RANK(C1,\$C\$1:\$C\$10))
Put in C1: =RAND()
Select B1:C1, fill down to C10

I made some changes for my application and it works great!

Again thanks

Marc

13. Assuming list is in A1:A10
=index(A1:A10,10*RAND()+1)

Should pull a random zip from a list of 10...

Steve

14. Originally Posted by tfspry
Hi Marc - Try this ....
=DATE(YEAR(TODAY())-18,MONTH(TODAY()),DAY(TODAY()))
Note: You need to check whether that formula returns what you expect on 29 February (ie 1 March)!

If you expect the formula to return 28 February, try:
=MIN(DATE(YEAR(TODAY())-18,MONTH(TODAY())+{0,1},DAY(TODAY())*{1,0}))

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•