Date function

[2077delta]

How do I write the following formula as a function that lets me supply any cell reference as cell1 to convert from text to a date?

=DATE(LEFT(cell1,4),LEFT(RIGHT(cell1,4),2),RIGHT(cell1,2))

Thanks in advance

 

[barry houdini]

Do you mean with VBA?

If so, not sure but you can do the same with simpler

=TEXT(cell1,"0000-00-00")+0

 

[2077delta]

I apologize, yes I mean with VBA. Yours is simpler, but the result is text. I want the result to be a numeric date, hence the use of the DATE formula.

 

[Doug.T]

In VBA:

a = DateSerial(Left(Range("a1"), 4), Left(Right(Range("a1"), 4), 2), Right(Range("a1"), 2))

 

[barry houdini]

......but the result is text. I want the result to be a numeric date, hence the use of the DATE formula.

No, the result of

=TEXT(cell1,"0000-00-00")+0

is numeric, the +0 at the end converts it from text so this formula should give an identical result