How to concatenate date value with a string?

Question

I need to create the following value:

// "ABC" + current date + some random number
ABC20160405123

I do as follows:

=CONCATENATE("ABC", NOW(), 123);

But instead of getting expected value I get something weird in date segment:

ABC42465.889227419123

How can I get pure date in concatenate()?

Answer 1

You need to convert the date value to a string of the form yyyymmdd. The text formula does this:

=text(today(), "yyyymmdd")

(It's better to use today() instead of now() when you want the date only.)

The entire formula would be

=concatenate("ABC", text(today(), "yyyymmdd"), 123)

or, more concisely,

="ABC" & text(today(), "yyyymmdd") & 123

Answer 2

You can eliminate the concatenate function altogether and join them using the & and transforming the data to a text format:

="ABC"&text(TODAY(),"yymmdd")&"123"