Thanks:  0
Likes:  0

# Thread: Date formula?

1. does anyone have a formula for converting numbers to dates.

ex. 03282002 = 03/28/2002

Thanks

2. 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

3. 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))

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

5. 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

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

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

8. 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 ]

9. 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 ?

10. On 2002-03-28 13:14, Juan Pablo G. wrote:
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 ?
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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•