Convert number to date

[fearless2002uk]

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

 

[happypig]

The numbers you saw were the 5 digit date serial numbers.

In Excel dates are really just numbers formatted to look like dates for our human consumption.

The date serial number is the count of days since a base date. The default base date is Jan 1 1900. Jan 1 1900 is date serial number 1. Each successive day the serial number increases by 1.

Jan 1 1900 = 1
Jan 2 1900 = 2
Jan 3 1900 = 3
Jan 10 1900 = 10
Jan 25 1975 = 27419
Feb 15 2012 = 40954

You can see the date serial number by entering a date in some cell then changing the cell format to General.

You just explained one of the greatest mysteries in my life with Excel.
Thanks a million!!!

 

[T. Valko]

You just explained one of the greatest mysteries in my life with Excel.
Thanks a million!!!

You're welcome. Thanks for the feedback!

 

[MikeB78]

Sorry to bother you again guys, but can someone explain why the following formula doesnt work for me to pull just the month and year:
=date(mid(i2,2,2)+2000,Mid(i2,4,2))<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[T. Valko]

Sorry to bother you again guys, but can someone explain why the following formula doesnt work for me to pull just the month and year:
=date(mid(i2,2,2)+2000,Mid(i2,4,2))<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

Your formula is missing the DAY argument. The syntax for the DATE function is:

=DATE(year,month,day)

All 3 arguments are mandatory.

If I2 contains 1120131 what result do you expect?

 

[MikeB78]

Your formula is missing the DAY argument. The syntax for the DATE function is:

=DATE(year,month,day)

All 3 arguments are mandatory.

If I2 contains 1120131 what result do you expect?

Ideally I would like to be able to pull the result of January-2012 but I dont know if that is possible

 

[T. Valko]

Ideally I would like to be able to pull the result of January-2012 but I dont know if that is possible

Ok, you can use the same formula:

=TEXT(20&MID(I2,2,6),"00-00-00")+0

And just format the cell to display as January-2012 by using this format:

mmmm-yyyy

Note that the cell still contains a true numeric date.

Or, you could use this version which will return the TEXT string January-2012:

=TEXT(--TEXT(20&MID(I2,2,6),"00-00-00"),"mmmm-yyyy")

 

[MikeB78]

Thanks again!

 

[T. Valko]

Thanks again!

You're welcome!

 

[BuffaloRun]

You're welcome. Thanks for the feedback! [/QUOTE


Observed a message that solved my problem perfectly.
=--text(A1,00-00-0000). Is a stroke of genius for taking a two digit date and turning it into a short date. This will save a lot of time. Hats off!!!!

 

[vicedo]

Please guys, i have successfully converted from PDF to Excel but the dates are scattered. I need to convert 24112110 into something like this 24/12/10. The major issue is that the slash has been converted into 1 and i have a whole lot of them