MID formula not extracting what I want.

I'm trying to extract the date from a cell using the MID formula and all
I'm getting is some weird number that does not seem to make sense.
I have tried formatting the target cell but still no change.

Any clues about it? How can I do this?


1/13/2006  12:16:29 AM

=MID(E5,1,9)

38730.011


-- 
wayliff
------------------------------------------------------------------------
wayliff's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29860
View this thread: http://www.excelforum.com/showthread.php?threadid=501062

0
1/13/2006 1:33:39 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
663 Views

Similar Articles

[PageSpeed] 35

Hi

That's because Excel stores dates as serial numbers, but displays the 
value in whatever format you choose.
To get just the data part out of Date and time, just use
=INT(A1)

-- 
Regards

Roger Govier


"wayliff" <wayliff.21ks4b_1137159301.5007@excelforum-nospam.com> wrote 
in message news:wayliff.21ks4b_1137159301.5007@excelforum-nospam.com...
>
> I'm trying to extract the date from a cell using the MID formula and 
> all
> I'm getting is some weird number that does not seem to make sense.
> I have tried formatting the target cell but still no change.
>
> Any clues about it? How can I do this?
>
>
> 1/13/2006  12:16:29 AM
>
> =MID(E5,1,9)
>
> 38730.011
>
>
> -- 
> wayliff
> ------------------------------------------------------------------------
> wayliff's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=29860
> View this thread: 
> http://www.excelforum.com/showthread.php?threadid=501062
> 


0
roger5293 (1125)
1/13/2006 1:42:03 PM
Try:

=LEFT(E5,FIND(" ",E5)-1)


HTH
JG


-- 
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=6261
View this thread: http://www.excelforum.com/showthread.php?threadid=501062

0
1/13/2006 1:45:49 PM
This will only work if your date and time is formatted as text.

If you are just trying to extract the date only do this:

=E5

and then Format Cells>Date>[3/14/2001] (or however you want the date
displayed)


-- 
mphell0
------------------------------------------------------------------------
mphell0's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30153
View this thread: http://www.excelforum.com/showthread.php?threadid=501062

0
1/13/2006 1:48:34 PM
when you type in
1/13/2006  12:16:29 AM
excel automatically converts it to a number.   In this case it is
38730.0114467593
This number represents the number of days from 12/31/1899 (1/1/1900 is 
stored as 1).

Using MID(E1,1,9) takes the first nine digits of this number (remember this 
number is the value of the cell).
to get the date portion and keep it as a date, you can take Roger's advice,
=INT(E1)
and format as a date.  If you need a text output, you can use 
=TEXT(E1,"m/dd/yyyy")

"wayliff" wrote:

> 
> I'm trying to extract the date from a cell using the MID formula and all
> I'm getting is some weird number that does not seem to make sense.
> I have tried formatting the target cell but still no change.
> 
> Any clues about it? How can I do this?
> 
> 
> 1/13/2006  12:16:29 AM
> 
> =MID(E5,1,9)
> 
> 38730.011
> 
> 
> -- 
> wayliff
> ------------------------------------------------------------------------
> wayliff's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29860
> View this thread: http://www.excelforum.com/showthread.php?threadid=501062
> 
> 
0
Sloth (218)
1/13/2006 3:52:03 PM
Thanks to everybody for their help...this is what I ended up doing in
the macro.
I guess a combo solution from everybody's input.

Range("J5").Select
Selection.FormulaR1C1 = "=MID(RC[-5],1,9)"
Range("K5").Select
ActiveCell.FormulaR1C1 = "=INT(RC[-1])"
Range("J5:K5").Select
Selection.AutoFill Destination:=Range("J5:K" & lastrow),
Type:=xlFillDefault
Range("K5:K" & lastrow).Select
Selection.Copy
Range("E5:E" & lastrow).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _        :=False, Transpose:=False

It seems to work just fine for the moment.
Now I'm having to think about the following

The current date is 1/13/2006 what if the date is 10/10/2006 1:30
Then the MID solution using 9 characters won't entirely work.


-- 
wayliff
------------------------------------------------------------------------
wayliff's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29860
View this thread: http://www.excelforum.com/showthread.php?threadid=501062

0
1/13/2006 3:57:41 PM
Reply:

Similar Artilces:

