Formula to get date from text string

This is what the cell currently looks like:

[10/01/09 11:30PM]

I would like the formula to return only: 10/01/09

Does anyone know what formula I should use? Any help would be greatly 
appreciated.

Thanks!!

Sara
0
Utf
5/17/2010 9:26:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
858 Views

Similar Articles

[PageSpeed] 34

Here's one way:

=3D--MID(A1,2,8)

though this will only work if the date is in the normal format for
your region (does it mean 10th January 2009, or 1st October 2009 ?).

A safer way might be:

=3DDATE(2000+MID(A1,8,2),MID(A1,5,2),MID(A1,2,2))

or:

=3DDATE(2000+MID(A1,8,2),MID(A1,2,2),MID(A1,5,2))

depending on the answer to my earlier question.

Hope this helps.

Pete

On May 17, 10:26=A0pm, Sara <S...@discussions.microsoft.com> wrote:
> This is what the cell currently looks like:
>
> [10/01/09 11:30PM]
>
> I would like the formula to return only: 10/01/09
>
> Does anyone know what formula I should use? Any help would be greatly
> appreciated.
>
> Thanks!!
>
> Sara

0
Pete_UK
5/17/2010 9:35:19 PM
Hi Sara

One way
=--INT((SUBSTITUTE(SUBSTITUTE(A11,"[",""),"PM]","")))

This will return the serial number of the date.
Format the cell in whatever date format you wish to see the result
--
Regards
Roger Govier

Sara wrote:
> This is what the cell currently looks like:
> 
> [10/01/09 11:30PM]
> 
> I would like the formula to return only: 10/01/09
> 
> Does anyone know what formula I should use? Any help would be greatly 
> appreciated.
> 
> Thanks!!
> 
> Sara
0
Roger
5/18/2010 9:51:40 AM
Reply:

Similar Artilces:

Formatting in the formula bar
When i type in a number into a cell in my worksheet, say: 42.99 Excel automatically rounds it to 43. Which is what i want and what i set it up to do. However, the number in the formula bar also rounds to 43. Normally i thought the formula bar stayed at 42.99 and only the spreadsheet cell rounds to 43. I am using Excel 2000. Please help asap as i need the formula bar to stay at 42.99 so i remember what the original number was!!! Hi maybe you have checked 'Tools - Options - Calculation - Precision as displayed' -- Regards Frank Kabel Frankfurt, Germany Beccy wrote: > When ...

Calculate a biweekly date
Is there a way to use a formula to calculate a biweekly pay day? For example, I want a spreadsheet that automatically puts the date for every other Wednesday throughout the year?? Automatically how? In what way, what would you do to trigger this date entry? -- Regards, Peo Sjoblom "TimT" <TimT@discussions.microsoft.com> wrote in message news:7681C94C-AB08-4141-AC45-1CD04C7788D7@microsoft.com... > Is there a way to use a formula to calculate a biweekly pay day? For > example, I want a spreadsheet that automatically puts the date for every > other Wednesday th...

Formula Help #9
Thanks Max and Ragdyer for your replies, Max I've downloaded the link you provided and had a bit of a play and it works so good I'm now going to see if I can get it to automatically fill in the sheets for the knockout rounds which I can print off and give to the marshal for lining up the cars. I'll let you know how I get on, Regards, Kieran Max Wrote: > In Sheet1- > Put in G2: =IF(COUNTBLANK(D2:F2)=0,MAX(D2:F2),"")- > > And if you want Sheet2 to display the results progressively ("live") as > the > scores are input from the very first run...

Help please with Offset/Match formula
I am struggling with a formula using Offset and Match. I have a table of data and I need to read in data into specific cells, I am using the combination of Offset and Match and everything is OK for 90% of the time. My problem is the following:- I have a number of production lines, some products can be manufactured on more than one line. Where the product/resource combination is unique then everything is fine. I need my Offset to match against more than one criteria. Example Resource Product Period 1 qty Period 2 qty etc..... Line 1 1233 1,000 1,00...

Excel ODBC driver fails to update XLS if a formula exists
Searching for an answer via Google and Bing didn't help, so I'm asking here. I'm using an old version of Visual Studio but I think the problem is in the ODBC driver for Excel. I found code on the Internet for a class called CSpreadsheet which successfully inserts columns into an empty Excel XLS spreadsheet just like I need, but when I try to do the same thing to an existing spreadsheet that contains even one formula like =SUM(A1:A12) none of the cells I try to update accept the values. The non-fatal error out of the Commit() being done says: Cannot delete spread...

macro problem. Upgraded to Excel 2002, now getting error message
Hello, this is my first time posting so please go easy on me. I am running a macro that pulls data from several files an interpolates information; the process takes about 20-25 mins. upgraded to excel 2002 and now the when I run a large input file i runs about 2/3 of the way then gives me the standard error messag saying the program needs to shut down. If i cut the file up in smalle parts the program will run through, but it's a pain stitching th output back together. I have never had this problem before when I wa running Excel 2000. Is there a setting I have to change? -Thanks :conf...

