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

[nesbie]

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.

 

[Rick Rothstein]

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)

 

[nesbie]

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!

 

[Joe4]

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.

 

[nesbie]

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?!

 

[nesbie]

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

 

[Joe4]

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?

 

[nesbie]

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.

 

[Joe4]

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:

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

 

[Rick Rothstein]

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:

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...

[table="width: 500"]
[tr]
	[td]Sub RandCode()
  MsgBox [TEXT(TODAY(),"\C\Smmdd")&RANDBETWEEN(100000,999999)]
End Sub[/td]
[/tr]
[/table]