Formatting a date in a text concatenation where the date is grabbed from a different cell.

[leahahuva]

Cell B7 contains the date June 30.  Column B is hidden.  In cell C1 I want it to display "As of " & and then the date, but if you concatenate then the date is not in date format.  I know how to do this in Access using the format function but I don't see that in Excel.   The reason I want to do this is so that when I paste in the monthly results I don't forget to change the As of at the top.

[#2]

@leahahuva 

Enter this formula in C1:

="As of"&TEXT(B7,"m/d/yyyy")

[#3]

@leahahuva 

Alternatively in C1 you may use =B7 and apply to C1 (Ctrl+1) custom number format as