Copy cell contents, then paste into the same cell with other text.
Hi! I tried a search first and couldn't find anything like this. My spreadsheet has a column for shipping that takes a series like this for each product: ?0.0*0.13.2*d*0x0x0:07:24:04 Following the question mark is the handling charge (0.0 in this example). This is followed by an * and then the weight of the item (0.13.2 in this example which is 13.2 ounces) I have a list of product weights in a colum with just pounds and ounces. I need to copy that information, then paste it into the weight area of the string above and then paste those modified contents back into t...

Convert Euro date to Julian
I have a euro date such as 20040902 and I need to convert it to 9/2/2004. I actually was successful previously using "text to columns" and then formatting but I have forgotten. Anyone run into this? Hi what do you mean with 'forgitten'?. You find this feature in the menu 'Data - Text to columns'. for formatting goto 'Format - Cells' -- Regards Frank Kabel Frankfurt, Germany KJ wrote: > I have a euro date such as 20040902 and I need to convert > it to 9/2/2004. I actually was successful previously > using "text to columns" and then ...

String extraction
Hi How can I extract the first line form a multi-line string? Many Thanks Regards hi John, John wrote: > How can I extract the first line form a multi-line string? Use Left(yourString, InStr(yourString, Chr(13) & Chr(10))) to get the first line. mfG --> stefan <-- "Stefan Hoffmann" <stefan.hoffmann@explido.de> wrote in message news:uDOmMrJ6HHA.1208@TK2MSFTNGP03.phx.gbl... > hi John, > > John wrote: >> How can I extract the first line form a multi-line string? > Use Left(yourString, InStr(yourString, Chr(13) & Chr(10))) to get the ...

Comparing dates in columns from linked table
Hi, I'm new to Access/SQL and have a problem with a query that I just cant seem to be able to solve. So, I have an Access database containing client number, security name and date, that information has to be updated every month, the source for the update being an excel file that I link to the Access database. But I dont want to overwrite on the existing data so what i wanted to do was add the current month's data, when it's not already in there. What I did was an insert query which inserts the data from the excel file to the table in the Access database only if the c...

Access 2007 Special Effect Text Box Won't show Sunken with Window XP
Hi All I am just getting to grips with Access 2007 and one of the quirks I have found is that when I design using Windows in XP Windows-Classic colors and set the text boxes on forms' special effect property to "sunken" They appear as sunken in the correct manner. However, when I change to the Windows XP color scheme the forms text boxes appear flat even though the special effect property has not changed. Any ideas or is it a new quirk we have to live with. I haven't tried it with Vista yet. That should be interesting Thanks Bob "bobdydd" <reallyuseful2004...

get access form in excel
Hello, i've created a excel menu and i would like to call the access form i've created how could i could that? thanks i don't think you can do that. you will have to create the form in excel. sorry. >-----Original Message----- >Hello, > >i've created a excel menu and i would like to call the access form i've >created > >how could i could that? > >thanks >. > ...

separate file attachment from text body
Hi All, One of my users running outlook 2007 replies to a recipient; upon attaching a file, the attachment shows up in the text body. I notice this when replying to messages vs. starting new ones. Any thoughts or help would be much appreciated. Thank you. -mike Check their format. Rich Text usually does this. Plain text and HTML insert the attachment into it's own field. -- Kathleen Orland - MVP Outlook Outlook Tips: http://www.outlook-tips.net/ http://www.howto-outlook.com/ "Mike" wrote: > Hi All, > > One of my users running outlook 2007 replies to a ...

How to use an Excel 1997 files with line chart that have date & ti
I have many old Excel 1997 files with line graphs that show up the x axis date and time values. The old Excel 97 show about ever 10 or 20th date/time stamp in the x axis, but the new 2003 show only black spots at the bottom of the line graphs. How do I get Excel 2003 to produce a line graph to show time/scale data that has dates and times like the old Excel 97 did? ...

Exporting to a unicode text file
I'm trying to export my worksheet to a unicode text file and everytime I have a cell with a comma in it the exported file has double quotes around the text in that cell. Does anyone know how to disable this annoyance? ...

Worksheet Calculations Exclude Custom Formula
I want to exclude certain custom calculations calculating when I use the VBA worksheet calculate event (i.e. Activesheet.Calculate) on custom functions. Is this possible? Thanks in advance This seemed to work in simple testing. Maybe you can enhance it: Option Explicit Public StopCalc As Boolean Function myFunc() As Variant Application.Volatile True If StopCalc Then myFunc = Application.Caller.Text Exit Function Else myFunc = Now End If End Function Sub testme01() StopCalc = True ActiveSheet.Calculate StopCalc = False End Sub Sub tes...

