Convert number to date

Thanks:  0
Likes:  0

1.
I have a spreadsheet with a column of 3500 numbers (in this format 20030129 or 19971112 for example) they are infact dates - I am struggling to format them as such. HELP!

2. You will have to use a formula to convert them - they won't be recognised as dates by Excel.

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

Change the reference to A1 as required and choose your date format.

3. =TEXT(A1,"0000-00-00")+0

Format the cell containing this formula with an appropriate date format.

[ This Message was edited by: Mark W. on 2003-01-31 13:30 ]

4. On 2003-01-31 13:29, Mark W. wrote:
=TEXT(A1,"0000-00-00")+0

Format the cell containing this formula with an appropriate date format.

[ This Message was edited by: Mark W. on 2003-01-31 13:30 ]
Cool!

5. Re: Convert number to date

I am trying to convert 38370 to date 19-Jan-2009.
Using formula =DATE(RIGHT(A1,2),MID(A1,5,2),LEFT(A1,4)).
But it gives starnge date.

Number 38370 is generated by converting date "19-Jan-2009" to general format in excel sheet.

6. Re: Convert number to date

Just Format the cell as

dd-mmm-yyyy

7. Re: Convert number to date

Originally Posted by Andrew Poulsom
Cool!
This by far makes most sense.
But what does +0 do in the function? Does it make the cell numeric?

8. Re: Convert number to date

Originally Posted by happypig
This by far makes most sense.
But what does +0 do in the function? Does it make the cell numeric?
Yes

The TEXT( ) function returns a TEXT data type even if looks like a number.

The math operation of adding 0 (+0) coerces the text string that represents a date into the true numeric date value.

Another way to do it is:

=--TEXT(A1,"0000-00-00")

9. Re: Convert number to date

Originally Posted by T. Valko
Yes

The TEXT( ) function returns a TEXT data type even if looks like a number.

The math operation of adding 0 (+0) coerces the text string that represents a date into the true numeric date value.

Another way to do it is:

=--TEXT(A1,"0000-00-00")

That is awesome. I knew taking a couple of programming courses at university should help understand Excel functions better in the future, haha!

Thanks so much for your explanation.

10. Re: Convert number to date

Originally Posted by happypig
That is awesome. I knew taking a couple of programming courses at university should help understand Excel functions better in the future, haha!

Thanks so much for your explanation.
You're welcome. Thanks for the feedback!

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
•