Adding text and date problem

I have text in column 1, a date in column 2 and in column 3 I wish to
show the text followed by the date.
I have tried both Concatenate and & but the the result is text followed
by  the underlying number representing the date. I cannot seem to format
this column to show the date as dd/mm/yy. Any suggestions?

Regards
0
aa111 (29)
5/17/2007 8:47:05 AM
excel 39879 articles. 2 followers. Follow

3 Replies
550 Views

Similar Articles

[PageSpeed] 22

="Today is: "&TEXT(A1,"mm/dd/yyyy")

-- 
Kind regards,

Niek Otten
Microsoft MVP - Excel

"newman" <aa111@despammed.com> wrote in message news:464C1646.AC6A42E0@despammed.com...
|I have text in column 1, a date in column 2 and in column 3 I wish to
| show the text followed by the date.
| I have tried both Concatenate and & but the the result is text followed
| by  the underlying number representing the date. I cannot seem to format
| this column to show the date as dd/mm/yy. Any suggestions?
|
| Regards 


0
nicolaus (2022)
5/17/2007 9:20:03 AM
Example for text in Cell A1, date in cell B1: In C1, enter the formula

=A1&TEXT(B1,"dd/mm/yy")

Then copy downwards the formula for the range you wish to use.

Kind regards,
Erny

"newman" <aa111@despammed.com> schrieb im Newsbeitrag 
news:464C1646.AC6A42E0@despammed.com...
>I have text in column 1, a date in column 2 and in column 3 I wish to
> show the text followed by the date.
> I have tried both Concatenate and & but the the result is text followed
> by  the underlying number representing the date. I cannot seem to format
> this column to show the date as dd/mm/yy. Any suggestions?
>
> Regards 


0
5/17/2007 6:11:31 PM
Niek Otten wrote:
> 
> ="Today is: "&TEXT(A1,"mm/dd/yyyy")
> 
> --
> Kind regards,
> 
> Niek Otten
> Microsoft MVP - Excel
> 
> "newman" <aa111@despammed.com> wrote in message news:464C1646.AC6A42E0@despammed.com...
> |I have text in column 1, a date in column 2 and in column 3 I wish to
> | show the text followed by the date.
> | I have tried both Concatenate and & but the the result is text followed
> | by  the underlying number representing the date. I cannot seem to format
> | this column to show the date as dd/mm/yy. Any suggestions?
> |
> | Regards

Thanks

I have one other question.

I have modified your info to also show the time using the now()
function.

However I have to press F9 to recalc this when I need to see the correct
time in the cell. Is it possible to have an automatic recalc occur at
set intervals or alternatively when that particular worksheet becomes
the active one?

Regards
0
aa111 (29)
5/21/2007 9:12:37 AM
Reply:

Similar Artilces:

Here is fix for Windows file problems
If you have installed a Windows update and started having problems opening, closing, copying files, see: http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.excel.misc&mid=1fe358b1-c8eb-4217-ad2a-4b9fcf741916 A better solution is offered at http://support.microsoft.com/kb/918165 "Problems in Windows Explorer or the Windows shell after you install security update MS06-015". Patricia Shannon wrote: > If you have installed a Windows update and started having problems opening, > closing, copying files, see: > > http://ww...

Problem with CFTPConnection
Hi I've an application that connects to an FTP Server and uploads a big directory (size over 500 MB). Now what happens is that after sometime the application stops uploading files and stays idle. The FTP server connection timeout value is set to 2 minutes. So the server seeing the connection idle for two seconds, disconnect the session and no more file is uploaded. On debugging I found out that after some time CFTPConnection.PutFile() method hangs on some random file and stays like that for 5 minutes (default send timeout value of CFTPSession). During this time the server connection timeo...

Excel 97 Win XP calc problem?
I have a machine running office 97 and Win XP. I have seen posts stating it runs fine, but I don't know how man people use spreadsheets that require a hard calculation: Excel will not force a calculation. i.e. ctrl-alt-f9 does not cause full workbook calculation as required. Meaning cells do not update and it doesn't calculate unless you clic in the cell to edit and hit enter. neither does the "Calc Now" button. Auto or manual calculation mode has no effect. Anybody else have this problem -- Message posted from http://www.ExcelForum.com Make sure that both the SR1 a...

if cell is text move left one column
ColB is a long list with sections names followed by category codes I need to move the text into colA leaving colB with codes only (all numbers) ColB. Doors 940590 555998 447006 447008 810697 810705 810706 810707 Windows 619435 525691 525692 Try Sub Macro1() Dim lngRow As Long For lngRow = 1 To Cells(Rows.Count, "B").End(xlUp).Row If Not IsNumeric(Range("B" & lngRow)) Then Range("A" & lngRow).Value = Range("B" & lngRow).Text Range("B" & lngRow).Value = "" End If Next End Sub -- Jacob ...