Shortcut to insert a formula on many rows
Hi, I forgot how to make something and hope somebody form here can help me. For example I have 5000 rows of some random numbers in column A. In column B I want to insert a formula (any formula). How can I "insert" that formula for all 5000 rows with data at once. I know there is a shortcut for this but I forgot... :( Thanks for help! -- Vitalie Ciobanu AISBERG http://aisberg.rau.ro Hi Vitalie, Double click on the fill handle. http://www.mvps.org/dmcritchie/excel/fillhand.htm it will copy the formula down as long as there is something in the column to the left. --- HTH,...

conditional formatting on text string
Hi, I have an Access 2003 db that has a report in it. I am trying to do a conditional format on one of the fields so that if the text in the text box has the string "GCA" (w/out quotes) in it, that it will fill back color with blue, and the fore color (or the text) white. The field is named "Club Position" Looks like it can't be done using the "Field Value Is" condition, but perhaps there is an "expression Is" that I can use. Any help is much appreciated. Thanks. Conditional formatting using "Field Value Is" should work perfectly. ...

Req.: Formula
Hello, I hope if somebody can help me out with a formule: I have 3 columns, and in this example 8 rows Column A1:A8 are letters ( alphabetical), if I fill in a digit in B1,B2, B3 etc the results should be shown in in column C1,C, C3 etc. So if I fill in a in cell B1 the result in C1 should be the letter in cell A2, if I fill in the 4 in B2 the result in C2 should be the letter in cell A4 etc. e.g: A B C 1 a 2 b 2 b 4 d 3 c 8 g 4 d 3 c 5 e 5 e 6 f 1 a...

recalculates formulas when opening files last saved by an earlier.
Get message: recalculates formulas when opening files last saved by an earlier version of excel. How do I find out what and where the formulas are? Hi all formulas are re-calculated :-) -- Regards Frank Kabel Frankfurt, Germany "Robert" <Robert@discussions.microsoft.com> schrieb im Newsbeitrag news:3DAB310A-9E42-4A84-9660-28224E362FC2@microsoft.com... > Get message: recalculates formulas when opening files last saved by an > earlier version of excel. > > How do I find out what and where the formulas are? and to find out what and where the formulas are do this...

How to get the next immediately adjacent item in CTreeCtrl?
Hi, I want to loop through all the items in a CTreeCtrl, one by one from top to bottom. CTreeCtrl has the following function, but it seems that nCode doesn't provide an option for getting the next immediately adjacent item. GetNextItem( HTREEITEM hItem, UINT nCode ); Do I have to write a recursive function to traverse the treeview for this purpose (because there are multiple levels of ancestors and offspring)? If so, I will be very surprised that such a simple and useful option can be missing from MFC. GetNextVisibleItem() is very close to what I need. However, it is limited t...

Dates posted within a standard column chart
I have a regular column chart with dates at the bottom, and the columns are the # of cases made per week. I'm trying to include the dates the Case specifications were changed within the chart. I only want to have a point within the chart showing the date it was created within the dates already showing. For example, I have the dates of 10/1, 10/6, 10/13, and 10/20 on the X-axis. There are four columns per date. The dates of the specification changes are 10/9, 10/14, and 10/15. I'd like points showing on those dates with Version 1, Version 2, and Version 3 marked. Any ideas...

Outlook 2003
Is there a way to force all incoming email to be received as plain text in OL-2003? TIA -- howard How about: "Tools | Options | E-Mail Options | Read all standard mail in plain text"? -- Aloha, -Ben- Ben M. Schorr, OneNote-MVP Stockholm Consulting Group/KSG http://www.scgab.com Microsoft OneNote FAQ: http://home.hawaii.rr.com/schorr/computers/onenotefaq.htm **I apologize but I am unable to respond to direct requests for assistance. Please post questions and replies here in the newsgroup. Mahalo! "Howard Cross" <no-spam@No-Spam.com> wrote in message ...

Excel Text Function
Hi anyone who can help me... I have some info in a spreadsheet as follows: A1 B1 C1 Centra Dublin Centra Belfast Centra London If I want to get just Centra out into another cell I would use =LEFT(A1:C1,6) and this works fine. But I want to actually get out the area - Dublin, Belfast or London and some other areas that might have more or less than 7 letters. Any ideas??? Thanks in advance Ann (Dublin, Ireland) =TRIM(SUBSTITUTE(A1,"Centra","")) will work if you have city names and centra.. -- Regards, Peo Sjoblom "Ann&q...

Stagger X-axis text
In 1-2-3 I could stagger the text in the X-axis. In Excel it seems that I can only rotate the text to 90 degrees. Is there a way to stagger and leave horizontal? Specifically, I have all the provinces (or 10 of them) across the axis and they take up room when spelt out (no abbreviations allowed). I would rather the first, third, fifth ... etc. be higher and the second, fourth etc. be lower to allow the chart to be narrower and still read the text clearly. Cheers, Deborah >-----Original Message----- >In 1-2-3 I could stagger the text in the X-axis. In Excel it seems Deborah I would...