In stead of J3, use Format(j3,"yyyy-mm-dd") or whatever date format you prefer. That's the way to transfer an integer (representing the date as the number of days since 1901-01-01 to a string that can be concatenated as any other string.
I am trying to use Excel's Concatenate expression to mix words, a cell reference that holds a MAX figure, and a cell reference that holds a date.
The formula I am using is;
=CONCATENATE("Max= ",MAX(C6:C750),"p"," on ",J3)
C6:C750 holds a series of amounts. The "p" is pence, or it would be cents in the US. J3 holds the date when the MAX was achieved.
This almost works, but it only gives me Max= 202.2p on 38735
The last figure, 38735 is the date in non-date format, and I cannot get it to show in date format, eg 1/8/07, (8th Jan). Using Format > Numbers on the cell, then "Date" does not change it to a date.
Is there any way around this?
All suggestions gratefully received.