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

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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)
 
Upvote 0
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!
 
Upvote 0
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:
Upvote 0
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?!
 
Upvote 0
You're 100% correct - how can I go about locking it manually (assuming this will be the fastest route vs VBA)
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top