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...o&userid=29860
View this thread: http://www.excelforum.com/showthread...hreadid=501062

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" wrote
in message ...

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...o&userid=29860
View this thread:
http://www.excelforum.com/showthread...hreadid=501062

Try:

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


HTH
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=501062

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 CellsDate[3/14/2001] (or however you want the date
displayed)


--
mphell0
------------------------------------------------------------------------
mphell0's Profile: http://www.excelforum.com/member.php...o&userid=30153
View this thread: http://www.excelforum.com/showthread...hreadid=501062

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...o&userid=29860
View this thread: http://www.excelforum.com/showthread...hreadid=501062

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...o&userid=29860
View this thread: http://www.excelforum.com/showthread...hreadid=501062