Text to date formula 1

[Mizzness]

All.

I am using the following formula to change my date (in text form) to a number:

=DATEVALUE(LEFT(L170,4)&"/11/"&RIGHT(L170,2))

How can I change the middle section, which is the month ?
Note: Left = the year / Right = the day

I have tried the MID function but to no avail.

Thanx.

 

[SkipVought]

Hi,
[tt]
=DATE(LEFT(L170,4),mid(L170,5,2),RIGHT(L170,2))

[/tt]
or try using the Data > text to columns... feature. Select FIXED, no separaters and choose the date format that is appropriate.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[Mizzness]

Skip,

I was trying that formula with DATEVALUE but DATE did the trick.


Thanx !!!