combine text & date formula to show mmmm dd, yyyy?

How do I combine text and date formula to show the written date and not the 
Excel number date? I have text in a couple of cells and a date formula in 
another cell(input from a different worksheet). I have the formula  =A3 & F3 
& A4 in A1 and would like it to read: We went to the station on December 21, 
2009 and took the train. However, it is reading We went to the station on 
40168 and took the train.
Thanks.
0
Utf
12/22/2009 3:58:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
665 Views

Similar Articles

[PageSpeed] 31

Try something like this...

A1 = Today is
B1 = 12/21/2009

=A1&" "&TEXT(B1,"mmmm dd, yyyy")

Returns: Today is December 21, 2009

-- 
Biff
Microsoft Excel MVP


"htm_temp1" <htm_temp1@discussions.microsoft.com> wrote in message 
news:A65BDEFE-181C-451C-B676-B4B1C933E5EF@microsoft.com...
> How do I combine text and date formula to show the written date and not 
> the
> Excel number date? I have text in a couple of cells and a date formula in
> another cell(input from a different worksheet). I have the formula  =A3 & 
> F3
> & A4 in A1 and would like it to read: We went to the station on December 
> 21,
> 2009 and took the train. However, it is reading We went to the station on
> 40168 and took the train.
> Thanks. 


0
T
12/22/2009 4:13:48 AM
=today() in Cell A1
="We went to the station on "&TEXT(A1,"mmmm dd, yyyy")&"and took the train." 
in Cell C1						


-- 
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"htm_temp1" wrote:

> How do I combine text and date formula to show the written date and not the 
> Excel number date? I have text in a couple of cells and a date formula in 
> another cell(input from a different worksheet). I have the formula  =A3 & F3 
> & A4 in A1 and would like it to read: We went to the station on December 21, 
> 2009 and took the train. However, it is reading We went to the station on 
> 40168 and took the train.
> Thanks.
0
Utf
12/22/2009 4:35:01 AM
You need the Text function to convert the date to the format you want. Use:
=a3&text(f3,"mmmm dd, yyyy")&a4

Regards,
Fred.

"htm_temp1" <htm_temp1@discussions.microsoft.com> wrote in message 
news:A65BDEFE-181C-451C-B676-B4B1C933E5EF@microsoft.com...
> How do I combine text and date formula to show the written date and not 
> the
> Excel number date? I have text in a couple of cells and a date formula in
> another cell(input from a different worksheet). I have the formula  =A3 & 
> F3
> & A4 in A1 and would like it to read: We went to the station on December 
> 21,
> 2009 and took the train. However, it is reading We went to the station on
> 40168 and took the train.
> Thanks. 

0
Fred
12/22/2009 5:17:30 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 ...

Font set for reading plain text
I've set Courier as the font to read and compose plain text message. However, I don't have idea why my Outlook showed with Arial rather than Courier. The message was from Yahoo account. It seems no difference where messages came from. Can Outlook show plain text message with the font that I set? Thanks, Charles OL2003 SP2 on XP Pro SP2 ...

formula change problem #2
The columns that the formula is referencing can change locations on th spreadsheet, if a user inserts a new column. To simplify the questio I put the actual column numbers in the formula but actually I will hav an integer defined that will hold the column number. That integer wil get populated by grabbing the column number from a named cell in tha particular column. I hope I explained that good enough. I was using the offset's in the formulas because that is what I am mos familiar with. Can offset's not be used in formulas? Is there a better way? Thanks -- cparson ---------...

How do I copy data from one workbook to another whilst retaining formulas on original
I'm looking to run a macro from workbook SWT (sheet CLT) which will cu and paste data from a range of cells (A2,C2,E2,F2,G2,I2,K2.. A3,C3,E3,F3,G3,I3,K3... A4,C4,E4,F4,G4,I4,K4.... until end of data from workbook SWT (Sheet CS) to rows A to G in Workbook SWTS (Shee Stats). I have vlookup formulas (eg =IF(B2="","",VLOOKUP($B2,'Data Field'!$A$7:$B$12,2))) in place in columns A,C,E,F,G,I,K on sheet CS and I'm looking to keep the these formulas in place (unless someone ca think of better alternatives). When I paste the data on sheet Stats i must paste onto the...

