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

%3CLINGO-SUB%20id%3D%22lingo-sub-2512583%22%20slang%3D%22en-US%22%3EFormatting%20a%20date%20in%20a%20text%20concatenation%20where%20the%20date%20is%20grabbed%20from%20a%20different%20cell.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2512583%22%20slang%3D%22en-US%22%3E%3CP%3ECell%20B7%20contains%20the%20date%20June%2030.%26nbsp%3B%20Column%20B%20is%20hidden.%26nbsp%3B%20In%20cell%20C1%20I%20want%20it%20to%20display%20%22As%20of%20%22%20%26amp%3B%20and%20then%20the%20date%2C%20but%20if%20you%20concatenate%20then%20the%20date%20is%20not%20in%20date%20format.%26nbsp%3B%20I%20know%20how%20to%20do%20this%20in%20Access%20using%20the%20format%20function%20but%20I%20don't%20see%20that%20in%20Excel.%26nbsp%3B%20%26nbsp%3BThe%20reason%20I%20want%20to%20do%20this%20is%20so%20that%20when%20I%20paste%20in%20the%20monthly%20results%20I%20don't%20forget%20to%20change%20the%20As%20of%20at%20the%20top.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2512583%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2512670%22%20slang%3D%22en-US%22%3ERe%3A%20Formatting%20a%20date%20in%20a%20text%20concatenation%20where%20the%20date%20is%20grabbed%20from%20a%20different%20cell.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2512670%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F895008%22%20target%3D%22_blank%22%3E%40leahahuva%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEnter%20this%20formula%20in%20%3CSTRONG%3EC1%3C%2FSTRONG%3E%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%22As%20of%22%26amp%3BTEXT(B7%2C%22m%2Fd%2Fyyyy%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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 Replies

@leahahuva 

Enter this formula in C1:

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

@leahahuva 

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

image.png