How to Combine Today() and If Statement in unique ID

April 11, 2019 at 00:05:48
Specs: Windows 10
In excel I am trying to combine the date today and my unique ID.
on where:

B2 = 0
B3 Fomula is =IF(ISBLANK(I3),"",B2+1)
B3 = 1

but i need it to show like YYYY-MM-DD-ID
B3 = 2019-01-30-01
YYYY-MM-DD-ID



See More: How to Combine Today() and If Statement in unique ID

Reply ↓  Report •

#1
April 11, 2019 at 13:09:24
OK, I'm officially confused.

Where is this ID coming from? Is that the result of B2+1?

If so, this might work:

=IF(ISBLANK(I3),"",YEAR(TODAY())&"-"&MONTH(TODAY())&"-"&DAY(TODAY())&"-"&IF(LEN(B2+1)=1,"0"&B2+1,B2+1))

However, the TODAY() function (which you asked for) is going to be updated every day and therefore the value in B3 is going to change every day.

If you want to lock in the date that the TODAY() function creates, it can be done (safely) with VBA and (dangerously) by enabling Circular References and changing default Iteration value to 1. The CR/Iteration method is not for the faint of heart.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
April 11, 2019 at 21:02:23
Thank you for that @DerbyDad03, the ID is starts with just a number 0 from the first Row,

but it has a missing Value. It works at the first row, but when i continue itto the following rows, it shows "#Value"

Error = "Function ADD parameter 1 expects number values. But '2019-4-12-2' is a text and cannot be coerced to a number."

message edited by AriesDiaz


Reply ↓  Report •

#3
April 12, 2019 at 05:19:00
I'm a little confused by your use of the words "first row". Nothing in your OP mentions Row 1, so I assume (which I hate to do) that by "first row" you mean the first row that contains the formula, i.e. Row 3. When posting in a help forum such as this, it's best to be as specific as possible. We can't see your worksheet from where we are sitting and can only help based on the words that you use.

Try the following formula. Notice the use of $B$2 instead of just B2. The dollar signs lock the reference to B2 instead of incrementing as you drag the formula down. As written, I3 will still increment, so you'll need to fix that also, if required.

=IF(ISBLANK(I3),"",YEAR(TODAY())&"-"&MONTH(TODAY())&"-"&DAY(TODAY())&"-"&IF(LEN($B$2+1)=1,"0"&$B$2+1,$B$2+1))

See here for more information related to Absolute vs. Relative references:

https://support.office.com/en-us/ar...


message edited by DerbyDad03


Reply ↓  Report •

Related Solutions

#4
April 15, 2019 at 08:56:02
sorrry about the confusion, here I will show you in figures

http://i66.tinypic.com/1znud08.png

but if its posibble to be more like this:

http://i67.tinypic.com/14abeas.png

C2019 = C+Year
04 = Month
11 and 12 = Day
01 and 02 = count of (it reset to "01" every day)


Reply ↓  Report •

#5
April 15, 2019 at 10:22:00
Now you've added a "C" at the beginning? If you keep changing your requirements, we'll never get this solved.

Since I have no idea what your work process is, there is not much more that I can do. If you can provide more specifics as to how and when these strings are supposed to be created, maybe we can help more. As of now, we don't have enough details.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

Ask Question