Date Format

S

Shoe3535

I would like to convert a date that I've input from another program. When I
input it I get 'Aug-28-2008. I want it to just be a regular date - so it
would be *8/28/2008 if I were to select it in Format Cells.
It says that I have General as the Category selected. When I switch it to
Custom - nothing seems to happen.
Any suggestions?
 
G

Gary''s Student

First create a small table in the worksheet:

Jan 1
Feb 2
Mar 3
Apr 4
May 5
Jun 6
Jul 7
Aug 8
Sep 9
Oct 10
Nov 11
Dec 12

and assign the name:
nmths
to the table. Then, with your data in A1, try:

=DATE(RIGHT(A1,4),VLOOKUP(LEFT(A1,3),mnths,2,FALSE),MID(A1,5,2))
 
T

Tangier

First create a small table in the worksheet:

Jan     1
Feb     2
Mar     3
Apr     4
May     5
Jun     6
Jul     7
Aug     8
Sep     9
Oct     10
Nov     11
Dec     12

and assign the name:
nmths
to the table.  Then, with your data in A1, try:

=DATE(RIGHT(A1,4),VLOOKUP(LEFT(A1,3),mnths,2,FALSE),MID(A1,5,2))
Click to expand...

very simply, create a macro and convert the date. The macro code will
look like this:

Sub dateconvert()
Cells.Replace What:="Aug-28-2008", Replacement:="8/28/2008",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

End Sub

Assign this macro to your worksheet and run it, it should convert all
the dates that look like Aug-28-2008
 
R

Rick Rothstein

Where do you see that apostrophe in front of the text... in the cell and
Formula Bar or only in the Formula Bar? Either way, the first thing to do is
select all your "dates" (which are really text that look like a date). Then,
if you see the apostrophe in the cell, then click Edit/Replace on Excel's
menu bar, put an apostrophe (') in the "Find what" field, leave the "Replace
with" field empty and click OK. Now, do the following for either case... the
cells where we just removed the apostrophe or for the case where the
apostrophe was only in the Formula Bar... click Data/TextToColumn, click
Next twice on the dialog box that appears, select the "Date" option button
in the "Column data format" section on the dialog page you end up on after
clicking Next the second time, then click the Finish button. Your entries
should now be real dates that you can use Format/Cell (from Excel's menu
bar) to make it display anyway that you want.
 
T

Tangier

Where do you see that apostrophe in front of the text... in the cell and
Formula Bar or only in the Formula Bar? Either way, the first thing to dois
select all your "dates" (which are really text that look like a date). Then,
if you see the apostrophe in the cell, then click Edit/Replace on Excel's
menu bar, put an apostrophe (') in the "Find what" field, leave the "Replace
with" field empty and click OK. Now, do the following for either case... the
cells where we just removed the apostrophe or for the case where the
apostrophe was only in the Formula Bar... click Data/TextToColumn, click
Next twice on the dialog box that appears, select the "Date" option button
in the "Column data format" section on the dialog page you end up on after
clicking Next the second time, then click the Finish button. Your entries
should now be real dates that you can use Format/Cell (from Excel's menu
bar) to make it display anyway that you want.

--
Rick (MVP - Excel)






- Show quoted text -
Click to expand...

Rick, if I am searching and replacing xcontents in a cell, how do I
specify SearchOrder: to just search one column, or one row????
 
R

Rick Rothstein

Instead of using Cells as the starting reference range, use a more
restricted range and the search/replace will occur within that range only.
For example, to modify your posted code to handle, say, Column B only...

Sub dateconvert()
Range("B:B").Replace What:="Aug-28-2008", Replacement:="8/28/2008", _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

--
Rick (MVP - Excel)


Where do you see that apostrophe in front of the text... in the cell and
Formula Bar or only in the Formula Bar? Either way, the first thing to do
is
select all your "dates" (which are really text that look like a date).
Then,
if you see the apostrophe in the cell, then click Edit/Replace on Excel's
menu bar, put an apostrophe (') in the "Find what" field, leave the
"Replace
with" field empty and click OK. Now, do the following for either case...
the
cells where we just removed the apostrophe or for the case where the
apostrophe was only in the Formula Bar... click Data/TextToColumn, click
Next twice on the dialog box that appears, select the "Date" option button
in the "Column data format" section on the dialog page you end up on after
clicking Next the second time, then click the Finish button. Your entries
should now be real dates that you can use Format/Cell (from Excel's menu
bar) to make it display anyway that you want.

--
Rick (MVP - Excel)






- Show quoted text -
Click to expand...

Rick, if I am searching and replacing xcontents in a cell, how do I
specify SearchOrder: to just search one column, or one row????
 
Top