Excel – Concatenated cells return random number instead of date

concatenationdatemicrosoft excelworksheet-function

I have a question about an excel formula.
I have a some fields I want to concatenate together. One of those is a date made with the formula TODAY.
When I concatenate all together it will give me a strange number in the place of the data from TODAY formula.

here the screenshot

As you can see in the screenshot the second raw contains data and the formulas used (without =) while the third raw contains the final url. The ad set value contains TODAY formula and it is formatted as date cell, but in the final url it takes it like a random number 42978. How can I fix this?
Thank you

Best Answer

Instead of concatenating "J13" (or whichever cell contains your date) in the final formula, concatenate TEXT(J13, "yyyy/m/d").

So your formula would become (spaces added for readability)

(...) & I13 & TEXT(J13, "yyyy/m/d") & K13 & L13

Of course, you can change the format "yyyy/m/d" if needed.