 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / February 2007different formatting, same cell
Say the date that a certain event occurs (2/17/07) is in cell A1. I would like to have cell B1 display: Completed 2/17/07 If I use the formula ="Completed "&A1, it displays: Completed 39130 Is there another way to do this? To Excel, dates are just numbers, so you need to tell it to convert the number (date) to text how to display that text. Try something like this: ="Completed "&TEXT(A1,"MM/DD/YYYY") Does that help? *********** Regards, Ron XL2002, WinXP > Say the date that a certain event occurs (2/17/07) is in cell A1. > I would like to have cell B1 display: Completed 2/17/07 > If I use the formula ="Completed "&A1, it displays: Completed 39130 > Is there another way to do this? On Feb 17, 4:53 pm, Ron Coderre <ronREMOVETHIScode...@bigfoot.com> wrote: > To Excel, dates are just numbers, so you need to tell it to convert the > number (date) to text how to display that text. [quoted text clipped - 13 lines] > > If I use the formula ="Completed "&A1, it displays: Completed 39130 > > Is there another way to do this? Exactly! Thanks! Matt ="Completed " &TEXT(A1,"mmmm, d yyyy") Your choice of date format. Gord Dibben MS Excel MVP >Say the date that a certain event occurs (2/17/07) is in cell A1. >I would like to have cell B1 display: Completed 2/17/07 >If I use the formula ="Completed "&A1, it displays: Completed 39130 >Is there another way to do this? Matt, You can make A1 say "Completed 2/17/07". With A1 selected, Format - Cells - Number tab - Custom. In the box, put: "Completed" mm/dd/yy Include the quote marks. Or you can make B1 do that with =A1, and the same formatting for B1.  SignatureEarl Kiosterud www.smokeylake.com ----------------------------------------------------------------------- > Say the date that a certain event occurs (2/17/07) is in cell A1. > I would like to have cell B1 display: Completed 2/17/07 > If I use the formula ="Completed "&A1, it displays: Completed 39130 > Is there another way to do this? Trouble with that Earl, is that empty cells will display: Completed 01/00/00 So, the =A1 formula should become something like this: =IF(A1>0,A1,"") OR ... to retain the =A1 formula, the custom format should be something like: [>0]"Completed "mm/dd/yy;General; With the last semi-colon included to eliminate 0's when empty cells are in the first column. SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > Matt, > [quoted text clipped - 10 lines] > > If I use the formula ="Completed "&A1, it displays: Completed 39130 > > Is there another way to do this? RD, Oops. You're right. Similar to your second solution, you could use "Completed" mm/dd/yy;; SignatureEarl Kiosterud www.smokeylake.com ----------------------------------------------------------------------- > Trouble with that Earl, is that empty cells will display: > Completed 01/00/00 [quoted text clipped - 28 lines] >> > If I use the formula ="Completed "&A1, it displays: Completed 39130 >> > Is there another way to do this? I like that one even better!<g> SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > RD, > [quoted text clipped - 39 lines] > >> > If I use the formula ="Completed "&A1, it displays: Completed 39130 > >> > Is there another way to do this? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.