Using IF formula with dates
Hi there, Can anyone help! I am trying to create the following formula : A1 = 01.08.02 IF (A1="01.08.02","N","S") As A1 does = 01.08.02 it should have "N" in the cell which I am usin the formula in shouldn't it ? or is there something I'm missing. The dates are exactly the same format in the formula and in the A cell. Any help would be very much appreciated!! ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ~~Now Availab...

View the formula #2
Hi all, i have an templet on an excel sheet having complex calculation. i am not able to view the formulas, as the sheet has been proteceted. is there any way i will be able to view the formulas. Thanks and best regards, subbu. Google for a password protection remover. There are many of them - most are free. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Subbu" <subbu3580@gmail.com> wrote in message news:1164782719.812301.212180@l39g2000cwd.googlegroups.com... > Hi all, > i have an templet on an excel sheet having complex calcu...

Formula update automatically
Please look into the formula & suggest. The excel sheet is fille-in as below: Column A B C D Row 1 2 6 5 Row 2 20 30 90 D2=A$1*A2+B$1*B2+C$1*C2 Row 3 2 4 8 D3=A$1*A3+B$1*B3+C$1*C3 & so on for further rows. What I need is, if a column is added between, say, B & C, then th formula in D column should update for the added column & similarly fo deleted column. Is there a way? I would prefer excel formula solution. If code is the only solution please write the complete code. The undo button should not beco...

