combine text with =today function

%3CLINGO-SUB%20id%3D%22lingo-sub-1227989%22%20slang%3D%22en-US%22%3Ecombine%20text%20with%20%3Dtoday%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1227989%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20combine%20the%20text%20string%20'LAST%20OPENED%20'%20with%20the%20%3DTODAY()%20function%20in%20a%20single%20cell.%20What%20is%20the%20proper%20syntax%3F%20I%20have%20tried%20the%20following%20in%20cell%20B1%20which%20is%20formatted%20as%20General%3A%3C%2FP%3E%3CP%3E'LAST%20OPENED%20'%2C%3DTODAY()%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%20for%20any%20help%20I%20can%20get.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1227989%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1228169%22%20slang%3D%22en-US%22%3ERe%3A%20combine%20text%20with%20%3Dtoday%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1228169%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F503634%22%20target%3D%22_blank%22%3E%40Heavy_Duty%3C%2FA%3E%26nbsp%3BI%20believe%20you%20also%20need%20to%20specify%20the%20date%20format%20like%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3D%22LAST%20OPENED%20%22%26amp%3BTEXT(TODAY()%2C%22dd-mm-yyyy%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EWithout%20the%20format%2C%20the%20formula%20returns%20this%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-03-14%20at%2007.16.19.png%22%20style%3D%22width%3A%20237px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F176906i07B9DF855CAE70B9%2Fimage-dimensions%2F237x117%3Fv%3D1.0%22%20width%3D%22237%22%20height%3D%22117%22%20title%3D%22Screenshot%202020-03-14%20at%2007.16.19.png%22%20alt%3D%22Screenshot%202020-03-14%20at%2007.16.19.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1228043%22%20slang%3D%22en-US%22%3ERe%3A%20combine%20text%20with%20%3Dtoday%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1228043%22%20slang%3D%22en-US%22%3E%3D%22LAST%20OPENED%22%26amp%3B%22%20%3A%22%26amp%3B%22%20%22%26amp%3BTEXT(TODAY()%2C%22dd%2Fmm%2Fyyyy%22)%3CBR%20%2F%3E%3CBR%20%2F%3EClick%20Enter...%20That%20will%20return%3CBR%20%2F%3ELAST%20OPENED%3A%2025%2F03%2F2020%3CBR%20%2F%3E%3CBR%20%2F%3E(For%20example)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1228052%22%20slang%3D%22en-US%22%3ERe%3A%20combine%20text%20with%20%3Dtoday%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1228052%22%20slang%3D%22en-US%22%3EYou%20can%20also%20do%3CBR%20%2F%3E%3DCONCATENATE(%22LAST%20OPENED%22%2C%22%3A%22%2C%22%20%22%2CTEXT(TODAY()%2C%22dd%2Fmm%2Fyyyy%22)%3CBR%20%2F%3E%3CBR%20%2F%3EYou%20will%20have%20LAST%20OPENED%3A%2014%2F03%2F2020%3CBR%20%2F%3E%3CBR%20%2F%3EPay%20close%20attention%20to%20the%20double%20quotations...%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1228574%22%20slang%3D%22en-US%22%3ERe%3A%20combine%20text%20with%20%3Dtoday%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1228574%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F503634%22%20target%3D%22_blank%22%3E%40Heavy_Duty%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOr%20just%20apply%20custom%20number%20format%20to%20the%20cell%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20391px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F176967iC84D3FBDD7AF3527%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2132483%22%20slang%3D%22en-US%22%3ERe%3A%20combine%20text%20with%20%3Dtoday%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2132483%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F503634%22%20target%3D%22_blank%22%3E%40Heavy_Duty%3C%2FA%3E%26nbsp%3B%20this%20not%20working%20in%20Excel365%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I want to combine the text string 'LAST OPENED ' with the =TODAY() function in a single cell. What is the proper syntax? I have tried the following in cell B1 which is formatted as General:

'LAST OPENED ',=TODAY()

 

Thanks in advance for any help I can get.

5 Replies
="LAST OPENED"&" :"&" "&TEXT(TODAY(),"dd/mm/yyyy")

Click Enter... That will return
LAST OPENED: 25/03/2020

(For example)
You can also do
=CONCATENATE("LAST OPENED",":"," ",TEXT(TODAY(),"dd/mm/yyyy")

You will have LAST OPENED: 14/03/2020

Pay close attention to the double quotations...

@Heavy_Duty I believe you also need to specify the date format like this:

="LAST OPENED "&TEXT(TODAY(),"dd-mm-yyyy")

Without the format, the formula returns this:

Screenshot 2020-03-14 at 07.16.19.png

@Heavy_Duty 

Or just apply custom number format to the cell

image.png

@Heavy_Duty  this not working in Excel365