date formattin
 

I have a date imported from another program. It shows up as 22.08.2008 for
Aug 22, 2008. It is not recognised as a date in excel. Can I convert it? I
have used the "replace" function and it works well. But I would like it to
be more automatic as the months change depending on when the data is
imported. There are also several of them (1400+).

How can I change 22.08.2008 to 22/08/2008? My computer is set to
English(US). Not sure if that is it?

date formattin
 

To update in place (without a helper column), select the cells you want to
convert and run this macro:

Sub formater()
For Each cell In Selection
s = Split(cell.Value, ".")
cell.Value = DateValue(s(1) & "/" & s(0) & "/" & s(2))
Next
End Sub

--
Gary''s Student - gsnu200807


"Duane" wrote:

I have a date imported from another program. It shows up as 22.08.2008 for
Aug 22, 2008. It is not recognised as a date in excel. Can I convert it? I
have used the "replace" function and it works well. But I would like it to
be more automatic as the months change depending on when the data is
imported. There are also several of them (1400+).

How can I change 22.08.2008 to 22/08/2008? My computer is set to
English(US). Not sure if that is it?

date formattin
 

Assuming the imported date is always formated with two-digit month and day
and 4-digit year, the following would work:

=DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))

"Duane" wrote:

I have a date imported from another program. It shows up as 22.08.2008 for
Aug 22, 2008. It is not recognised as a date in excel. Can I convert it? I
have used the "replace" function and it works well. But I would like it to
be more automatic as the months change depending on when the data is
imported. There are also several of them (1400+).

How can I change 22.08.2008 to 22/08/2008? My computer is set to
English(US). Not sure if that is it?

date formattin
 

Select the Colum, goto DataText To Columns, Next through the first two
screens, then pick Date from the Column data format and Finish

--
__________________________________
HTH

Bob

"Duane" wrote in message
...
I have a date imported from another program. It shows up as 22.08.2008 for
Aug 22, 2008. It is not recognised as a date in excel. Can I convert it?
I
have used the "replace" function and it works well. But I would like it
to
be more automatic as the months change depending on when the data is
imported. There are also several of them (1400+).

How can I change 22.08.2008 to 22/08/2008? My computer is set to
English(US). Not sure if that is it?

date formattin
 

Remember, please do not double post. You had responses on your other post.
--
John C


"Duane" wrote:

I have a date imported from another program. It shows up as 22.08.2008 for
Aug 22, 2008. It is not recognised as a date in excel. Can I convert it? I
have used the "replace" function and it works well. But I would like it to
be more automatic as the months change depending on when the data is
imported. There are also several of them (1400+).

How can I change 22.08.2008 to 22/08/2008? My computer is set to
English(US). Not sure if that is it?

date formattin
 

Hi Duane

You have replies in the posting you made in another group.

--
Regards
Roger Govier

"Duane" wrote in message
...
I have a date imported from another program. It shows up as 22.08.2008
for
Aug 22, 2008. It is not recognised as a date in excel. Can I convert it?
I
have used the "replace" function and it works well. But I would like it
to
be more automatic as the months change depending on when the data is
imported. There are also several of them (1400+).

How can I change 22.08.2008 to 22/08/2008? My computer is set to
English(US). Not sure if that is it?

date formattin
 

Hi Bob

The OP isn't giving all the facts here.
From his posting in .misc
"Duane" wrote in message
...
I have a date and time. I need it to show up as a date and it is not. I
have it imported from another program as:
22.08.2008 00:25:00

DataText to columns wouldn't work for this.
I suggested

In an adjoining column
=--SUBSTITUTE(A1,".","/")
copy down as far as required.

To "fix" the data, copy your new columnPaste SpecialValues
Format the column to whatever date format you prefer.
You can then delete the original imported column.
--
Regards
Roger Govier



--
Regards
Roger Govier

"Bob Phillips" wrote in message
...
Select the Colum, goto DataText To Columns, Next through the first two
screens, then pick Date from the Column data format and Finish

--
__________________________________
HTH

Bob

"Duane" wrote in message
...
I have a date imported from another program. It shows up as 22.08.2008
for
Aug 22, 2008. It is not recognised as a date in excel. Can I convert
it? I
have used the "replace" function and it works well. But I would like it
to
be more automatic as the months change depending on when the data is
imported. There are also several of them (1400+).

How can I change 22.08.2008 to 22/08/2008? My computer is set to
English(US). Not sure if that is it?