concatenate text and date

when I concatenate a text field & " "&date field it's displaying the text 
fine but displaying the date in serial number
e.g. location and date 01/01/2001
becomes location and 19283 
How can I maintain the date format?
0
TaraKeane (6)
2/11/2005 5:09:02 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
767 Views

Similar Articles

[PageSpeed] 40

="Text " & text(now(),"mm/dd/yyyy")

Replace now() with your date and mm/dd/yyyy with the formatting you desire

http://HelpExcel.com

"Tara Keane" wrote:

> when I concatenate a text field & " "&date field it's displaying the text 
> fine but displaying the date in serial number
> e.g. location and date 01/01/2001
> becomes location and 19283 
> How can I maintain the date format?
0
galimi (74)
2/11/2005 5:13:05 PM
Use the TEXT function

=text field & " "&TEXT(date,"mm/dd/yyyy")

-- 

Regards,

Peo Sjoblom

"Tara Keane" <TaraKeane@discussions.microsoft.com> wrote in message
news:2049521D-0CDF-43E8-BC69-93D1880E247B@microsoft.com...
> when I concatenate a text field & " "&date field it's displaying the text
> fine but displaying the date in serial number
> e.g. location and date 01/01/2001
> becomes location and 19283
> How can I maintain the date format?


0
terre081 (3244)
2/11/2005 5:17:05 PM
Reply:

Similar Artilces:

Remove text "Discount code: **" from receipt??
Hello, I have a weird problem within a receipt. I got two registers, with different receipt.xml linked. Register 1 prints on the receipt the text "Discountcode: ***" when an item is sold with discount. Register 2 doesn't print this text, even if I use the same receipt format?? I can't get what the problem is? Hope somebody can help me. Greetings Raymond Bakker the Netherlands Are you using windows drivers on one of the printers? "Raymond Bakker" <raymond.bakker@rebus-it.nl> wrote in message news:1C61DF7A-1E93-4BD2-ABDF-46731BDC9B25@microsoft.com.....

When concatenating concatenates don't concatenate...
Hi List, Can anyone help? When concatenating already-concatenated cells, th result displays perfectly well in the Excel spreadsheet, but truncate when the cell is pasted into a .txt file. It doesn't seem to be due t Data Validation limits (having said that, selecting the entir worksheet and doing Alt > Data > Validation > Validation criteria Allow = Any value" did seem to solve the problem once, but only to com back next time round). The truncation occurs sometimes after 8 or 1 chars, and sometimes after 20 or so, always at the same spot. If I cop the cell into a fresh Excel...

Display Text in the Data Area of a Pivot Table
I would like to simply display text in the data area of a pivot table without summarizing it. For example, take the following database: Date Time Instructor 8/9 8am Jones 8/9 9am Smith 8/10 8am Anderson 8/10 9am Doe ....etc and display it in a pivot table like so: | 8/9 | 8/10 --------------------------------- 8am | Jones | Anderson 9am | Smith | Doe thanks. ...

Convert text to number in IIF statement
Hi, I have a query where I am using an iif statement so that my field we either equal text or number depending on the value of another field. Here is my iif statement Portfolio1: IIf([qry_mhfsac4dPassThrough.SecId]="60934n583","FPA",IIf([qry_mhfsac4dPassThrough.SecId]="825252646","Aim",CInt([portfolio]))) I am basically saying that if the SecID of a security is x then"FPA" or "AIM" otherwise I want to see the portfolio number. However, whether I use the formula cint() or val() or a couple others that I have tried, this field still c...

Concatenate function
Hi, How can I concatenate these 2 cells: one is time and the other is text: 9:00 and AM and I want the result to be 9:00 AM =CONCATENATE(AD2, " ", AE2) I have tried different formating cells but it doesn't work, this is what I get: 0.375 AM Thanks for your help. NSNR - You must format the time (which is a number) to text: =TEXT(AD2,"H:MM") & " " & AE2 -- Daryl S "NSNR" wrote: > Hi, > How can I concatenate these 2 cells: one is time and the other is text: 9:00 > and AM and I want the result to be ...

Search column and move text formula
I am trying to collate results from an online questionnaire. I have a list of all possible field names in column A. I then import the results of a submitted questionnaire - these come in 2 columns and I place them in C and D. D cotains the data/responses and C is the corresponding field name. Unfortunately questionnaire fields that are not answered do not produce anything in column C (ie return no field name) thus I need to match up the data in D with the corresponding field in A. The only was I can see doing this is to devise a formula that searches column A for the fieldname in say ...

Reading Pane Text Problem
The message displayed in the reading pane is larger than the pane. Is there anyway to confine the text to the pane, which would happen if there were word wrap. It is a nuissance to have to slide the view left and right to see entire sentences. Never did this before but it does it now and I would like to get back to the orignial display of messages. Any clues what may have happened and how it can be fixed?? The PC is a Dell with pre-installed software. Oh, and I am a novice so be gentle. Thanks for your help. Henry ...

