Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Dec 2009
    Location
    Atlanta, GA
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hi Marc - Try this ....
    =DATE(YEAR(TODAY())-18,MONTH(TODAY()),DAY(TODAY()))

  3. #3
    3 Star Lounger
    Join Date
    Dec 2009
    Location
    Atlanta, GA
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Will do thanks!

  4. #4
    3 Star Lounger
    Join Date
    Dec 2009
    Location
    Atlanta, GA
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Works!!!!

    Thanks again!

  5. #5
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by tfspry View Post
    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. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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. #7
    3 Star Lounger
    Join Date
    Dec 2009
    Location
    Atlanta, GA
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    =RAND()*(Max-Min)+Min

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

    Steve

  9. #9
    3 Star Lounger
    Join Date
    Dec 2009
    Location
    Atlanta, GA
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Steve even if that data is zip codes for example?

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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. #11
    3 Star Lounger
    Join Date
    Dec 2009
    Location
    Atlanta, GA
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #12
    3 Star Lounger
    Join Date
    Dec 2009
    Location
    Atlanta, GA
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Assuming list is in A1:A10
    =index(A1:A10,10*RAND()+1)

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

    Steve

  14. #14
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by tfspry View Post
    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
    [MS MVP - Word]

Posting Permissions

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