# Convert number to date

This is a discussion on Convert number to date within the Excel Questions forums, part of the Question Forums category; I have a spreadsheet with a column of 3500 numbers (in this format 20030129 or 19971112 for example) they are ...

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!

Many thanks in advance

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!

Page 1 of 5 123 ... Last

#### Posting Permissions

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