excel spreadsheet 2003 wont show content when moved to another par
i have backed all documents which included excel spreadsheets from c drive to d drive (ANOTHER PARTITION). Now i cannot see the content of the spreadsheet when i access it from the D drive or C drive. what have i done to it? It also wont open when itry to access it from backed up USB stick. Thanks Chris I had a similar issue with some files. What happened was that somehow the windows for the files got minimized or dragged out of view somehow (not sure how this happened). Users would open a file and see nothing. This fixed it for me. Goto the Window item on toolbar, S...

Text Box Control contains generated hyperlink
Hi. I have a textbox control on a report that contains a generated hyperlink. I output this report to a PDF and when I click the link it only returns the first line of information. This link contains links to documents that reside in a database, so if the link should contain 10 documents, it only returns 8 because that's what fits on the first line of the link. Any ideas of what is happening? Cathy ...

Dates in XIRR
I was just looking at http://msdn2.microsoft.com/en-us/library/bb224771.aspx, which states, in reference to the XIRR function: "Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text." Is this something new in Excel 2007? I've never encountered such a problem with earlier versions of Excel On Apr 27, 3:07 pm, "Howard Kaikow" <kai...@standards.com> wrote: > Is this something new in Excel 2007? No. The H...

An issue regarding the formula
Hi, It's OK to copy the formula from one cell to another one within the same sheet. How about that to copy the formula from one Excel file to another one? It seems only the value of that cell will be copied to that file but not the formula inside it. I even have tried to copy the corr. formula within that again but just* the copied formula will not work successfully in there. * Thks & Rgds, HuaMin -- HuaMin ------------------------------------------------------------------------ HuaMin's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29423 View this t...

Sheet tab text color
...

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

CCTV text insertion??
Is there anyone using text insertion from RMS? I need a way to insert text most times this is done off receipt printer or off a com port. The reason i dont like using a receipt funtion is rms dumps all the info at the end of the transaction i would rather have it while the cashier is ringing up that item. I would use it as a pole display but not everything is sent to the pole display. JUst wondering if anyone else was using text insertion on a rms system. thanks for the help Nope right now only way is printer. "Doug Pic-N-Pac" <DougPicNPac@discussions.microsoft.com> w...

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 text to Word text
How can I copy Excel text, then paste it into MS Word without carrying over the grid lines? Any help would be appreciated. Thanks Ed English I don't believe you can copy and paste without the gridlines. However, in Word, select the data and Table>Convert>Table to Text. -- Greeting from the Gulf Coast! http://myweb.cableone.net/twodays "Ed" <ee15803@att.net> wrote in message news:3f7e21e9.53324199@netnews.worldnet.att.net... > How can I copy Excel text, then paste it into MS Word without carrying > over the grid lines? Any help would be appreciated. Thanks &g...

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

Convert Incoming Plain Text to HTML
Is there a way to have Outlook (2003) automatically convert incoming Plain Text messages to HTML without manually changing the formatting option? Thanks in advance, Jody As far as I know, no. You can go from HTML to Plain Text but not the other way. "J. Owens" wrote: > Is there a way to have Outlook (2003) automatically convert incoming Plain > Text messages to HTML without manually changing the formatting option? ...

Wildcards with Array Formulae
Hi, I'm looking for some help with a formula I'm using to pull statistic out of a client spreadsheet. The formula uses the SUMIF command to loo at one column, check for a text value then look at another colum within the same row and check for another text value. It returns value of one if both are true and a value of 0 if one or none are tru then keeps a total. It's a pretty standard array formula, but I need i to use wildcards because the second column can contain value A, value or value AB. I want the formula to pick up all instances where value appears, including where it a...

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

