dates

[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.

 

[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.

 

[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.

 

[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.