different 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?

0
MLThornton (28)
2/17/2007 10:42:57 PM
excel 39879 articles. 2 followers. Follow

7 Replies
789 Views

Similar Articles

[PageSpeed] 10

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


"Matt" wrote:

> 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?
> 
> 
0
2/17/2007 10:53:03 PM
Matt

="Completed " &TEXT(A1,"mmmm, d  yyyy")

Your choice of date format.


Gord Dibben  MS Excel MVP


On 17 Feb 2007 14:42:57 -0800, "Matt" <MLThornton@gmail.com> wrote:

>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?

0
Gord
2/17/2007 10:56:35 PM
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.
>
> Try something like this:
> ="Completed "&TEXT(A1,"MM/DD/YYYY")
>
> Does that help?
> ***********
> Regards,
> Ron
>
> XL2002, WinXP
>
> "Matt" wrote:
> > 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?



Exactly!  Thanks!

0
MLThornton (28)
2/18/2007 1:08:45 AM
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.

-- 
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Matt" <MLThornton@gmail.com> wrote in message 
news:1171752177.933067.205800@k78g2000cwa.googlegroups.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?
> 


0
someone798 (944)
2/18/2007 2:14:34 AM
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.
-- 
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Earl Kiosterud" <someone@nowhere.com> wrote in message
news:Ors5SKwUHHA.5060@TK2MSFTNGP06.phx.gbl...
> 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.
>
> -- 
> Earl Kiosterud
> www.smokeylake.com
> -----------------------------------------------------------------------
> "Matt" <MLThornton@gmail.com> wrote in message
> news:1171752177.933067.205800@k78g2000cwa.googlegroups.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?
> >
>
>

0
ragdyer1 (4060)
2/18/2007 2:53:15 AM
RD,

Oops.  You're right.  Similar to your second solution, you could use

"Completed" mm/dd/yy;;

-- 
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Ragdyer" <RagDyer@cutoutmsn.com> wrote in message 
news:%23UzayfwUHHA.4188@TK2MSFTNGP06.phx.gbl...
> 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.
> -- 
> Regards,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "Earl Kiosterud" <someone@nowhere.com> wrote in message
> news:Ors5SKwUHHA.5060@TK2MSFTNGP06.phx.gbl...
>> 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.
>>
>> -- 
>> Earl Kiosterud
>> www.smokeylake.com
>> -----------------------------------------------------------------------
>> "Matt" <MLThornton@gmail.com> wrote in message
>> news:1171752177.933067.205800@k78g2000cwa.googlegroups.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?
>> >
>>
>>
> 


0
someone798 (944)
2/18/2007 4:04:27 AM
I like that one even better!<g>
-- 
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Earl Kiosterud" <someone@nowhere.com> wrote in message
news:OUTysHxUHHA.3500@TK2MSFTNGP05.phx.gbl...
> RD,
>
> Oops.  You're right.  Similar to your second solution, you could use
>
> "Completed" mm/dd/yy;;
>
> -- 
> Earl Kiosterud
> www.smokeylake.com
> -----------------------------------------------------------------------
> "Ragdyer" <RagDyer@cutoutmsn.com> wrote in message
> news:%23UzayfwUHHA.4188@TK2MSFTNGP06.phx.gbl...
> > 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.
> > -- 
> > Regards,
> >
> > RD
> >
>
> --------------------------------------------------------------------------
-
> > Please keep all correspondence within the NewsGroup, so all may benefit
!
>
> --------------------------------------------------------------------------
-
> > "Earl Kiosterud" <someone@nowhere.com> wrote in message
> > news:Ors5SKwUHHA.5060@TK2MSFTNGP06.phx.gbl...
> >> 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.
> >>
> >> -- 
> >> Earl Kiosterud
> >> www.smokeylake.com
> >> -----------------------------------------------------------------------
> >> "Matt" <MLThornton@gmail.com> wrote in message
> >> news:1171752177.933067.205800@k78g2000cwa.googlegroups.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?
> >> >
> >>
> >>
> >
>
>

