Combine numbers and date to cell

Or if A1 or A2 might have a leading zero:

Formula:

Please Login or Register  to view this content.

You will probably need to change the commas to semi-colons.

@Rick: thank you. I did not know you could do that. Is that documented somewhere?

@Rick: yes. It worked for me. It also worked with TODAY() but obviously NOW() wins in the brevity stakes. Thanks again.

Surely =YEAR(NOW()) would win in the brevity stakes??? Are there any other such metacharacters? Always interesting to find out such things...

I was also about to ask for documentation on this as I've never seen it before.

As far as I can tell:
b = unclear? looks like some year counter with 1957/2057 = 00, 1956/2056 = 99 etc. ¯\_(ツ)_/¯
d = day
e = 4-digit year
g = unclear? (gives me an empty cell but with some character in it)
h = hour
m = month
n = unclear (gives me a #VALUE! error)
s = seconds
y = 2-digit year

Just to follow up on things you can do with the TEXT function that many are not aware of... assuming you have a newer version of Excel that supports dynamic arrays, you can output different formatted patterns (number and/or date) to different columns for a range of numbers or dates with just a single formula. Let's assume you have a range of dates in cells A2:A9 and you wanted the month name in one column, the day number along with the day name in the next column and the international formatted date in the next column after that. You can supply the different format patterns in an array constant and the TEXT function will apply them accordingly. For the example I proposed, this single formula would output all three columns of formatted results for the entire range...

Formula:

Please Login or Register  to view this content.

Just for fun, I’ve put all the solutions and advice into a workbook.

It checks repetitions of the formatting characters. Interestingly, "b" and "bb" give one value, "bbb" and "bbbb" give a longer value. A single "a" just returns "a", and "aa" returns "aa". "aaa", "aaaa", and "aaaaa" return variations of the Day; "Mon" and "Monday". "mmmmm" (5*"m") returns the first letter of the month.

I had the same experience with multiples of "g" and "n"; blank and #Value! respectively.

@RaulSerg: I don't think that's a coincidence (regarding the "b" options) . . , 2 digit and 4 digit Thai Solar year. Who'd a thought?

I did also consider the options for other countries and whether or not capitalisation would make a difference . . . but that felt like a couple of steps too far.

Edit: quick check of capitalisation indicates that it must be a lower case "e". Upper case "E" returns #VALUE! Other than that, there doesn't seem to be a difference.