How to convert date to text
I have a field - date. Let's say that one of the values is 6/20/05. How can I convert it to a number field, for example 6/20/05 will be shown as 20050605? Thanks, galsaba =TEXT(A1,"yyyymmdd") -- HTH RP (remove nothere from the email address if mailing direct) "galsaba" <galsaba@aol.com> wrote in message news:1116789405.609939.308320@g47g2000cwa.googlegroups.com... > I have a field - date. Let's say that one of the values is 6/20/05. > How can I convert it to a number field, for example 6/20/05 > will be shown as 20050605? > > Thanks,...

Undefined Function DATE
Access 2003 On 3 PCs the query works, on two PCs it gives the error message Undefined function DATE when I use Date() as a query Criteria Any ideas? Thank you. On Fri, 16 Nov 2007 17:12:15 -0500, Brendan on Comcast.net wrote: > Access 2003 > On 3 PCs the query works, on two PCs it gives the error message > Undefined function DATE > when I use Date() as a query Criteria > Any ideas? > Thank you. Those PC's have a missing reference. Open any module in Design view (or click Ctrl + G). On the Tools menu, click References. Click to clear the check box for the typ...

"Paste" always pastes same text
Hi, I need to individually copy and paste a list of names into a text box in Publisher 2003. However, the Paste command (Ctrl-V or Edit/Paste) in Publisher seems to be "stuck." Each time I copy a name and try to paste it, the very first name I copied and pasted appears, not the name I just copied. I've tried emptying the Windows clipboard, but this strange behavior still occurs. The original names come from a multipage Word 2003 document. I've tried copying the names to Notepad to be sure they are in plain text, then copying and pasting them into Publisher. I've ...

Filltering data between two date ranges
Hi, wonder if anyone can help me - I have a list containing peoples birthdays. I wish to be able to enter a date range i.e. today and 2 weeks into the future. I then wish for the list to be filtered showing only those birthdays within the date range. The date range could span across 2 different months. I'm not particularly skilled in using features such as VBA etc, so if there is a simple solution it would be much appreciated. Many thanks for any help. Hi Dave! If you enter the birthdays as Month/Day this is somewhat easy. If you enter the birthdays as Month/Day/Year it is much m...

weekending date of a given date
Hi All, If I have a past date of say fri 3/7/2008 and the weekending date for me is that sun the 9th, is there a way to get that date of the 9th? thanks, rodchar You can base it on offset from a well known Sunday date: DECLARE @dt DATETIME; SET @dt = '20080307'; -- 7 Mar 2008 -- Jan 7 1900 is Sunday SELECT DATEADD(DAY, (DATEDIFF(DAY, '19000108', @dt) / 7) * 7, '19000114') AS sunday_date; /* sunday_date ----------------------- 2008-03-09 00:00:00.000 */ -- Plamen Ratchev http://www.SQLStudio.com thanks for the help, rod. "...

Concatenating Cells
I have spent hours this afternoon in Excel 2003 trying to concatenate two adjacent text columns into a third column defined as Text format. It doesn't work, the result cell just displays the formula you enter {e.g. =A1&B1 or =CONCATENATE(A1,B1)}. I discovered after a great deal of frustration that this will only work if the cell containing the formula is formatted as '*General'*. All the MS command help refers to the data being concatenated as 'text' data as does the command help that displays as you type. I found no help on this on the MS site and trawling the w...

Text prediciton
How can I stop excel completing text when I enter data e.g. if a line above says "study day" I enter the line below "student" when I enter "s" it will automatically enter "study day", how do I stop this? Hi, Tools>Options>Edit tab - uncheck 'Enable AutoComplete for Cell Values' HTH Martin Thanks Jason "MartinW" wrote: > Hi, > > Tools>Options>Edit tab - uncheck 'Enable AutoComplete for Cell Values' > > HTH > Martin > > > Boenerge, You may know this, but when the autocomple...

date shows none in out of box
Cannot send e-mails, I can compose an e-mail and it goes into the outbox but will not send. The date shows none in the outbox. Every e-mail I compose goes into the outbox but the date shows none. Please advise Michael wrote: > Cannot send e-mails, I can compose an e-mail and it goes into the > outbox but will not send. The date shows none in the outbox. Every > e-mail I compose goes into the outbox but the date shows none. > Please advise Version of Outlook? Type of Mail Server? If pop/imap, does it go if you do a Send/Recieve? Did it ever work? Any known changes between i...

Payroll Manual Checks taking user date and not check Date
Hi everybody, I am facing a problem when posting the Payroll Manual checks, it's taking the user date as the check date and not the check date itself. Anyone who can help on this issue. Thanks, -- Khaled ASK Mr Khaled, I tested the scenario which is stated by you in Fabrikam company. I entered a manual check with posting date (01 April 07) on Batch Entry Windows, which is the check posting date in General Ledger. My Userdate is the (12 April 2007) and My Check Date is (10 April 07) i posted the check and its all fine. The Checkdate is exactly the Date i give for Check, My Post...