0
ragdyer1 (4060)
2/18/2007 5:50:17 AM
Reply:

Similar Artilces:

convert Date formate (windows default) to Arabic
Dear all, any one have an idea about how i can convert the Date from gorgian Date formate (windows default) to Arabic Um AL Qura format (Hijiri) ? -- MBS Developer Abdelrahman Nabil ...

be able to print indivually different labels on multiple label sh.
hi i want to be able to use publisher to print individual labels on a multiple label sheet, any ideas regards greg Do you mean different addresses on the labels or be able to print one label as needed and tell it which label you want to use? (I do the latter in Word.) -- JoAnn Paules MVP Microsoft [Publisher] "Greg" <Greg@discussions.microsoft.com> wrote in message news:740CB634-47E8-4EEC-BE75-768932495A30@microsoft.com... > hi > i want to be able to use publisher to print individual labels on a > multiple > label sheet, > any ideas > regards > g...

Primary Axis(X) Formating Issue
I'm using a custom type chart (Lines on 2 Axes) and I'm using two different ranges for the Category (X) axis labels and the Second category (X) axis labels in order to display some additional information on the chart. When I say two different ranges I mean the rows are the same, but the columns are different. The problem I'm having is I want to use a custom format (dd-MMM-yy) to display the dates, but when I do so the dates have format MMM-yy-dd. The only way I can get the dates to display accurately is to use the Date format with the * (either *03/14/01 or *March 14, 20...

