dates

D

Duane

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

If I change the .08. to aug, it works. I can use "replace" and it all
works. But the months will change. I think it is a format thing. My excel
is set to english US. Is that something to do with it?

Thanks for any input.
 
J

John C

Assuming your days and months are always 2 characters, and the year is always
4 characters
A1: 22.08.2008 00:25:00
=DATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2))

Hope this helps.
 
R

Roger Govier

Hi Duane

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

To "fix" the data, copy your new column>Paste Special>Values
Format the column to whatever date format you prefer.
You can then delete the original imported column.
 
D

David Biddulph

Not a question of Excel settings but of Windows regional settings (through
the Control Panel).
Change them to something which expects a date as 22.08.2008 and the when you
import 22.08.2008 00:25:00
it will be recognised as date and time, rather than as text.
 
Top