How do I turn a scanned handwritten document into text?
I would like to be able to scan pages in a handwritten journal, and be able to edit them as text in MS Office (OneNote or Word). I have MS Office 2007 + MSO 2003, also OneNote 2007. I use a pen and tablet, so I can manually use handwriting in the Office Programs. I have a scanner. I have tried the Document Imaging program in MSO Tools, that scans a typed document in TIFF format. However, handwritten documents don't seem to work. Surely, there must be a solution Out There? On Jan 3, 4:48=A0am, TeaPatch <TeaPa...@discussions.microsoft.com> wrote: > I would like to be...

Checking for the date after midnight
Hi, In one of my forms, when user saves the data, the form also records the date the data was saved by the user i have named the field as transDate. This is working fine but the problem is since, we work from evening 6:00 PM to morning 3:00 AM, when user saves the data after 12:00 AM, the transDate field takes the next days date but what i need is that, even after 12:00 AM, system should calculate and take previous days date. Please help. Let me know if my question is not clear. thanks, navin Try using this --- DateAdd("h", -3, Date()) Maybe -4 to be safe? -- KARL DE...

Mixed colur text.
I have cells that contains a mixture of black and red text. Other cells contain either only black or only red text. I wish to (a) selectively locate the cells with mixed-colour text and then (b) change the red text to bold black text. leaving any original black text as unbold. I have got as far as the stage of trying to detect these cells. When I use: Cells(i, j).Select iColour = Selection.Font.ColorIndex Valid indexes are returned for only black or only red text contaiing cells. But I get Invalid use of "Null" as an error message on the mixed cells. . ...

IF and Concatenate
Hi I have a following chart which list out delivery dates arcross the top with items and units on the body of the chart (dashes are spaces) On the right column is the results that I need. What kind of formula can I use to return such results? I thought that I can use IF and Concatenate formula (IF, ordered units, then seek out date....). Not even sure I can use IF, since in my real chart, I have more than 20+ dates going across. Any help would be much appreciated! Style---7/30---8/13----8/14----8/15---8/17--------Wanted-Result 66106-------------------------------9--------5--------9 DUE ...

How to format date as ddd, mmm, (date)
I would like Sunday, October 01, 2006 to show up as Sun Oct 1st Thanks for your help! ....Lisa...Excel 2003 Excel dates are days since Jan 00 1900 so one day is 1 etc. If you type in (assuming you are using US date formats) 10/01/06 in a cell, then use custom format ddd mmm d it will return Sun Oct 1, you can't format for ordinals like 1st, 2nd 3rd etc, you would need either an event macro or a formula in another cell and neither is really a good solution. You can of course type in Sun Oct 1st but it will be text -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Exc...

How do I lock a text and picture box together?
I have a text box (word art) with wording over a picture box and want to lock the two together so if you move the picture, the words go with it in the same position. Anybody have any tips? Beyond any corner left click and drag to beyond the opposite corner and release. This will put a box around all the boxes within and now click the key box at the bottom. They are now locked together. -- Don Vancouver, USA "Rob" <Rob@discussions.microsoft.com> wrote in message news:00E8E537-AC0E-40EA-ADA3-547DA49623AA@microsoft.com... > I have a text box (word art) with wording o...

When concatenating concatenates don't concatenate... #3
Sometimes the simplest thing... I didn't realise that all the files ha to be open for the concatenation to work :rolleyes: . Doing s eliminates the problem and explains the erratic behaviour. And yes, th idea is to blend the variable and unvariable parts of an html pag together by pasting the final concatenation into a notepad.txt file Primitive, but interim. Thank you very much for your help. Best regards d'A -- d'A ----------------------------------------------------------------------- d'Az's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1504 ...

Date Formatting
With the help of Biff yesterday I entered the formula ="Closing Balance at "&TEXT(E4,"dd-mmm-yyyy") which returned Closing Balance at 31-Mar-2010 exactly as I wanted - almost. My superiors require that to conform with the rest of the document the month element of the date be in upper case, i.e. Closing Balance at 31-MAR-2010 I have tried, in vain and in desperation, the following - ="Closing Balance at "&TEXT(E4,"DD-MMM-YYYY") and ="Closing Balance at "&TEXT(UPPER(E4),"dd-mmm-yyyy") but these do not have th...

Subtracting Times with dates
I have 4 colums on for start date one for start time one for end date and one for end time I need to find a formula that will give me just the difference in the two times they may be two to 3 days different in the dates. example 3/1/2010 14:30 3/3/2010 22:30 need it to give me the answer of 56:00 =B1-A1 Custom format: [h]:mm "Tstewart14" wrote: > I have 4 colums on for start date one for start time one for end date and one > for end time > > I need to find a formula that will give me just the difference in the two > times they may be t...