|  | Date Format | | |
| | | Shoe3535 |  |
|  Posted: Fri Aug 29, 2008 2:56 pm Post subject: Date Format |  |
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? |
| |
| | | Gary''s Student | |
| | Posted: Fri Aug 29, 2008 3:21 pm Post subject: RE: Date Format | |
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))
--
Gary''s Student - gsnu200802 |
| |
| | | Rick Rothstein | |
| | Posted: Fri Aug 29, 2008 7:30 pm Post subject: Re: Date Format | |
| |  | |
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)
"Shoe3535" <Shoe3535@discussions.microsoft.com> wrote in message
news:92D5998A-81C5-499D-88C2-CBF5B726CDF2@microsoft.com...
| Quote: | 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? |
|
| |
| | | Tangier | |
| | Posted: Fri Aug 29, 2008 9:15 pm Post subject: Re: Date Format | |
On Aug 29, 12:21 pm, Gary''s Student
<GarysStud...@discussions.microsoft.com> wrote:
| Quote: | 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))
--
Gary''s Student - gsnu200802
|
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 |
| |
| | | Tangier | |
| | Posted: Fri Aug 29, 2008 9:39 pm Post subject: Re: Date Format | |
| | | |
On Aug 29, 4:30 pm, "Rick Rothstein"
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
| Quote: | 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)
"Shoe3535" <Shoe3...@discussions.microsoft.com> wrote in message
news:92D5998A-81C5-499D-88C2-CBF5B726CDF2@microsoft.com...
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?- Hide quoted text -
- Show quoted text -
|
Rick, if I am searching and replacing xcontents in a cell, how do I
specify SearchOrder: to just search one column, or one row???? |
| |
| | | Rick Rothstein | |
| | Posted: Sat Aug 30, 2008 4:42 pm Post subject: Re: Date Format | |
| | | |
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)
"Tangier" <nitinhemmady@gmail.com> wrote in message
news:51c9c57a-c0df-46a5-b0eb-dd807873078b@m36g2000hse.googlegroups.com...
On Aug 29, 4:30 pm, "Rick Rothstein"
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
| Quote: | 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)
"Shoe3535" <Shoe3...@discussions.microsoft.com> wrote in message
news:92D5998A-81C5-499D-88C2-CBF5B726CDF2@microsoft.com...
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?- Hide quoted text -
- Show quoted text -
|
Rick, if I am searching and replacing xcontents in a cell, how do I
specify SearchOrder: to just search one column, or one row???? |
| |
|
|
