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

Topic

New Reply

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

Marc

Reply | Quote

Replies

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

Reply | Quote

Works!!!!

Thanks again!

Reply | Quote

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.

Reply | Quote

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}))

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Will do thanks!

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

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

Steve

Reply | Quote

Steve even if that data is zip codes for example?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Should pull a random zip from a list of 10…

Steve

Reply | Quote