How to convert number format to date format?

[Raja]

Hi all,

I will receive a excel file which have the date in 20090711 format
need a auotmation to convert the exixting format to dd-mmm-yy format.

Example:

I have A:A in 20090711 format

I need A:A in 11-Jul-09

So that i can calculate my SLA using the date.

Any help would appreciated.

Regards,

Raj

 

[Gord Dibben]

Data>Text to Columns>Next>Next>Column Data Format>Date>YDM>Finish

Macro Recorder will show you how to automate this.


Gord Dibben MS Excel MVP

 

[Rick Rothstein]

Select the column of dates, click Data/Text To Columns on the menu bar,
click the Next button twice, click the Date option button (in the upper
right corner of the dialog box) and select YMD from its drop down list, then
click the Finish button. Your "dates" should now be real Excel dates.

 

[Rick Rothstein]

Data>Text to Columns>Next>Next>Column Data Format>Date>YDM>Finish

I think you may have meant YMD for the next to last step.

 

[Gord Dibben]

Thanks Rick


Gord

I think you may have meant YMD for the next to last step.

 

[Raja]

Thanks Rick


Gord

Thanks everyone,

Thanks a lot for your rapid reply.

This looks like good when i recorded the macro however i need the
format like dd-mmm-yy for my requirement.

Note the column contains a web query so esch time when i refersh the
data the column will retrieve new data so the proposed solution might
not work in this case.

Please advice

Regards,

Raja

 

[Rick Rothstein]

Note: We tend to use top-posting in the Excel newsgroups... it would be
better for the archive records if you would follow the same type of posting
that the first responder implements.

Okay, I'm not sure of the "web query" part, but I will note that once the
procedure that Gord and I posted has been completed, your column will
contain *real* Excel dates, so you can use Format/Cells (from Excel's menu
bar) to format these dates any way that you want (which, in your case, would
be a Custom format of "dd-mmm-yy").

 

[Raja]

Thanks mate sorry if i hurt you in any sense your idea helped me lot.

Regards,

Raja

 

[Rick Rothstein]

My comment about the top/bottom posting wasn't about me in any way, it is
more for the person who searches these archives later on and comes across
this thread... if the answers and responses alternate from between top and
bottom, it will make that person's attempt to see if the thread addresses
his/her needs that much harder for them to decipher. If the answers are
consistently top posted, or if they are consistently bottom posted, then the
thread of messages can be more easily read in the sequential order they were
posted in and the "flow" of the responses will be easier for the individuals
reading it to follow.

 

[Modeste]

Bonsour® Raja avec ferveur ;o))) vous nous disiez :

Hi all,

I will receive a excel file which have the date in 20090711 format i
need a auotmation to convert the exixting format to dd-mmm-yy format.

Example:

I have A:A in 20090711 format

I need A:A in 11-Jul-09

=1*(TEXT(1*A1;"0000""-""00""-""00"))
then format cell as Date you want

 

[Raja]

Thanks a lot Modeste,

Its working perfectly but can i get macro fro this since i have the
huge number of number for mat data in the column so each time i have to
drag the formula, is there any script to convert the number format to
date format as long as data receive in the A column are someting because
that would help me lot.

Cheers,

Raja

 

[Rick Rothstein]

I'm a little unsure what you want at this point. Did you want to physically
change your data (the 20090711 type values) into real dates in the same
cell? Or did you want to put the real dates in a different column and keep
your original values? Either way, in a macro, you would make use of the
Format function to create a real date value, put that value in whatever cell
you want and then, using code, format the cell to look the way you want.
Something like this (assuming, for this example, you are putting the real
date back into the cell the 8-digit number came from) should do what you
want...

If YourCell.Value Like "00000000" Then
YourCell.ClearFormats
YourCell.Value = Format(YourCell.Value, "0000-00-00")
YourCell.NumberFormat = "dd-mmm-yy"
End If