how do i change the color of a cell auotmatically?
I am desiging a data shee and i want to changes the color of a cell based on the valuse of anthor cell. ex. if "cell m34" is equal to 0 then "cell p21" is white, if cell m34 less than 1548, then "cell P21" is yellow Can this be done? If so, please help me out. Hi! Select cell P21 Goto Format>Conditional Formatting In the dropdown select Formula is Enter this formula in the box: =AND(M34<>"",M34=0) Click the Format button and select the style to apply then click OK. Click the Add button In Condition 2 Formula is: =AND(M34>=1,M34<=154...

Is it possible to have text and currancy in one cell and ...
Is it possible to have text and currancy in one cell and be able to use the numbers in that cell to make an equation with another? The short answer .. No -- Rgds Max xl 97 --- Please respond in thread xdemechanik <at>yahoo<dot>com ---- "thegen" <thegen@discussions.microsoft.com> wrote in message news:1552FF10-9CAA-42AD-B401-B0D229D6A343@microsoft.com... > Is it possible to have text and currancy in one cell and be able to use the > numbers in that cell to make an equation with another? well maybe depending on how the "text & currency" was ar...

How to skip cells with text strings.
What should I add to the code below to cause it to skip a cell tha contains a text string in the cell above it? I need it to only work o numbers in the cell above it. If ActiveCell.Offset(rowoffset:=-1) <> "" Then ActiveCell = ActiveCell.Offset(rowoffset:=-1) + 1 Application.SendKeys ("{Enter}") End If Thanks so much. mikebur -- mikebur ----------------------------------------------------------------------- mikeburg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2458 View this thread: http://www.excelforum.com/showthread.php?thread...

Diffrent cell on diffrent date?
I�m going to TRY and explain this the best I can. For work when we do our cash out at the end of the night we use a exce sheet. This sheet is to add up our change, checks, credit cards etc etc. etc. When we're done we print it out and than type the figure into another excel sheet. I pretty much know how to combine the sheets so instead of typing the numbers into SS1 and than entering i into SS2 I can have the numbers going from SS1 to SS2 automatically But my question is that how do I set it up so when I type the info i SS1 that it will go to in to SS2 but to have the cells change by dat...

Excel Formula Guidance. Formula need to determine if cell is popul
Hi, Hopefully someone will be able to provide some guidance. I have been tasked with setting up some spreadsheets at work to application coming into my department and the number of days it takes for us to deal with them. To work this out is easy enough using formula =NETWORKDAYS(B4,E4,$A$119:$A$158) with the numbers involved with the $ referencing bank holidays listed in the spreadsheet and B4 received date and E4 being the completed date. I now need to include some extra lines as I need to be able to monitor written enquiries as well so the headings would read Application ...

Segrigation of different datas from one colum
Hi , I am trying to segrigate and pull data from one colum to different colums. For this I want to created a macro . Also I want this data to be pulled from a word document and to be pasted on an Excel template.Is it possible ??then How can we do it . Hi Roy- Providing a sample of the type of data you are working with (whatever you want to segregate, also called "parsing") would be helpful in order to provide you more direction. You can definitely extract data from a Word document, but how efficient it is will vary depending on how your Word document was set up...

very custom formats
I need help with a custom format If I enter the numbers 123 I want it to display 12-3 in the cell. (i.e simple custom format ##-# But if I also want to be able to enter 123A into the same cell and have it display 12-3A (where A could be any letter A-Z).. What would my custom format need to look like in order to do that Or is there a better approach? Not sure you can do what you want because the alphabetic character means the value is no longer numeric, hence a custom number format won't work Regards Trevor "mrdigithead" <anonymous@discussions.microsoft.com> wrote i...

Coping numbers to next blank cell on another worksheet
I am just learning Excel (need SIMPLE help) but any help would be greatly appreciated. My OS is XL Professional and I have MS Excel 2002 SP2. I need to copy calculated numbers (I got this much done on my own) in specific cells on worksheet # 1 to the next blank cells in existing columns of numbers used in calculations on other worksheets in the same workbook. I other words, I have a formula that calculates numbers and places the results in the same column and row each time it is used. Those calculated numbers need to be copied and used on different worksheets in the same workbook. While...

Word 2007
I have created several tables in a Word document. I would like to know if there is a way to copy the table formatting from one table to the next while still mainiting the information that is currently in the table. Well, one way you could do this would be to:- - click in the Table - TableTools - Design contextual tab - Table Styles group - choose a Style in here and then apply the same Style to the other Tables that you have. If my comments have helped please hit Yes. Thanks. "Sammy" wrote: > I have created several tables in a Word d...

How to save custom cell formatting?
When I goto Format - Cells - Custom and type my own format. For e.g. #,###,##0.00000 The format mentioned above is available for current file only. How do I make it available to other files (and new ones) as well? ...

Supplier Cost different from Item Cost?
Why is there a separate Supplier Cost for each Item? We worked hard to update the costs of all of our edible items (several hundred), but when we create Purchase Orders, the costs of all the items shows $0.00. It's because the PO shows the cost associated with the supplier. While this makes some sense, why is there a need for 2 different costs?--perhaps in different businesses an item's cost after it is bought from the supplier goes up due to packaging and labor? In any case, is there any way to create purchase orders so that it gets the cost from the regular cost field? Also, what h...

Need to access selective cells from Excel file stored at Sharepoin
I need to link my PPT 2007 slide with an Excel 2003/2007 sheet data stored at Sharepoint 2007. Excel sheet is having lots of cells/rows - hundreds, but i want only selective ranges to be visible in PPT slide. Moreover, if can apply some calculations prior to rendering the date, is also required. Any suggestions please? ...

how do i copy rows to a new worksheet by a cell value
Hi, I have a worksheet with many rows of values. Where a cell in a particular column is "Yes" I wish to copy the values of that row to a different worksheet. Is this something suited to a macro? Many thanks for any assistance. Nibbs without a macro, use data>filter>autofilter>filter on yes>copy -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Nibbs" <Nibbs@discussions.microsoft.com> wrote in message news:144D7242-BAFC-43B4-98EA-EC95657832B2@microsoft.com... > Hi, > > I have a worksheet with many rows of values. Where a cell ...

loop thru cells to find matching record
I'm having difficulty with the following code. I think thre are several issues, perhaps the easier one first. When I set the following range, I noticed it didn't start at row 14 but at row 12, not sure why? Set rng = wsSheet.Range("A14:A" & Cells(65536, "A").End(xlUp).Row). I noticed that the message box shows cells I'm not interested in. The more difficult problem is per the following The active workbook, "sheet 1" has a lising of serial numbers, the serial numbers start at row 14, the exact number varies, hence the set rng as per abo...

look one cell below
Does anyone know how reference one cell below another cell. Example In cell B10 I want to return the value in the cell below cell A5, but I can't type A6 directly. I want to type in something like =A(5+1) but excel doesn't take that. Any suggestions? Thanks! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Hi, try =OFFSET(A5,1,0) Gromit ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post u...

Manual Input of Cell Refs
I need to do a calc on sheet 1 which will use data from sheet 2. I want to point to the row on sheet 2 by manually inputting its number in sheet 1 at ,say, A1. Ex: In sheet 1 a3 I have the formula =Sum(sheet2!A?:G?) where the value of ? has been inserted manually in Sheet1 at A1 TIA Craig Hi try = =Sum(INDIRECT("'sheet2'!A" & A1 & ":G" & A1)) -- Regards Frank Kabel Frankfurt, Germany Craig wrote: > I need to do a calc on sheet 1 which will use data from sheet 2. > I want to point to the row on sheet 2 by manually inputting...

Pivot Table Page Field value from cell in another worksheet
I have looked through the other posts on the subject and the combination of my low skills in VB and the other solutions not exactly covering my situation means I still need to ask for help. Scenario: Worksheet a = "Scorecard" FieldName = "CustomerNumber" Worksheet b = "Products Resume" PivotTable = "PivotTable2" PivotTable Page Field = "Account Number" When CustomerNumber value on worksheet "Scorecard" is changed I need the Pivot Table Page field "Account Number" to use this value to filter the pivot t...

why can't I access conditional formatting?
I cant seem to acces the conditional formatting thing in the formulas pull down. I would really like to use this feature, It just is always unavailible If your workbook is shared, CF is unavailable. See 'Features that are unavailable in shared workbooks' in help -- HTH Bob Phillips (remove nothere from email address if mailing direct) "dennis" <dennis@discussions.microsoft.com> wrote in message news:3B543FFC-BB33-4057-9AC8-B068DEEF6907@microsoft.com... > I cant seem to acces the conditional formatting thing in the formulas pull > down. > > > I w...

Use a Carriage Return when typing data into a text cell
What is the code for entering a carriage return in a text cell.? (not using a char map lookup) I tried "& char(10) &" and various combinations using alt, with no luck. What I want is to type Far[the_char_return_code]Farley[ENTER] resultng in Far Farley in the same cell. I know it exists, because a long time ago I found a spreadsheet that had it in the cell, and I copied the cell. I have been using that, but going to that worksheet and copying that character is a bit of a pain in the butt. There HAS to be a better way of doing it than that. TIA Far Farley The Profe...

Select Different Printer Without Document/View Framework
I want to have my MFC program to select a printer other than the default printer. I have see several examples on how to do this in the Document/View Framework, but that's not what i need. Here are the first couple lines of the print class that I'm using: void CMyPrintClass::Print(bool Default) { m_nPrintPos = NULL; CString strPrintString; m_nPrintPos = GetFirstStrPosition(); //Do Print Job CDC dc; CPrintDialog printDlg(FALSE); //Get printer settings from user if (Default==FALSE) { if (printDlg.DoModal() == IDCANCEL) return; } if(Default==TRUE) { ...

Text Formatting for URL
In CRM, On the accounts form, we would like to have an field point to a portal site where we would keep more information about a client, I have made the field a URL formatting type however it will not show up as a URL just as text. Any help would be greatly appreciated. Stephen ...

Process to dismount store and restore to different machine
Can someone please point me to an online source that describes the process to take an Information Store from one Exchange 2000/2003 AND then restore that same store to ANOTHER Exchange 2000/2003 Server - the method and machine being restored to can EITHER be SAME server or DIFFERENT server name (process should be flexible to allow same or different server name). Thanks! ...