Excel formatting in a mixed cell

%3CLINGO-SUB%20id%3D%22lingo-sub-1540810%22%20slang%3D%22en-US%22%3EExcel%20formatting%20in%20a%20mixed%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1540810%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI'm%20trying%20to%20set%20up%20a%20cell%20to%20display%20%22on%20mm%2Fdd%2Fyy%22%2C%20where%20the%20date%20is%20pulled%20from%20another%20cell%20via%20an%20index%20and%20match%20function.%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20cell%20formula%20is%26nbsp%3B%20%3D%22on%20%22%26amp%3BINDEX(...)).%20However%2C%20instead%20of%20showing%20%22on%207%2F22%2F20%22%2C%20the%20result%20returns%20the%20numeric%20equivalent%20of%20the%20date%20-%20for%20example%2C%20%22on%2044034%22.%26nbsp%3B%20I%20can't%20figure%20out%20how%20to%20get%20it%20to%20display%20the%20date%20format%20within%20this%20mixed%20cell%20content.%26nbsp%3B%20Can%20anyone%20help%3F%3C%2FP%3E%3CP%3ESincerely%2C%3C%2FP%3E%3CP%3EJQuinn%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1540810%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1540819%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formatting%20in%20a%20mixed%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1540819%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F737597%22%20target%3D%22_blank%22%3E%40JQuinn%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAssuming%20your%20date%20is%20in%20cell%20A1%2C%20use%20this%20%3CSTRONG%3E%3D%22on%20%22%26amp%3BTEXT(A1%2C%22m%2Fd%2Fy%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBecause%20what%20you're%20doing%20is%20creating%20a%20text%20entry%2C%20you%20need%20to%20turn%20the%20date%20into%20a%20text%20and%20format%20it.%20The%20%3CSTRONG%3ETEXT%3C%2FSTRONG%3E%20function%20takes%20care%20of%20that%2C%20and%20one%20of%20the%20arguments%20is%20the%20way%20you%20want%20it%20displayed.%20The%20same%20sort%20of%20thing%20would%20apply%20if%20you%20were%20dealing%2C%20say%2C%20with%20financial%20numbers%20and%20wanted%20it%20to%20be%20in%20dollars%20and%20cents.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1541028%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formatting%20in%20a%20mixed%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1541028%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThank%20you.%26nbsp%3B%20That%20works.%26nbsp%3B%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CDIV%3EI%20just%20need%20to%20figure%20out%20how%20to%20merge%20it%20with%20the%20Index-Match%20formula%2C%20so%20I%20don%3Bt%20need%20to%20point%20to%20an%20alternate%20cell%20for%20the%20date.%3C%2FDIV%3E%3CDIV%3EThanks%2C%20again%2C%3C%2FDIV%3E%3CDIV%3EJQuinn%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1541115%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formatting%20in%20a%20mixed%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1541115%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F737597%22%20target%3D%22_blank%22%3E%40JQuinn%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20long%20as%20your%20spreadsheet%20doesn't%20contain%20private%20or%20confidential%20info%2C%20you%20can%20post%20it%20here%20so%20we%20can%20work%20with%20the%20actual%20situation%20rather%20than%20having%20to%20reconstruct%20it%20from%20your%20description.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20%3CEM%3Ethink%3C%2FEM%3E%20that%20%22all%20you'd%20need%20to%20do%22%20is%20nest%20the%20INDEX%2FMATCH%20function%20within%20that%20TEXT%20function%2C%20assuming%20that%20what%20the%20INDEX%2FMATCH%20is%20doing%20is%20getting%20the%20date%20in%20question.%20It's%20hard%20to%20be%20certain%20without%20seeing%20more%20directly%20what%20you're%20working%20with.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1541123%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formatting%20in%20a%20mixed%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1541123%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F737597%22%20target%3D%22_blank%22%3E%40JQuinn%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAlternatively%20you%20may%20apply%20to%20the%20cell%20into%20which%20INDEX%2FMATCH%20returns%20the%20date%20custom%20format%20like%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%20378px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F207437i284EAC6E169A72C9%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%0A%3CP%3EPlus%20is%20that%20it%20still%20will%20be%20a%20date%2C%20not%20text%2C%20with%20which%20you%20may%20work%20-%20compare%20with%20our%20dates%2C%20whatever.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1541502%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formatting%20in%20a%20mixed%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1541502%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%2C%20Sergei.%3C%2FP%3E%3CP%3EThat%20would%20be%20an%20elegant%20solution.%26nbsp%3B%20However%2C%20I%20can't%20seem%20to%20apply%20that%20format%20to%20the%20cell%20and%20get%20the%20intended%20response.%20It%20still%20displays%20as%20'on%2044034'.%26nbsp%3B%20(See%20capture%20image.)%3C%2FP%3E%3CP%3EDo%20you%20know%20if%20that%20formatting%20is%20restricted%20to%20certain%20versions%20of%20Excel%3F%20I'm%20using%20Excel%202016.%3C%2FP%3E%3CP%3EJQuinn%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1541549%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formatting%20in%20a%20mixed%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1541549%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20for%20the%20offer.%26nbsp%3B%20However%2C%20there's%20a%20ton%20of%20confidential%20info%20in%20that%20file%2C%20some%20of%20which%20is%20integral%20to%20the%20that%20cell.%26nbsp%3B%20For%20now%2C%20I'll%20just%20generate%20the%20Index%2FMatch%20date%20elsewhere%20and%20refer%20to%20it%20in%20the%20mixed%20content%20cell%2C%20using%20your%20suggested%20approach.%3C%2FP%3E%3CP%3EGratefully%2C%3C%2FP%3E%3CP%3EJQuinn%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1544553%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formatting%20in%20a%20mixed%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1544553%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F737597%22%20target%3D%22_blank%22%3E%40JQuinn%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20general%20it%20shall%20work.%20If%20I%20apply%20custom%20format%20to%20such%20number%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%20390px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F207709i990BF3DAADC84760%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%0A%3CP%3Eresult%20is%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%20135px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F207710i54F5281E46D989F7%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%0A%3CP%3EIf%20you%20apply%20simple%20date%20format%20to%20the%20value%20returned%20by%20formula%2C%20is%20it%20converted%20to%20date%20form%3F%20Hard%20to%20say%20what's%20wrong%20without%20sample%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello,

I'm trying to set up a cell to display "on mm/dd/yy", where the date is pulled from another cell via an index and match function. 

The cell formula is  ="on "&INDEX(...)). However, instead of showing "on 7/22/20", the result returns the numeric equivalent of the date - for example, "on 44034".  I can't figure out how to get it to display the date format within this mixed cell content.  Can anyone help?