Why does Outlook display text in yellow? How can i change it?
When pasting text into an e-mail, it appears in pale yellow. Sometimes, parts of meesages from others are also yellow. How can I prevent this? Are you using stationery by any chance? -- Kathleen Orland "Wise Dog" <Wise Dog@discussions.microsoft.com> wrote in message news:2D2FBAF7-2B2B-4C66-AE33-B2CA5A51B821@microsoft.com... > When pasting text into an e-mail, it appears in pale yellow. Sometimes, > parts of meesages from others are also yellow. How can I prevent this? ...

Split combined date time data #2
From file dump have combined date time cells eg "14/04/03 14:20" (value 37725.59722). Want to perform time analysis so need to split to 2 separate cells; eg. "14/04/2003" (value 37725) & "14:20" (value 0.59722). Is there a fnc to do this? (Currently convert cell to value, then fncs trunc & cell less int(cell) then refmt to date dd/mm/yy & time hh:mm respectively) See one answer in reply to your slightly later post. In article <EDD7065A-E5AA-40AB-BF38-50707E04109B@microsoft.com>, "Mark Ada" <MarkAda@discussions.microsoft.co...

Creating XML Text
Hello, I need to create some well formed XML text live in memory. What I really want is to use the XmlTextWriter object applied to some location in memory (Stream or something?), because it does a nice job of formatting/identing, etc. But I can't figure out how to instantiate the XmlTextWriter object correctly w/o a filespec. I'm not allowed to directly instantiate a Stream & therefore a StreamWriter for that matter since there's no filespec involved. Can someone show me some code to make this work? TIA, -- John C. Bowman Software Engineer Thermo Electron Scientific I...

Conditional formula with an if statement
I am wanting to create a "envilope" budget sheet and have ran into a problem. What I am trying to do is make everything in Column B all negative unless I put a figure in Column A, which would make the Column B Positive. Example: Column A Column B Deposit Car (nothing here) $50 (this will show up negative) $500 $500 (This figure will show up positive) I want to be able to add numbers in both column A and B but reflect the appropriate sign as an expense (with nothing being added into deposits) or as income, when ...

Can't paste text to a document because of macros
When I open a document in Word 07, and try to copy text to it it, I get a message that macros are disabled and to go to help for instructions. Instructions say to close the doc. and reopen with macros enabled. BUT it doesn't say HOW to do that. And, why is this happening all of a sudden and everytime I open a doc.? If this happens with all documents in Word, it's probably being caused by poorly designed code in an add-in (or in the Normal template). Did you install something recently? -- Stefan Blom Microsoft Word MVP "leslie7" <leslie7@discus...

counting embedded text #2
If I format the information with "Valid" or "Not Valid" first followe by an explaination, it works. The other option is to separate th status from the explaination. IE: have a separate column called vali and put a yes or no and count those. I only have about 45 entries s far, so it isn't that big of deal. So far, using the wildcard seems to be working -- Shocke ----------------------------------------------------------------------- Shocked's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1489 View this thread: http://www.excelforum.com...

how can i copy formula?
hi, please help! my problem was i create a formula that gets the total sum from another workbook. i want to copy the formula and past to another cell but the only thing must change is the name of the workbook. example. i want to paste the formula of A1 to B1 and so on which autoamtically link to another workbook. Workbook1. sheet1 A1=sum of workbook2 B1=sum of workbook3 C1=sum of workbook4 Hi Into A1 enter the formula like ="_=SUM('C:\My Documents\[Workbook" & COLUMN()+1 & ".xls]Sheet1'!A1:A100)" Copy the formula to right. Select all formulas, copy ...

Missing text in email
I am having a problem with Users complaining of email missing the text when it was there originally. Also some have gotten emails back from people saying what they sent was blank, then when they check their sent items... sure enough it is blank!! Running Exchange 2003 on Windows 2003 with latest Service Pac Clients are Outlook 2000 with Latest Patches, etc Changing settings as to how the emails are formatted changes nothing (text/rich text/HTML Any help would be...... helpfull What AV are you running? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion ...