Formatting text and date together

How can I format a cell to hold text ("Last update:") and a date whic
is obtained from another cell? The Formula reads something like +"Las
update: "&f2. The date comes out as its numerical value.

Is it possible.

Thank

--
Brisbane Ro
-----------------------------------------------------------------------
Brisbane Rob's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2509
View this thread: http://www.excelforum.com/showthread.php?threadid=49227

0
12/9/2005 6:54:52 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
486 Views

Similar Articles

[PageSpeed] 28

Use the text function to format the numeric component to suit your needs, 
along the lines of ="Last Updated on " & TEXT(A1,"Mmm dd")
--Bruce

"Brisbane Rob" wrote:

> 
> How can I format a cell to hold text ("Last update:") and a date which
> is obtained from another cell? The Formula reads something like +"Last
> update: "&f2. The date comes out as its numerical value.
> 
> Is it possible.
> 
> Thanks
> 
> 
> -- 
> Brisbane Rob
> ------------------------------------------------------------------------
> Brisbane Rob's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25096
> View this thread: http://www.excelforum.com/showthread.php?threadid=492271
> 
> 
0
12/9/2005 7:04:02 PM
use    ="Last Update: " & Text(F2,"mm/dd/yyyy")

The  "+" that you are currently using is the old Lotus 1-2-3
method; loose it!


"Brisbane Rob" <Brisbane.Rob.1zsdlm_1134154506.2798@excelforum-nospam.com> wrote 
in message news:Brisbane.Rob.1zsdlm_1134154506.2798@excelforum-nospam.com...
>
> How can I format a cell to hold text ("Last update:") and a date which
> is obtained from another cell? The Formula reads something like +"Last
> update: "&f2. The date comes out as its numerical value.
>
> Is it possible.
>
> Thanks
>
>
> -- 
> Brisbane Rob
> ------------------------------------------------------------------------
> Brisbane Rob's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=25096
> View this thread: http://www.excelforum.com/showthread.php?threadid=492271
> 


0
jmay (696)
12/10/2005 10:00:18 AM
Reply:

Similar Artilces:

Problems with date calculations (bank hols etc)
I have a database of dates of claims received. Column A has the customers name, Column B is the date claim was received. Problem is that in column C i want to enter the 60th day (i.e. Whatever is in column B + 60days - however this MUST NOT include Saturdays or Sundays in the calculation and should also NOT include bank holidays). I have a separate sheet of all the bank holidays listed. I haven't a clue how to solve this one !. Please help. So for e.g Mr Smith puts his claim in on 16th March 2005, i want column C to add 60 WORKING days onto this, but miss out the bank holidays (i.e. 25/4...

Custom Format time to arc
I'm trying to find a symbol to Format/Cells/Custom, a symbol that represents the max value of 360�. I know "H" is equal to the max value of 24 hours, "m" is equal to sixty minuets and "s" is equal to sixty seconds, this [h] will roll hours over past 24, and I know to format cells like this h� m' s.00\" but these symbols represent time not the arc of 360�. I want to convert arc to time and time to arc with the symbols in costum format, I don't think this is possible, is it? Am I doing this backwards? Should I be working with a formula or a combina...

Remove space and trailing text from number
Hi ng Using xl xp pro. Receive text file wish to convert to xl Some data comes in like this: 12 oz Would like to trim " oz" w/o quotes So all I'm left with is 12 Here's my formula: =IF(ISNUMBER(A3),A3,(VALUE(LEFT(A3,FIND(" ",A3)-1)))) Works fine. However other data comes in this format: 12+z How could I re-work my formula to take care of either condition Remaining value beginning from left of string could be any number of digits Also: If col a contains text I receive #value! error I take care of this with =IF(ISNUMBER($F1037),$F1037,1) But it would be more el...

A Date Query
I need to create a formula which can do the following: IF the end date (S2) is greater than than 12/31/05, AND the start date (R2) is less than 12/31/05 then...(and I know the formula for here). Any ideas? Thanks? Sam B -- SamuelT ------------------------------------------------------------------------ SamuelT's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27501 View this thread: http://www.excelforum.com/showthread.php?threadid=475036 try this: =IF(AND(S2>DATE(2005,12,31),R2<DATE(2005,12,31)),IF TRUE,IF FALSE) Good Luck -- swatsp0p -------...

Conditional Formatting 03-29-07
H Everyone, I have been playing around with conditional formatting. I Have a few text boxes with shading if certain items are entered. Now I want the first 3 fields in my subforms to be shaded if a record is started. I can get the shading to work but I have the next record showing below the current record that is entered. Those fields are also shaded, I only want them to shade if there is anything entered in any of those 3 fields. Is there a wild card to use so if anything is entered so the other 3 fields will shade? Thanks, Linda Linda, I am not 100% sure I understand what y...

