Excel extract date into cell

I am using Excel 2007 for importing data from another program.  Column
C always has the date in this form:
3/24/12

I would like a new column to show the following (without the quotes):
"Report for Saturday, March 24, 2012"

I tried this (but I am getting an error message):
="Report for " & date(c2)

Please advise.




0
3/24/2012 3:48:47 PM
excel 39879 articles. 2 followers. Follow

2 Replies
1809 Views

Similar Articles

[PageSpeed] 9

On Saturday, March 24, 2012 10:48:47 AM UTC-5, Tony O wrote:
> I am using Excel 2007 for importing data from another program.  Column
> C always has the date in this form:
> 3/24/12
> 
> I would like a new column to show the following (without the quotes):
> "Report for Saturday, March 24, 2012"
> 
> I tried this (but I am getting an error message):
> ="Report for " & date(c2)
> 
> Please advise.

try 
="report for" & text(c2,"mmm dd, yyyy")
0
dguillett11 (103)
3/24/2012 4:19:11 PM
On Sat, 24 Mar 2012 08:48:47 -0700 (PDT), Tony O <tonyrulesyall@yahoo.com> wrote:

>I am using Excel 2007 for importing data from another program.  Column
>C always has the date in this form:
>3/24/12
>
>I would like a new column to show the following (without the quotes):
>"Report for Saturday, March 24, 2012"
>
>I tried this (but I am getting an error message):
>="Report for " & date(c2)
>
>Please advise.
>
>
>

If you custom format the cell in the new column:

NewColumn:  =C2
Format/Cells/Number Custom Type:  "Report For " dddd, mmmm d, yyyy

This has the advantage of still being able to use the date in numeric formulas.
0
ron6368 (329)
3/24/2012 6:05:55 PM
Reply:

Similar Artilces:

Excel
Text pasted into a cell from Word or other software will keep the formats including carriage returns, line numbers, tabs, etc. Can text be typed into a cell with control characters that will force this type of formating? -- mwaller If I understood your question correctly - you might try hitting Alt+Enter in order to go to a new line in the same cell Micky "mwaller" wrote: > Text pasted into a cell from Word or other software will keep the formats > including carriage returns, line numbers, tabs, etc. > Can text be typed into a cell with control charac...

x-y plot in Excel 2007 questions
I am trying to make an x-y plot and the axis that excel uses are not what I want. I am comparing wages to 3 different outputs. I want wages on the y-axis and output on the x-axis, but excel does the opposite. I went to "select data" and did "switch row/column" and did not get the results that I am wanting. So my question is, how do I tell excel what I want on the x-axis and y-axis? The simplest way: You put the x-values in one column and the y-values in a column to the right. You make the chart from this data Or with the 'wrong' chart.Right click and use...

Copy empty cells from one workbook to another
Hello, Sorry for posting 2 times. Date & time in my computer were not correctly set. In addition to what I've written before, here is the code I wrote: Sub Dataacquire() Workbooks.Open "data.xls" Worksheets("sheet1").Range("b1:d19").Copy _ ThisWorkbook.Worksheets("sheet1").Range("b1:d19") ActiveWorkbook.Close SaveChanges:=False End Sub My problem that empty cells are not copied as empty and because of this I get an error messages in other worksheets (like #VALUE). How Can I solve this problem ? Thank you David Cohen ...

