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 = 1but i need it to show like YYYY-MM-DD-ID
B3 = 2019-01-30-01
YYYY-MM-DD-ID
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
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
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
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)
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