Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: a little odd: Need to convert TODAY() date to two digits 0000 and use 2 letters + 6 random number formula

  1. #1
    New Member
    Join Date
    Apr 2018
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default a little odd: Need to convert TODAY() date to two digits 0000 and use 2 letters + 6 random number formula

    Hello,

    I'm breaking my head here, I understand a little =Tex which changes my TODAY() cell in another cell to 0406. Now I would like to add initials at the front like CS0406 followed by 6 random numbers. CS0406123456- It's killing me, can someone help?

    It doesn't have to pull the date from the =TODAY() cell, if there's a formula that can do all of the above, that would rock.

    Can someone please guide me as I get stuck using TEXT/RANDOM etc.

    Been searching for hours.

    Chris.

  2. #2
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,903
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: a little odd: Need to convert TODAY() date to two digits 0000 and use 2 letters + 6 random number formula

    Quote Originally Posted by nesbie View Post
    Hello,

    I'm breaking my head here, I understand a little =Tex which changes my TODAY() cell in another cell to 0406. Now I would like to add initials at the front like CS0406 followed by 6 random numbers. CS0406123456- It's killing me, can someone help?

    It doesn't have to pull the date from the =TODAY() cell, if there's a formula that can do all of the above, that would rock.
    I think this does what you want...

    =TEXT(TODAY(),"\C\Smmdd")&RANDBETWEEN(100000,999999)
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  3. #3
    New Member
    Join Date
    Apr 2018
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: a little odd: Need to convert TODAY() date to two digits 0000 and use 2 letters + 6 random number formula

    OMG! YESSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSS!!!!!!!!!!!!!!!!!!!!!!

    Let me buy you a drink one day that you're in LA!!! Seriously! I hate how I spent hours trying to find out a solution, not knowing if I should go with TEXT, RANDOM etc and you figured it out quickly!!

    I'm truly thankful sir!

    Was worried when I clicked save if the same numbers would open and everytime new numbers appear (hence randbetween) thank you!

  4. #4
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    47,922
    Post Thanks / Like
    Mentioned
    40 Post(s)
    Tagged
    8 Thread(s)

    Default Re: a little odd: Need to convert TODAY() date to two digits 0000 and use 2 letters + 6 random number formula

    I think this does what you want...

    =TEXT(TODAY(),"\C\Smmdd")&RANDBETWEEN(100000,999999)
    The only issue is that that the value will keep changing with every calculation of the sheet (because RANDBETWEEN will keep re-evaluating).
    If you need to "lock it" at a certain value doesn't change, you will need to convert the formula to a hard-coded value, either manually or by VBA.
    I guess it depends on how they are using it, if that is going to be a problem or not.
    Last edited by Joe4; Apr 6th, 2018 at 04:00 PM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    New Member
    Join Date
    Apr 2018
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: a little odd: Need to convert TODAY() date to two digits 0000 and use 2 letters + 6 random number formula

    Quote Originally Posted by Joe4 View Post
    The only issue is that that the value will keep changing with every calculation of the sheet (because RANDBETWEEN will keep re-evaluating).
    If you need to "lock it" at a certain value doesn't change, you will need to convert the formula to a hard-coded value, either manually or by VBA.
    I guess it depends on how they are using it, if that is going to be a problem or not.

    It's working fine but any changes I make on a different cell, it generates a new number! UGH. I thought it was just when I closed and re-open it gave a new number but it's at anytime while it's open... how can I change this?!

  6. #6
    New Member
    Join Date
    Apr 2018
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: a little odd: Need to convert TODAY() date to two digits 0000 and use 2 letters + 6 random number formula

    You're 100% correct - how can I go about locking it manually (assuming this will be the fastest route vs VBA)

  7. #7
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    47,922
    Post Thanks / Like
    Mentioned
    40 Post(s)
    Tagged
    8 Thread(s)

    Default Re: a little odd: Need to convert TODAY() date to two digits 0000 and use 2 letters + 6 random number formula

    You're 100% correct - how can I go about locking it manually (assuming this will be the fastest route vs VBA)
    Copy -> Paste Special Values would do it.
    If VBA, I would recommend doing the whole calculation in VBA. The question is, when you run this VBA code, where do you want it to place the value?
    What is the logic of that?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  8. #8
    New Member
    Join Date
    Apr 2018
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: a little odd: Need to convert TODAY() date to two digits 0000 and use 2 letters + 6 random number formula

    I just have a simple form, I'm have a field for user/address/number (this is where the code sits) and it repeats the same thing on 6 rows. When I open the log or make any changes, the CS0406000000 changes, which sits on the tab 'number' - not sure if this makes sense or answers your question. If I had a VBA code to put the 'numbers' row, that would solve my problem.

  9. #9
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    47,922
    Post Thanks / Like
    Mentioned
    40 Post(s)
    Tagged
    8 Thread(s)

    Default Re: a little odd: Need to convert TODAY() date to two digits 0000 and use 2 letters + 6 random number formula

    Here is some VBA code that calculates the value you want. I have it returning to a MsgBox, but you can have it put the value wherever you need it:
    Code:
    Sub RandNum()
    
        Dim Low As Double
        Dim High As Double
        Dim R As Double
        Dim ID As String
        
        Low = 100000
        High = 999999
        R = Int((High - Low + 1) * Rnd() + Low)
        
        ID = Format(Date, "\C\Smmdd") & R
        
        MsgBox ID
        
    End Sub
    Got a little help from here: http://www.cpearson.com/excel/randomnumbers.aspx
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  10. #10
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,903
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: a little odd: Need to convert TODAY() date to two digits 0000 and use 2 letters + 6 random number formula

    Quote Originally Posted by Joe4 View Post
    Here is some VBA code that calculates the value you want. I have it returning to a MsgBox, but you can have it put the value wherever you need it:
    Code:
    Sub RandNum()
    
        Dim Low As Double
        Dim High As Double
        Dim R As Double
        Dim ID As String
        
        Low = 100000
        High = 999999
        R = Int((High - Low + 1) * Rnd() + Low)
        
        ID = Format(Date, "\C\Smmdd") & R
        
        MsgBox ID
        
    End Sub
    Got a little help from here: http://www.cpearson.com/excel/randomnumbers.aspx
    Alternately, this one-liner will do the same thing as the code you posted...
    Code:
    Sub RandCode() MsgBox [TEXT(TODAY(),"\C\Smmdd")&RANDBETWEEN(100000,999999)] End Sub
    Last edited by Rick Rothstein; Apr 6th, 2018 at 05:35 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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