Sincerely,

JQuinn

7 Replies
Highlighted

@JQuinn 

 

Assuming your date is in cell A1, use this ="on "&TEXT(A1,"m/d/y")

 

Because what you're doing is creating a text entry, you need to turn the date into a text and format it. The TEXT function takes care of that, and one of the arguments is the way you want it displayed. The same sort of thing would apply if you were dealing, say, with financial numbers and wanted it to be in dollars and cents.

Highlighted

@mathetes 

 

Thank you.  That works.  

I just need to figure out how to merge it with the Index-Match formula, so I don;t need to point to an alternate cell for the date.
Thanks, again,
JQuinn
Highlighted

@JQuinn 

 

So long as your spreadsheet doesn't contain private or confidential info, you can post it here so we can work with the actual situation rather than having to reconstruct it from your description.

 

I think that "all you'd need to do" is nest the INDEX/MATCH function within that TEXT function, assuming that what the INDEX/MATCH is doing is getting the date in question. It's hard to be certain without seeing more directly what you're working with.

Highlighted

@JQuinn 

Alternatively you may apply to the cell into which INDEX/MATCH returns the date custom format like

image.png

Plus is that it still will be a date, not text, with which you may work - compare with our dates, whatever.

Highlighted

Thank you, Sergei.

That would be an elegant solution.  However, I can't seem to apply that format to the cell and get the intended response. It still displays as 'on 44034'.  (See capture image.)

Do you know if that formatting is restricted to certain versions of Excel? I'm using Excel 2016.

JQuinn

 

 

@Sergei Baklan 

Highlighted

Thank you for the offer.  However, there's a ton of confidential info in that file, some of which is integral to the that cell.  For now, I'll just generate the Index/Match date elsewhere and refer to it in the mixed content cell, using your suggested approach.

Gratefully,

JQuinn

 

@mathetes 

Highlighted

@JQuinn 

In general it shall work. If I apply custom format to such number

image.png

result is

image.png

If you apply simple date format to the value returned by formula, is it converted to date form? Hard to say what's wrong without sample file.