How do I convert an existing MS Excel worksheet tracking a simple.
I am a novie Excel user who has tracked the activity of my simple savings account with an Excel worksheet. I would like to set up this existing worksheet to perform autosum functions when I enter deposits, withdrawals, and interest instead of manually doing the math on a calculator to arrive at the balance total after each entry. Hi, NOTANYJOE; If A2 contains your first deposit, and columns B and C are withdrawals and interest respectively, in D2 enter this: =A2-B2+C2 ( Or if you're using negative numbers for withdrawals: =A2+B2+C2 ) In D3 enter this: =A3-B3+C3+D2 ( Or if you're ...

How to export specific emails to excel?
I am stuck at a problem where I want to export emails with specific "TO" ( can be a Distribution List). And I expect the excel to have the email address of the sender and the time at which the email was received. Is there a way to do this? I have looked on several forums and sites, but unable to find something like this. My ultimate goal is to track the emails coming in my Microsoft outlook sent to me Distribution List and export them in an excel sheet. The objective is to look for the number of emails recieved and the time/date at which it was received.. I a...

excel needs feature to delete blanks when autofilter is used.
I frequently import large data files in which data overflows the column creating blank lines plus there are also blank/incomplete data records. Deleteing the blanks involves turning on autofilter, selection that column, scrolling to the bottom of data in the list box to the "blanks" and selecting it. then selecting all the row numbers that is lists, deleting the rows, then hitting show all data again. Bob I know this is not the case all the time, but bear in mind for completely blank rows, you can sort the data and the blanks will sort to the bottom -- HTH Nick Hodge Microso...

converting Excel 2007 Charts to PDF
User reports that he has 4 charts created in Excel 2007 (line charts); Has issue with one of the charts when "save as PDF file". The colored background won't show up; the data series show up but no data line displays; Some data is missing. Has anyone else run into this issue? ...

How to open newer XLS files in Excel Version 4.0
I'm using Excel version 4. Can someone point me to an add-in or extension or something to let me open newer files ... at least Excel 95 or Excel 97? Thanks, Nick "Nicholas Fitzpatrick" <nfitz@sentex.ca> wrote... >I'm using Excel version 4. Can someone point me to an add-in or extension >or something to let me open newer files ... at least Excel 95 or Excel 97? You must have a very unconventional view of how Microsoft operates. You need to use a newer version of Excel or some other spreadsheet that opens newer Excel files. There are some inexpensive alternati...

Excel 2003: How to make transparent columns in Excel chart?
If you create a bar plot froma given dataset you can format the columns by right clicking and choosing the desired options. In the tab that opens there is a slider which is supposed tho set the level of transparency of the column (selceted area). But so far i couldn't find a way how to use this slider. I know that there is an alternative way to get transparent bars by creating a rectangular object formating it and the use copy -> paste. But i wonder what is the slider for if you can't use it? Does anybody know have an idea? Cheers, Thomas ...

Excel instance in Task Manager
I created instance of the Excel.Application as following On Error Resume Next Set objExcel = GetObject (,"Excel.Application") If err.number > 0 then CreateObject("Excel.Application") End if But the problem is that on closing of my VB application, I m terminating the excel instance as follow Set objExcel = Nothing But despite closure of the VB application, the excel instance remains in the Task Manager. After if I open any other work from Explorer or from my VB Application, the workbook dont become visible. Any suggestions... -------------------------------------...

PivotTable
Hi, I have a PivotTable with two columns - student names and tuition. I highlighted the **grand total tuition** with fill color. Please note that only ONE cell had this format. However, when I changed the column of student names to a row of student names (by moving the "button"), all the tuition (and not just the grand total) had the fill color. Is this a bug? Is there a way to get around this? I know I can remove the fill color first, change from column to row then put back the fill color. Just wondering if there is a better way. Appreciate advice. Epinn ...

Combo box value does not update in Excel 2007
I inserted a combo box form control in a cell and found problems with changing the value. Another cell on the worksheet contains a formula using the value in the combo box. When I selected a new value in the combo box and recalculated the formula it used the old value in the combo box rather than the new one. I noticed that when I used the arrow keys to navigate back to the cell containing the combo box the formula bar showed the old value, even though the combo box in the cell showed the new value. Am I doing something wrong, or is Excel not behaving as it should? bum...

Is there any way I can import a report design from excel?
Hi My client has designed his Delivery Notes in excel. Is there any way I can import this into access as a report design? Stapes No. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Stapes" <steve.staple@gmail.com> wrote in message news:64a0bb8a-98f5-4cb8-b436-c0cff3ff65c4@s8g2000prg.googlegroups.com... > Hi > > My client has designed his Delivery Notes in excel. Is there any way I > can import this into access as a report design? > > Stapes ...

why csv fields converted into dates
Hi I have a csv file with fields that look like this ,"20-10", this is being interpreted as a date when i open it in Excel. what do i need to do with this filed in order to make Excel consider it a text? I tried replacing the double quotes with a single quote like this: ,'20-10', but that makes the single quote appear in the cell in Excel, which is not what i want. please help. thanks hilz wrote: > I have a csv file with fields that look like this > ,"20-10", > this is being interpreted as a date when i open it in Excel. Hmm, that&#...

Contiguous cells are blank
Excel 2000 I have a spreadsheet with four columns: July, August, September, October. Various cells have a number in them, some are blank. When all four cells in a row are a blank, I want to delete that row. So, if row 10 for July, August, September and October are blank, I want to delete that row. How do I do that? -- Howard Hi one way: http://www.cpearson.com/excel/deleting.htm#DeleteBlankRows -- Regards Frank Kabel Frankfurt, Germany "Howard" <DFM@discussions.microsoft.com> schrieb im Newsbeitrag news:668B7816-587C-45D4-8840-CD701F379CB5@microsoft.com... > Excel 200...

Date
Hello, I am tracking the response from suppliers, the status is: "On time" or "Late". [D3Date] is I received the response from suppliers. =IIf([D3Date] Is Null,IIf(Now()-[issueDate]>1,"Late"," "),IIf([D3Date]-[IssueDate]>1,"Late","On Time")) When I issue a ticket, shold be reponse in the following work date, if not is "Late", But if I issue on Friday, then the following work date is Monday, then the expression doesn't work right, how can I avoid the Staturday and Sunday. Thank you! ...

Select a certain number of cells in a row
Hi, I'd like to have the macro to select row 5 to 10 in the active column. May I know what is the VB code to write? Regards, Valerie maybe... dim myRng as range dim myCol as long with activesheet mycol = activecell.Column set myrng = .range(.cells(5,mycol),.cells(10,mycol)) end with myrng.select ======= or with activesheet .cells(5,activecell.column).resize(6).select end with I'm not quite sure why you want to select that range. But for the most part, if you act directly on the range (and avoid .selects), you're code will work faster and be easier to modify. Dolph...

rounding to nearest hundred dollar in Excel
User wants to round up to nearest $100 dollar figure if it's over 50, or down to nearest $100 figure if it's under 50. For example, if they enter dollar figure of $1234.00, they want it rounded down to the nearest $100 dollar amount, which would be $1,200.00. Thanks. 50000? Since you say round up to nearest 100 if over 50 shouldn't 1234 round up to 1300? Or did you mean 50000? Regardsless round to nearest 100 is ROUND(x,100) round up is CEILING(x,100) so you can set it the way you want by testing the condition =IF(x>50,formula1,formula2 Regards, Peo Sjoblom "Diane&...

Problem with Excel 2003 and 2007 on same computer
I've always used the Office 2003 programs. But recently I decided to switch Outlook to the 2007 version. In the process. Office 2007 also installed the 2007 versions of Excel, Word, etc. So now I have both Excel 2003, and Excel 2007 on my Windows 7 home premium computer. I don=92t care at all for the ribbon and other features of Excel 2007. But occasionally, and seemingly randomly, my opening an old Excel file brings up 2007 Excel I know there=92s a setting to create an association between .XLS, .XLT etc.. and a particular program, in this case Excel 2003. But in Windows 7, what woul...

Excel 2007 spreadsheet saved as DBF4
Just converted to Windows 7. My Excel spreadsheet .xls can't "save as DBF4" I was using Windows XP and had no problems. Help please. Mary, This has nothing to do with your version of Windows but with your version of Office (the two are not related) A Google search with "excel DBF converter" go many hits best wishes -- Bernard Liengme http://people.stfx.ca/bliengme Microsoft Excel MVP "Mary" <Mary@discussions.microsoft.com> wrote in message news:74C9B53F-2BE4-4A86-8717-1E79690A6493@microsoft.com... > Just converted to Windows 7. My ...

Word 2007 how to get data from forms to Excel
I have forms that have been filled out by various people and would like to export the data into an Excel worksheet. This seemed straightforward in Word 2003, but I cannot get it to work in Word 2007. The actual form is a Word 2003 document. When I save the file as a txt file (after Word options / Advanced / Preserve fidelity / check "save form data as delimited text file") it creates the delimited file, but it gets overwritten when I close the original filled form. Any ideas? Thanks. I am having a similar problem, but the "save form data as delimited text...

Smartlist is opening excel multiple times in version 9
In version 8, you export a couple things from Smarlist, it created multiple workbooks within the same sheet, so that you could flip between them by clicking on Window. In version 9, it seems to create mutliple worksheets and also seems to open excel multiple times, so we cannot flip between them. We are using Citrix. ...

Macro to add name on a cell based on the data
Hello, I am using Excel 2003 and I need help to do a macro. I copy and paste data into a worksheet call "Table" In the worksheet I want to put a specialist name in the column called "Specialist" beside a column called "STATE" the specialist name will be based on what the state is. For example If the state contains "CA" I want the specialist name to be "Anna" or the state of "OR" I again want the specialist name to be "Anna" Now if we get a different state "UT" I want the specialist name to be "...

Excel question #15
When using the scroll bar in Excel the screen blanks out - why? Might be a slow video card. Since Excel 2002 the screen is scrolled real time when you drag the scrollbars. Slower video cards might not be able to keep up. -- Jim Rech Excel MVP "Battlespace" <Battlespace@discussions.microsoft.com> wrote in message news:34DD582C-0BFD-40F7-B555-951CFF65B7C8@microsoft.com... | When using the scroll bar in Excel the screen blanks out - why? ...

copy and paste excel spreadsheet into autocad
My autocad drawing is linked to a excel spreadsheet. For fast updating. But the window displayed in autocad changes if I have more than on excel sheet. As I switch from auotcad to excel is there a way to permently save the excel window display and make another window display and alway keep the window the same as you switch from one software to the other ...