+ Reply to Thread
Results 1 to 15 of 15

CONCATENATE+TEXT+RANDBETWEEN+date format - doesn't work

  1. #1
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Question CONCATENATE+TEXT+RANDBETWEEN+date format - doesn't work

    Hi,
    I'd like to generate date with
    Please Login or Register  to view this content.
    but:
    1. days are generated
    2. months always 01
    3. how to generate years, i.e. from 2000 to 2015

    all advices are very welcome.
    sandy
    Last edited by sandy666; 03-01-2015 at 03:57 PM.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: CONCATENATE+TEXT+RANDBETWEEN+date format - doesn't work

    For random dates between 1/1/2000 & 12/31/2015, use:
    Please Login or Register  to view this content.
    Ben Van Johnson

  3. #3
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: CONCATENATE+TEXT+RANDBETWEEN+date format - doesn't work

    Thanks for answer protonLeah,

    but =RANDBETWEEN("1/1/2000","12/31/2015") => #VALUE

  4. #4
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: CONCATENATE+TEXT+RANDBETWEEN+date format - doesn't work

    Try this..

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Regards!
    =DEC2HEX(3563)

    If you like someone's answer, click the star to give them a reputation point for that answer...

  5. #5
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: CONCATENATE+TEXT+RANDBETWEEN+date format - doesn't work

    It works on my xl 2007 version(??)

  6. #6
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: CONCATENATE+TEXT+RANDBETWEEN+date format - doesn't work

    Working great Debraj Roy,
    Please Login or Register  to view this content.
    Many thanks

  7. #7
    Valued Forum Contributor
    Join Date
    11-11-2008
    Location
    Euro
    MS-Off Ver
    2007, 2010
    Posts
    470

    Re: CONCATENATE+TEXT+RANDBETWEEN+date format - doesn't work

    Quote Originally Posted by sandy666 View Post
    Hi,
    I'd like to generate date with
    Please Login or Register  to view this content.
    but:
    1. days are generated
    2. months always 01
    3. how to generate years, i.e. from 2000 to 2015
    all advices are very welcome.
    sandy
    this is a solution

    1st : date value is random between 1/1/2000 and 31/12/2015
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    or

    2nd: (convert to text)
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Best regard, -)iger-/iger
    If you are pleased with a solution mark your post SOLVED.

  8. #8
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: CONCATENATE+TEXT+RANDBETWEEN+date format - doesn't work

    ProtonLeah,

    I don't have ex2007 and don't know why on ex2013 result is #VALUE

    anyway thank you for help

  9. #9
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: CONCATENATE+TEXT+RANDBETWEEN+date format - doesn't work

    tigertiger,

    second option isn't good because definitions of yy mm dd are different in different languages. It cannot be changed by excel engine because there are " ".

    EDIt:

    It's working if I use it like: =RANDBETWEEN("36526","42369") and format cell as DATE

    thank you
    Last edited by sandy666; 03-01-2015 at 04:29 PM.

  10. #10
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: CONCATENATE+TEXT+RANDBETWEEN+date format - doesn't work

    Thank you all gurus,

    I got solution SOLVED

  11. #11
    Valued Forum Contributor
    Join Date
    11-11-2008
    Location
    Euro
    MS-Off Ver
    2007, 2010
    Posts
    470

    Re: CONCATENATE+TEXT+RANDBETWEEN+date format - doesn't work

    Quote Originally Posted by sandy666 View Post
    tigertiger,

    second option isn't good because definitions of yy mm dd are different in different languages. It cannot be changed by excel engine because there are " ".
    yeah, so you can choose the 1st solution, hope you get yr solution

  12. #12
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: CONCATENATE+TEXT+RANDBETWEEN+date format - doesn't work

    Quote Originally Posted by sandy666 View Post
    =DATE(RANDBETWEEN(2000,2015),RANDBETWEEN(1,31),RANDBETWEEN(1,12))
    Just FYI...

    Irrespective of system setting.. Date Command always took YEAR,MONTH,DATE..
    Please re-check your formula.. as everythig is random, and excel smart enough to convert DATE(2015,17,45) to a actual date,, you may not able to catch it..

  13. #13
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: CONCATENATE+TEXT+RANDBETWEEN+date format - doesn't work

    tigertiger,

    read my answer to you above in EDIT

    thank you

  14. #14
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: CONCATENATE+TEXT+RANDBETWEEN+date format - doesn't work

    So, I think the best and much shorter will be
    Please Login or Register  to view this content.
    and format cells to date format.

    Anyway I got some ideas and solutions

    Thank you all

  15. #15
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: CONCATENATE+TEXT+RANDBETWEEN+date format - doesn't work

    Quote Originally Posted by Debraj Roy View Post
    .....and excel smart enough to convert DATE(2015,17,45) to a actual date,, you may not able to catch it..
    Yes, that's right

    Even with the Month and Day parts swapped, i.e. with this version:

    =DATE(RANDBETWEEN(2000,2015),RANDBETWEEN(1,12),RANDBETWEEN(1,31))

    You may get dates in the correct range but some dates would be more likely than others, e.g.

    =DATE(2015,3,1)=DATE(2015,2,29)

    I would use tigertiger's suggestion

    =RANDBETWEEN(DATE(2000,1,1),DATE(2015,12,31))

    That version will work correctly, with an even distribution, regardless of your date system or regional settings.
    Audere est facere

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Concatenate text, date and time keeping format
    By TiagoG in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-15-2015, 07:07 PM
  2. [SOLVED] returning date instead of number (format doesn't work)
    By denvdm in forum Excel General
    Replies: 9
    Last Post: 08-17-2012, 10:28 AM
  3. Replies: 4
    Last Post: 04-19-2006, 07:56 AM
  4. Why doesn't my =RANDBETWEEN function work?
    By DanielWalters6 in forum Excel General
    Replies: 4
    Last Post: 12-19-2005, 06:35 AM
  5. [SOLVED] the date format is not working ,sort by date doesn't work.
    By Rosa Campos in forum Excel General
    Replies: 1
    Last Post: 09-12-2005, 06:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1