Covert Number to date

N

Newbee

I have a spreadsheet with 20071123 is in number format. I am trying to
covert it so that excel will see those numbers as a date. It is not text but
in number format. How do I change this to make it read 11/23/2007
 
B

Bob Phillips

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

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
P

Peo Sjoblom

Select the column with the "dates", do data>text to columns, click next
twice, under column data format select Date and from dropdown select YMD and
click finish


--


Regards,


Peo Sjoblom
 
T

T. Valko

Try this:

Select the cell in question
Goto the menu Data>Text to columns
Click Next twice
In Step 3 of the wizard under Column data format...
Select Date>YMD
Click Finish
 
R

Rick Rothstein \(MVP - VB\)

If you want to go the formula route as Bob suggested, here is a slightly
shorter alternative for you to consider...

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

Rick
 
N

Newbee

This worked perfectly!!!

Rick Rothstein (MVP - VB) said:
If you want to go the formula route as Bob suggested, here is a slightly
shorter alternative for you to consider...

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

Rick
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top