Formating Bars based on the value of a bar
I have created a bar chart in an access report. I want the bars to change color based on the value of the specific bar. For example, if the value of a bar is less than 1, it is red, if it is between 1 and 5, it is yellow, if it is greater than 5 it is green. How can I do this? Seems like an easy problem, but I've just spent a couple of hours playing with this and I can't make it work. The problem is that the Value property of the points in the SeriesCollection is not exposed in the VBA interface. It certainly exists (eg. with the chart in design mode, if you hover the mouse...

How to put Time or Date Only in Cell 1) Time HHMMSS 2) Date YYYYMMDD
I need to have a cell = the current time in the format HHMMSS And when I export as Comma Delimited or Fixed Length Ascii, I need for it to remain in that format. Ditto for Date as YYYYMMDD I've played around with both =TIME and =DATE but no luck. Can anyone tell me how to do this? AND, once the forumula is in the cell... how can I get it to update to the current time or date? thanks for any help I formatted my times as hhmmss and my dates as yyyymmdd and then did a file|saveAs. I chose the comma separated values (*.csv) and I got what you wanted. I opened it in notepad to ver...

Aligning text to right margin using right tab
Hi, I am trying to create a heading in a Publisher 2003 document, the first part of which starts flush with the left margin and the second part of which ends flush with the right margin. I have done this before in Word using left aligned text, with a right tab stop at the right margin. However, this doesn't seem to work in Publisher - the tab seemingly has to be in indented from the actual margin position, otherwise the text wraps onto the next line. Am I doing something wrong, or have I missed something? I don't really want to create a separate text box to achieve this, as ...

BSOD in RtlStringCbVPrintfA() when using %wZ format specifiers on XPSP3
I get a BSOD when using this func with the UNICODE_STRING format specifier %wZ (the bug is actually in vsnprintf. when the UNICODE_STRING is empty. ie, a valid buffer (zeroed or unzeroed makes no difference) with the Length and MaximumLength set to zero. Now I know these specifiers aren't documented, but they exist, and are well known, and used. This is the crash: 0: kd> !analyze -v ******************************************************************************* * * * Bugcheck Analysis * * * ****************...

how to store text in a cedit window to file
how do you store the text from a Cedit window, or any CString, to file as text that can be viewable by other applications such as notepad? CString s; c_MyEditControl.GetWindowText(s); CStdioFile f; if(!f.Open(_T("Name.of.your.choice.txt"), CFile::modeCreate | CFile::modeWrite))) return FALSE; // or other appropriate error action f.WriteString(s); f.Close(); return TRUE; // or other appropriate success action joe On Mon, 4 Aug 2008 16:33:30 -0700, "Nick Schultz" <nick.schultz@flir.com> wrote: >how do you store the text from a Cedit window, or any C...

Report Card-like format
I don't know the best NG for this questions, so I guess that's why they've got excel.misc <g>. I have a simple table of data like this: Date Field1 Field2 Field3 7/14/2003 123 254 75 7/15/2003 201 10 124 7/16/2003 215 258 2 7/17/2003 78 8 89 7/18/2003 125 186 582 7/19/2003 254 45 257 7/20/2003 200 199 89 The table is always the same size, but the data changes as the days go by. What I'd like to do is have the table look like a card. Sort of like a report card or other. My idea is a rectangle ...

Cells formated as text do not always display properly
I am using Office XP and Windows XP. I have formatted a column of cells as text and word wrap is checked, horizontal alignment is Left, Vertical alignment is Bottom. I use these cells to type a short narrative. The problem is some of the cells will not display properly, all they show is ######. The row height will expand to hold the text sometimes but not always but it will still only show ######## instead of the text I typed. If I change the cell type to General all the text is visible. This does not happed to evey cell, only some of them. I can type several rows of data with a narr...

Concatenate text & number cell
Hi all, I need to concatenate a word and a number cell. eg, Concatenate("Limit = $",A1) where A1 = $100,000. It shows up like this: "Limit = $100000". However I want the number to format with the comma between the thousand, ie. "Limit = $100,000" Does any one know how to do this. Thanks in advance. regards, John. one way: ="Limit = " & TEXT(A1,"$#,##0") In article <2285c01c45d99$ff299070$a301280a@phx.gbl>, "John" <anonymous@discussions.microsoft.com> wrote: > Hi all, > I need to concatenate a wo...

convert number into corrseponding text
Hi, I would like to know if there is a way to display text on the corresponding number. eg. I type 530,000 and it would show me Five hundred and thirty thousand Take a look at http://www.xldynamic.com/source/xld.xlFAQ0004.html -- HTH RP (remove nothere from the email address if mailing direct) "mustafa" <mustafa@discussions.microsoft.com> wrote in message news:6A5B7274-959A-4E78-B9B9-D8A31D23D3D1@microsoft.com... > Hi, > > I would like to know if there is a way to display text on the corresponding > number. > > eg. > I type 530,000 > and it ...

Text Box 05-21-10
I have the following event procedure on a text box: Private Sub txtNotesOnTransaction_BeforeUpdate(Cancel As Integer) Debug.Print "User: " & CurrentUser() & " " & Me!txtNotesOnTransaction End Sub It is suppose to stamp the user name in the text box but it does not work. Any suggestions? Thanks PJ - The debug.print will just display (in the immediate window of the code pane) the name and the Notes. If you want the notes saved with the name, do this: Me!txtNotesOnTransaction = "User: " & CurrentUser() & " " &a...

