Date formula?

[trekcowboy]

does anyone have a formula for converting numbers to dates.

ex. 03282002 = 03/28/2002

Thanks

[Juan Pablo González]

On 2002-03-28 12:21, trekcowboy wrote:
does anyone have a formula for converting numbers to dates.

ex. 03282002 = 03/28/2002

Thanks

=DATE(RIGHT(A10,4),LEFT(A10,2),MID(A10,3,2))

But, that ASSUMES that the text length is 8 characters. I mean, month 3 is shown as 03 and day 8 is shown as 08

[Yogi Anand]

On 2002-03-28 12:21, trekcowboy wrote:
does anyone have a formula for converting numbers to dates.

ex. 03282002 = 03/28/2002

Thanks

Hi trekcowboy:
if 03282002 is in cell B11, then use

=DATE(RIGHT(B11,4),LEFT(B11,2),MID(B11,3,2))

[Yogi Anand]

Hi Juan:
We might have been posting at the same time!

[trekcowboy]

thanks to both of you, what if the month is not always two digits.

on my spreadsheet excel is dropping the leading 0

3282002

thanks again

[Juan Pablo González]

What about the Day ? does it always have 2 digits ?

[Juan Pablo González]

How about this formula ?

=DATE(RIGHT(A11,4),LEFT(A11,(LEN(A11)=8)+1),MID(A11,(LEN(A11)=8)+2,2))

[Mark W.]

On 2002-03-28 12:34, trekcowboy wrote:
thanks to both of you, what if the month is not always two digits.

on my spreadsheet excel is dropping the leading 0

3282002

thanks again

Use =TEXT(REPT(0,8-LEN(A10))&A10,"00-00-0000")+0

[ This Message was edited by: Mark W. on 2002-03-28 14:40 ]

[Juan Pablo González]

On 2002-03-28 13:12, Mark W. wrote:

Use =TEXT(REPT(0,8-LEN(A10))&A10,"00-00-0000")+0

Mark, just confirming, this assumes that the Date system uses mm-dd-yyyy right ?

[Mark W.]

On 2002-03-28 13:14, Juan Pablo G. wrote:

Mark, just confirming, this assumes that the Date system uses mm-dd-yyyy right ?

If your Regional date setting is dd/mm/yy and you enter 28032002 into cell A10 it will work as well.

If you're concerned about the dashes instead of slashes... it makes no difference.