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

nesbie

New Member
Joined
Apr 6, 2018
Messages
6
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.
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,666
Office Version
2010
Platform
Windows
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

New Member
Joined
Apr 6, 2018
Messages
6
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,199
Office Version
365
Platform
Windows
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:

nesbie

New Member
Joined
Apr 6, 2018
Messages
6
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

New Member
Joined
Apr 6, 2018
Messages
6
You're 100% correct - how can I go about locking it manually (assuming this will be the fastest route vs VBA)
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,199
Office Version
365
Platform
Windows
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

New Member
Joined
Apr 6, 2018
Messages
6
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,199
Office Version
365
Platform
Windows
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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,666
Office Version
2010
Platform
Windows
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:
[table="width: 500"]
[tr]
	[td]Sub RandCode()
  MsgBox [TEXT(TODAY(),"\C\Smmdd")&RANDBETWEEN(100000,999999)]
End Sub[/td]
[/tr]
[/table]
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,090,409
Messages
5,414,236
Members
403,521
Latest member
Mattlake

This Week's Hot Topics

Top