Compare dates (one cell not in date format)
Hi all, I have one column of cells in date format (6/14/2005) and anothe column of cells which also contains a date pulled from a database bu this second column is not in date format, it comes from the databas like this 2005Jan20. I need to compare the two dates to work out th number of days between the dates but I can't convert the second date t date format. Is there any way I can work out the number of days between the tw dates? Thanks in advance Crai -- craigcs ----------------------------------------------------------------------- craigcsb's Profile: http://www.excelforum.com/...

Excel Date
want to be able to have a cell auto fill the first time the sheet i saved so i have the creation date. Then if poss - never let them chang the cell so it keeps record of when the file was first created. Thanks - any help will get some free chocolate! ;-) Arie Astor Chocolate Corp. www.astorchocolate.co -- Message posted from http://www.ExcelForum.com Try some code in the workbook module ; e.g. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Range("Sheet1!A1") = "" Then Range("Sheet1!A1") = Date End If End Sub Then set Sheet1...

Convert text to number
Hi guys, I receive a workbook that contains unformatted text and need to wor with numbers. I have MS Excel 2000 and can not convert text to number Just do not know how. P.S. Select Range - Format to number does not work. Thanks a lot to everybody who has any advice. Irina -- Irin ----------------------------------------------------------------------- Irina's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=868 View this thread: http://www.excelforum.com/showthread.php?threadid=26345 Hi what type of text does your range contain. Could you post an example? ...

place cursor in a specified text box in a Word form
Hello, I have a Word form developed in vba and when a specific textbox is empty a message box appears to tell the user to enter some data before they can proceed. When they click ok to continue the cursor places itself in textbox4 on the form but I want it to be in the box they have left empty (in this case textbox5). I have used the SetFocus method but that doesn't seem to do anything, it still ends up in textbox4. Any ideas anyone? Thanks. What sort of a form? If a protected form see http://www.gmayor.com/formfieldmacros.htm -- <>>< ><<> >...

Will Windows' format mark bad sectors?
The external (USB connected) hard drive I use for backups apparently has developed some bad spots. Formatting the drive starts producing System Event Log entries at about 12% into the format, with the decription being "The driver detected a controller error on \Device\Harddisk3\D." If I allow the format to continue, will it eventually plow its way through the bad area and mark those sectors as bad, or is this whole exercise a waste of time? -- Bert Hyman St. Paul, MN bert@iphouse.com Waste of time would by my feeling! When a "modern" drive has enough er...

formula is displayed as literal text instead of formula result
I have been having trouble frequently with formulas appearing as litera text in the cell instead of a formula with a result. Does anyone have any suggestions as to what I may be doing wrong -- carlossalt ----------------------------------------------------------------------- carlossaltz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2420 View this thread: http://www.excelforum.com/showthread.php?threadid=38396 The cell you're working in is probably formatted as text. Try changing the format to something appropriate. If you are entering the formula cor...

Formatting a List Box for Uppercase
I'm trying to format the data in a List Box to be upper case. I've tried ">" in the field properties, but that doesn't work. Below is the SQL Statement in my query. Any idea where I would insert something to make the First_Name and Last_Name fields to be all upper case letters? SELECT DISTINCT [tbl_PMS_Persons].[FIRST_NAME] & " " & [LAST_NAME] AS Expr1 FROM tbl_PMS_Persons ORDER BY [tbl_PMS_Persons].[FIRST_NAME] & " " & [LAST_NAME]; Thanks in advance, Fred Try changing the Row Source of the list box SELECT DISTINCT UCas...

Formatting Odd Number Strings
I have imported a text report into Excel. For some reason the date string on the text file reads as 20/05/0728. Is there a quick way to reformat the string so it reads 7/28/2005 or even 2005/07/28, for that matter. I really don't want to manually edit 300+ lines! Thanks. Assuming your fubared date is in column A, insert a new column immediately to the right of that, and enter this formula: =DATE(LEFT(A1,2)&MID(A1,4,2),MID(A1,7,2),RIGHT(A1,2)) This parses the existing text string into arguments used by the DATE function, which generates a date useable by Excel. What is the cell...

date problem--excel changing entry
I want date to show as 11/05/03 and not have to enter the / or a -. Have formatted cells to date and that but excel ix changing to a totally different date----7/19/06? You could use a macro that converts numeric entries to dates. Chip Pearson has shared his: http://www.cpearson.com/excel/DateTimeEntry.htm Or if you're consistent (leading 0's for days and months), you could format the column(s) as text, do your data entry and then use data|text to columns to convert them to real dates (choose mdy as the field type). D Blount wrote: > > I want date to show as 11/05/03 and...

Need help formatting text
I am using OE 6 to read newsgroups and I sometimes post articles that I find on the web, often copying and pasting articles into new messages. However, when I do this, it formats it in such a way that when I open my posts, the paragraphs are separated by at least 3 lines, rather than 1 line. I have tried adding an additional step of pasting articles as unformatted text in word before I paste them in a new message in OE. It seems that no matter what I do, however, I get this weird extra spacing between the paragraphs. Anyone have any idea how to solve this problem? -- Aloha, G-Ride &qu...