Convert text date to general number

[Guest]

Hi,

I have a text value for the date. I want to convert it to a number (ie
from 190707 to 39282). Please note the format of the date is ddmmyy

 

[Peo Sjoblom]

Make sure the cells are not formatted as text, then do data>text to columns,
click next twice, in step 3 select date under column data format and from
date dropdown select DMY, click finish

 

[Guest]

Sorry, I should have explained I had the date in a variable name (The_Date)
in VBA code.

Any ideas?

 

[Rick Rothstein \(MVP - VB\)]

Try this...

The_Date = "190707"
SerialNumber = CDbl(DateSerial("20" & Right$(The_Date, 2), _
Mid$(The_Date, 3, 2), Left$(The_Date, 2)))

Rick

 

[Guest]

You legend!

Thanks

You deserve your MVP

 

[Guest]

Hi,

For more information, please refer to the following site:
http://www.contextures.com/xlDataEntry02.html

Challa Prabhu

 

[Rick Rothstein \(MVP - VB\)]

Just to keep things straight... my MVP is not for Excel related
activities... it is for programming in Visual Basic (the compiled version).
I'm a relative new comer to the Excel world (well, actually, a returnee
after a long absence), but I find a lot of the programming skills I acquired
over in the compiled world of VB can be translated into Excel, so every now
and then I can "get one right".

With that said, I like to point out that these various Excel newsgroups have
several Excel MVPs (and several non-MVPs as well) who are the true
legends... I've seen displays of Excel related skills by these individuals
which I find truly amazing and awe inspiring. They volunteer their time and
skills to help others here... these are the people who deserve your praise,
not me (trust me on that).

Rick