Date display in Excel
Format column of cells as Date, display as mm/dd/yy. Date entered into cell, shows up correctly in the text entry field at the top of the screen, but the data on the worksheet displays as "33747", or similar number. Only happening on one workbook. Try tools|options|View tab|uncheck Formulas. Clark wrote: > > Format column of cells as Date, display as mm/dd/yy. Date > entered into cell, shows up correctly in the text entry > field at the top of the screen, but the data on the > worksheet displays as "33747", or similar number. Only > happening on one...

CRM Outlook Client Configuration Wizard Problem
CRM 4.0 - Microsoft Outlook Client Guys, We've Microsoft CRM 4.0 Server roll up 8 as well as CRM Outlook Client on the same roll up version. Our Internal Web Access works without problems users get to it and they're automatically authenticated. IFD Development is the same way except they're prompt through a login page for their username and password when they're coming from an external network. Ever since we moved to a new facility users are experiencing problems connecting to CRM using their outlook client, I have a test machine where I have the CRM Outlook Client ins...

C# Adding Days to a Date
Hello, I have 2 objects: objContract.activeon and objContract.expireson. I am trying to add 364 days to objContract.activeon and assign it to the value of objContractexpireson. ---------------------------------------------------------- // Contract Start Date DateTime dt = DateTime.Now; objContract.activeon = new CrmDateTime(); int iFound = 0; string sTemp = ""; if (objAccount.paymenttermscode.Value == 1) // due on receipt - use Todays Date { objContract.activeon = objInvoice.CFDinvoicedate; } else // ...

Separating Date and Time in a cell
I have a column of cells in the format "11/01/02 06:21". I would like to separate the text into 2 cells - one with the date and the other with the time. My attempts with LEFT and RIGHT have been unsuccesful. Thanks for your help Sameer --- Message posted from http://www.ExcelForum.com/ For the date use =INT(A1) replace A1 with the first cell of your range for time =MOD(A1,1) you probably have to reformat the first to mm/dd/yy (or whatever the setting is) and hh:mm Note that you can do this by just using format but if you want to compare to other cells with just pure d...