Count if formula and subtotals
Hi Have two columns of data, for example: Dept Personnel 1 60 2 78 3 35 3 45 2 23 3 24 4 67 2 23 1 23 2 34 3 34 I have already placed the formula =count if(A1:a1000, "1") and so on at the base of column A to count occurences of departments. At the base of column B I wish to have a calculation that totals the amounts of column b (personnel) that correspond with each dept (i.e. something to the extent of if looking at number of personnel corresponding to department 3, the result of the formula should be 138! Any help appreciated! Hi One way is to...

formulas #16
how do I use a % range for instance if f13 is 25-75% in a formula =IF(AND(F13>=25%,F13<=75%),"yes","no") -- HTH Bob Phillips (remove nothere from email address if mailing direct) "las" <las@discussions.microsoft.com> wrote in message news:F912E1EA-9AC9-40A9-9DF5-1F18D93C4694@microsoft.com... > how do I use a % range for instance if f13 is 25-75% in a formula Bob I have been working on this for hours...thank you for you assistance it worked! las "Bob Phillips" wrote: > =IF(AND(F13>=25%,F13<=75%),"yes","...

mid function in ms query
Hello, i know that the funtion mid works in Excel. i need to use this function in Ms Query, (get external data, and edit my query there). is there any similar function Thank you Suhair Suhair, Try substr(string,startpostion,length) eg. substr(mystring, 1, 4) It's a bit of a shot in the dark but it might work. "suhair" wrote: > Hello, > i know that the funtion mid works in Excel. > i need to use this function in Ms Query, (get external > data, and edit my query there). > is there any similar function > Thank you > Suhair > ...

Cell reference in formula as range
I have a formula in my spreadsheet that looks like: =IF(D11=0," ",IF(C11<D11,"Not Enough Hours",INDEX('PF Kintanas Pivot Tables'!$A$403:$A$418,MATCH($D11,t(E1),-1)))) In cell E1 I have the following text: gtalbo In the PF Kintanas Pivot Tables tab I have a named range with the name: gtalbo When I try to use the above formula I get the #Value! error. If I substitute the actual name of the range in the formula it works: =IF(D11=0," ",IF(C11<D11,"Not Enough Hours",INDEX('PF Kintanas Pivot Tables'!$A$403:$A$418,MATCH...

=mid function
Cell A1 contains 1,274 characters. My function is: =mid(a1,xxx,12) Is there a way to determine the position of the first character in cell A1, besides physically counting over 'xxx' characters? Don't really understand your question. The position of the first character in A1 is "1". =Left(A1,12) - BUT - I'm sure that's not what you're looking for. Is it ? Do you want to put the value xxx in a cell B1 and use that ? =MID(A1,B1,12) You want to return 12 characters starting at position xxx. You have to have something to identify or mark that particular sta...

Want to copy / backup just contacts
What is the file name where Outlook stores the contacts? And where is it stored? All Outlook data is stored in the same file. The file you need to back up is your Personal Folders file (*.pst) It's where all the mail, calendar, contacts etc are stored. Take a look at these pages for info on Outlook data backup: http://www.slipstick.com/config/backup.htm -- Russ Valentine [MVP-Outlook] "dave" <anonymous@discussions.microsoft.com> wrote in message news:0d2101c3b2ab$9182b540$a401280a@phx.gbl... > What is the file name where Outlook stores the contacts? > And wher...

Formula Question
I need to do some type of lookup and sum in vba I'd like to use Total Sheet.B5 to return the value Total Sheet.B5 to = 55 if Total Sheet.A5 = Orange Total Sheet A5 = Orange B5 = 55 Sheet 1 A4 = Blue b4 = 5 A5 = Green b5 = 10 A6 = Orange b6 = 15 Sheet 2 A4 = Orange b4 = 10 A5 = Green b5 = 20 A6 = Blue b6 = 30 Sheet 4 A4 = Green b4 = 20 A5 = Orange b5 = 30 A6 = Blue b6 = 40 Thank you for any help Hummm, I can't really tell what you're doing, but look here: http://www.xldynamic.co...

Formula: Search for Dates and Sum values
Good day, I have the following data sample: Dates Risk Profits 16-Feb-04 Certain 0.0060 16-Feb-04 Uncertain 0.0060 16-Feb-04 Certain -0.0030 19-Feb-04 Certain 0.0060 20-Mar-04 Certain 0.0060 21-Mar-04 Certain -0.0040 22-Mar-04 Certain 0.0060 22-Mar-04 Certain 0.0060 22-Mar-04 Certain 0.0060 25-Mar-04 Certain -0.0040 08-Apr-04 Certain 0.0060 09-Apr-04 Certain -0.0040 1- I would like to be able to get the "Profits" average per month. So how can I SUM the values from the 3rd column for an entire month? 2- Also, per month,...

Cannot find and delete an old named formula
Version: 2004 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I have recently saved a workbook as a new file, under a new name. Every time I save the file I get the following message: <br><br>&quot;A formula in a cell (Name: Telecom) could not be converted because it contains a function that is not available in the file format to which you are saving.&quot; <br><br>When I first saved the file there were several named cells identified that I was able to find and delete, and this last one (Telecom) has been deleted using the name define list but t...

Creating Formula
To All I am using Excel and OLE to generate a maintenance checklist spreadsheet , the spreadsheet is then downloaded to a palm device for field technicians to complete - only a date is required to be entered , what I would like is when the date cell is entered the current date is entered automatically ( saving the tech on entering a lot of dates on the small palm screen ) Something like this oSheet:Cells(nRow,"H"):Formula := "=FillDate()" Can someone assist me with an Excel formula to do this and how I associate it with a template sheet. Thanks for any help Colin ...

copy values generated by conditional formula in one sheet to the other work sheet as values
Hi Everybody, I have data generated by conditional formulae in work sheet1 in columns A to J. If the condition is satisfied the cell will display a realnumber, if the condition doesn't satisfied the cell will display the text"FALSE". Now I wanted to copy the cells which have the real numbers in sheet1 to sheet2 as values(as we do with paste special and paste the values) Do we have any formula or other method to copy the cells in sheet1 to sheet2. can anybody helpme out in this issue. Thanks and Regards Ramana Hi Bobby One way Data>Filter>Autofilte...

Formulas #26
How do I set up a formula that will deduct from a beginning balance and keep an ending balance as I add items to be deducted? One way is to use the SUM() function for your total (ending balance) and enter the amounts to be deducted as negative values by using a minus sign or enclosing them in parentheses. You could also build a formula such as =A1-A2-A3-A4, etc. Other ways are also possible. Hope this helps |:>) "Theresa" wrote: > How do I set up a formula that will deduct from a beginning balance and keep > an ending balance as I add items to be deducted? > ...

I want to change the series shown in a chart by using a dropdown b
I have 4 series on a graph and I would like to be able to change which series is displayed (i.e. 1 of 4, or 2 of 4, 3 of 4, or all 4). I would like to do it by drop down menu if possible. Is this possible? Send me a sample file and I will show how. Remove TRUENORTH -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jamie" <Jamie@discussions.microsoft.com> wrote in message news:04DDBC1C-0558-4A89-83B7-517F7F6F404C@microsoft.com... >I have 4 series on a graph and I would like to be able to change which >series > is displayed (i.e. 1 of 4, or ...

Advanced Vlookup Formula
Hello, I have a list of unique values in Column A in sheet 1. In sheet2, I am have data laid out in a table with row headers and column headers. I am trying to come up with a formula that will display the value that intersects in sheet 2, based on the combined column header and row header. So for example. Sheet 2 Column Headers Row Headers State City Weight Jeff TN Nashville 200 Tim FL Miami 155 Eric GA Atl 225 Sheet 1...

Me and Mid
Hi guys, I'm having some issues with Mid, I was trying to remove 22 from cases that start like this 22###-* , I have query that is already doing this but I would like to remove it as the user enters case number and not after. These cases usually look like this 22060-01531-01 , or 22060-01531a, or 22060-01531 or 22060-01531a-04 but they also could be like this 060-2350a or 060-2350 or 0622-CC00215 or 0622-AC00218-03 What I do now is strip suffix, like -01, -02 etc. on the end using this: Dim strlen As Integer strlen = Len(CaseNo) If Mid(CaseNo, (strlen - 2), 1) = "-" Then Me...

cells with formula fire worksheet_change when navigating thru them
hi, i have cells that contain diff. formula and the first time i enter any one of them then leave it (eg. use arrow key to scroll across columns in a given row) the worksheet change event is fired. is this a bug because nothing has changed in the cell (perhaps some recalculate event has fired?). the reason the above noted behaviour is a problem is because in my worksheet_change event i execute an Application.Undo and this line of code throws an error because nothing there is nothing to undo because nothing has actually changed. i don't know how to test for whether there...

Disabling formulas and turning file into text only
I have a workbook in which there are formulas that pull through from other workbooks. I want to make the workbook so that it doesnt ask me to update the formulas everytime and make it so that only the values show up(formulas are no longer needed once computed) Is there a way to do that besides copying and pasting as special? Not 100% sure, but I think you can do this by going edit/links/break links. >-----Original Message----- >I have a workbook in which there are formulas that pull through from other >workbooks. I want to make the workbook so that it doesnt ask me to updat...

problem with MID()
I am trying to use MID to extract teh first 2 numbers of a 4 digit number that is in a cell. The problem is, the number sometimes has a "0" as the first digit and when this happens, MID() ignores the 0, and reads the 2nd and 3rd character. So: Cell A1 = 0123 Cell B2 =MID(A1,1,2) Returns 12 instead of 1 How can I fix this? I need tehm to stay numbers so I can use them in mathmatical operations in another formula. I thought I had teh problem solved by formatting the cell to "Number ->custom 0000" TIA Adam One way =TEXT(MID(A1,1,LEN(A1)-2),"00") ...

IF Formulas #2
Hi All Im having a problem, I have the following formula: =IF(D5="Standard Soft Start & optimiser","25",IF(D5="mechanica press","15",IF(D5="injection moulding 1","15",IF(D5="injection mouldin 2","15",IF(D5="conveyor_on","15",IF(D5="conveyor_off","20",IF(D5="scre compressor","10",IF(D5="reciprocating compressor","5","Error")))))))) All works fine, BUT it wont let me put any more IF commands in there I need to do another...

Index formula
I am trying to build a index for lack of a better name. I would like to build a list several of the itmes "cells" at the beginning of a spreadsheet . When these items "cells" are double clicked, it would take you to the corresponding cell on the spreadsheet. The index cell would have the exact same information as the cell it would be looking for. I am continually adding rows to this spreadsheet so the row # would change as added. This may not be possible, but I have seen you guys do amazing things with excel on here. Thanks and keep up the great work!!!!!!!! John ...

Shorten Formula
Can someone help me shorten this formula? I'm not sure if the mathamatics can be shortened by altering the formula or using a different formula to figure out the problem, but the path name makes it exceed the max amount of charachters. Changing the linking files path location is not an option. I know I can achieve it by putting different formulas in a couple of seperate cell's but I'm trying to get the final result by only using one cell, I know the path name can be shortened by using '[pn] instead, but the name of the spreadsheet changes every month, so I can't easily find...

Lookup formula #6
Can I do 'n Vlookup and then Hlookup in one formula? A B C 1 North South 2 Oil 1.00 2.00 3 Gas 3.00 4.00 4 5 6 7 Gas 8 North 'Answer in Cell A9 below: 9 3.00 <<< Contains formula =INDEX(B2:C3,MATCH(A7,A2:A3,0),MATCH(A8,B1:C1,0)) "Esrei" <anonymous@discussions.microsoft.com> wrote in message news:16a101c536bf$1d735bc0$a601280a@phx.gbl... > Can I do 'n Vlookup and then Hlookup in one formula? ...