Adding support for mfc / atl types in a Win32 project
MSDN says this about adding support for classes shared between atl & mfc (I'm just after points and rects btw)... just add the header ( atltypes.h ). I'm getting linker errors for these classes though. Is there a lib I need to add? Any screwy build options need changing? It's in VC 2003. In the project wizard I note that atl and mfc support aren't available when creating an empty Win32 application. Ta. On 26 May 2006 07:42:09 -0700, Simon L wrote: > MSDN says this about adding support for classes shared between atl & > mfc (I'm just after points and rect...

Problem with Update function of CRecordSet class
Hi All When I use from Update function of CRecordSet class, it apply to table after a few time(for example 1 second) when I need to see it at time. Thanks ...

OL2002 Problem : HELP, SOS, MAYDAY
Hi, I have an IMAP account and OL2002 as client. I have checked for my account ( tools, send/receive settings, define send/receive groups, <my_account>, edit) download item description only. It works only the first time I start OL2002, but it does not work anymore for subsequent automatic/manual send/receive operations. Please could someone really help me ? Thank you in advance, Patrick ...

Problem with macro assigned to custom toolbar button
Problem with macro assigned to custom toolbar button I am having a probelm with macros assigned to a custom button on custom button. I am using excel 2000. 1. I have created a macro called "Jump" in a workbook named "Test1" 2. I then created a custom toolbar and added a custom toolbar button t that toolbar. 3. Through the customize dialog box I assign the Macro "Jump" to th custom button. Everything works fine UNTIL. If I do a SAVE AS for the workbook "Test1" and call it something els like "Test2" and then close "Test2" an...

request to solve my problem #5
hello friends this is vijay,i got a problem while doing my project based on vc++ (i.e)"How to implement Fit Window feature in VC++6.0 in an MDI Application" if any of you got any idea about that please do reply me with the proceedure.i will be waiting for your reply bye with regards vijayprasadreddy ...

pulling certain characters from a string of text
I need to look up "certain critera" within a string of characters, then return that "certain criterea" to a new column. Some examples of a strings of characters may look like these: K5J091509001 Sample PO#S881009 K55sample PO CarrieRJR TJ5 My "Certain Critera" I have listed on another sheet, named "REP ID" K5J S88 K55 RJR TJ5 How do I pull out the 3 characters of "Certain Criterea" from the string of text and copy or enter it into a new column? Hi, =left(a1,3) "SaraMack" wrote: > I need to look up "c...

Excel. I am having a problem with hidden text
As I type text in some cells, it does not always dispaly if it exceeds the cell length. I wish to keep the cell lenghts for the entire document, but do wish for teh text in that particular row to be displayed. How about if you select that cell, then format|cells|alignment tab|check wrap text And with that row selected format|row|autofit SHR77 wrote: > > As I type text in some cells, it does not always dispaly if it exceeds the > cell length. I wish to keep the cell lenghts for the entire document, but do > wish for teh text in that particular row to be displayed. -- Da...

can I snap wrap points to a text box
rather than having to add individual wrap points to the edge of a frame, which is never as accurate anyway, can they be set to 'snap' to a frame (eg the ellipse) so that they are perfectly inline, (and which would of course be a lot quicker)? Edit points will not snap. There are options for edit points, select a point, right click. If you hold down control, the cursor will turn into an x, you can delete a point with a click. Truly a good draw program would be preferable. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com &q...

adding extra marker styles
how do we add extra marker styles into an excel chart? we have used all the ones it has given us in 2003, but there use3d to be many more in 2002, how do we get them back? Hi, Here is an explanation on how to add custom markers. http://peltiertech.com/Excel/ChartsHowTo/CustomMarkers.html I don't think the marker style have changed since xl2000. Cheers Andy Grizzabella wrote: > how do we add extra marker styles into an excel chart? we have used all the > ones it has given us in 2003, but there use3d to be many more in 2002, how do > we get them back? -- Andy Pope, Micro...

default text height comment
Is there a way to set the default text height for a new comment? Thanks mark (I've looked through help but can't find it if it's in there.) I assume you mean the font size? There is no text height available in Excel. A comment has a shape property and that is what you can use to change the font size. They didn't make it easy ... Range("D4").Comment.Shape.TextFrame.Characters.Font.Size = 12 -- Jim Cone Portland, Oregon USA http://www.mediafire.com/PrimitiveSoftware (free and commercial excel programs) "mp" <nospam@Thanks.com> wrote in message...

Change the text of a shape rather than its master
Hi, I build custom masters by mixing two general shapes, say square and circle together, and have text on both the shapes. But after I drop an instance of the master into a page, I cannot modify the text of the instance. To do so, I need to modify the text on the master, which is non-sense for me. How to change the text of a shape without modifying its master? Thanks! How are you doing this? By code or by the UI? Are you grouping the shapes? If you drag two shapes to the stencil, it will group the shapes. So instead of a square and a circle you have three shapes. A Square, Circle and the...

date function #3
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C73005.0FA093A0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have a cell A1 with date 5/2/07. If in cell A2 I do month(A1), I get = 5. Is there a way to return May instead? either through a command or = formatting? I can reference cell A2 with the 5 with a if(cell =3D 5, = "May", ) in cell A3, but the date in cell A1 varies from Jan to Dec and = the 12 embedded if's in cell A3 are too long (that is the error I get = when trying to do so), plus i...

Removing spaces from text #4
I'm in excel and i have a bunch of text data that has an extra space at the end of the text on the right hand side for each cell. Is there any easy way to remove this space? Use the TRIM() function. -- Kind regards, Niek Otten "lj" <lj@spu.edu> wrote in message news:1144876429.220961.309040@j33g2000cwa.googlegroups.com... > I'm in excel and i have a bunch of text data that has an extra space at > the end of the text on the right hand side for each cell. Is there any > easy way to remove this space? > I tried using that function but the results st...

Removing text from cells leaving numbers (help with function)
I need a function that will remove all text from a cell and just leav numbers. Formatting cells to number does not work. For example if I have: (Sired] Tennessee 37013 (herein I just want 37013 left. Anybody know a function to resolve this -- Message posted from http://www.ExcelForum.com The following will strip the text from the active cell and place the number in the adjcent cell one column to the left. If there are subsequent numbers in the original string you will get erroneous results. Put the cursor on the cell to be processed and run the macro. ********************************...

searching a cell for a contained text word
Is it possible to search a cell for a key word or words contained in text made of multiple words enabling the user to than create a pivot table using the collected key word or words as data? Doug K ...

how do I change date format in the header in Excel XP ?
I need to chage my date format, in the header to Day; Month DD, YYYY ie. Saturday; May 23, 2005. Woudl you please help me out ? Thank you. Regards, Hesam Shakourian Check this out. http://support.microsoft.com/?kbid=213742 but change the format to "dddd, MMMM dd, yyyy" "Hesam" <Hesam@discussions.microsoft.com> wrote in message news:93495F75-4196-4208-9C0D-E800BCAE3A89@microsoft.com... > I need to chage my date format, in the header to Day; Month DD, YYYY > ie. Saturday; May 23, 2005. > > Woudl you please help me out ? > > Thank you. > &g...

Excel List Sorting Problem (Descending)
Hi there, I'm having trouble sorting my list--my column contains *only* 4-digit numbers but when I click on "descending order", only about the first half of the rows are arranged this way, before it begins again to arrange the rest in descending order. Like this: 5120 5119 5118 4000 3050 5116 4112 etc. Has this problem happened for anybody else? I'd appreciate any help you can offer. Part of your list is text, although it looks like numbers. Format an empty cell as number. Enter the number 1. Copy. Select your "numbers". Edit>Paste Special, check Mul...