Converting and sorting US dates into European format?

[Eric G]

XPP SP2
Excel 2003 SP2


Hello,

I have a long list of dates in US date/time format, ie "11/16/2005 11:38:08", that needs to be sorted in date and time order.

Preferably, I should also be able to convert this into the European date/time format of "YYYY-mm-dd hh:mm:sec". How do you do this in the simplest possible way in Excel?

I was thinking that one should possibly first convert the US format to a serial number format thereby making it an easy task use the dates in future calculations. But is this necessary?

Best regards,


Eric G
Stockholm, Sweden

 

[Peo Sjoblom]

If you have Swedish version of Excel you can do it in quite a few ways,
1 if you get a spreadsheet sent to you from a US version of Excel it should
be converted to whatever country region is used, since that doesn't seem to
happen you probably get it from another source, that means it is seen by
Excel as text

You can fix that pretty easy without VBA or formulas

Do you need the times? If so select the column, do data> text till kolumner,
select avgränsade fält,
click nästa, select blanksteg, click nästa, make sure the left column is
darkened/highlighted, then under
kolumndataformat select Datum and from the dropdown select MDÅ and click
slutför

that should give you the date as 2005-11-16in one column and the time
11:38:08 in the other
if you want to put them together again just add them in a third column,
assume the date is in A and the time in B
in C you can use

=A2+B2

copy down, then select the column with formulas, copy then in place do
redigera>klistra in special and select värden

finally format custom as ÅÅÅÅ-MM-DD tt:mm:ss

this can be done in otrher ways as well but this is the easiest way

if you don't want the time you can can select the right column in step 3 and
importera inte denna kolumn,
then select left column and MDÅ and slutför



--
Regards,

Peo Sjoblom

(No private emails please)


XPP SP2
Excel 2003 SP2


Hello,

I have a long list of dates in US date/time format, ie "11/16/2005
11:38:08", that needs to be sorted in date and time order.

Preferably, I should also be able to convert this into the European
date/time format of "YYYY-mm-dd hh:mm:sec". How do you do this in the
simplest possible way in Excel?

I was thinking that one should possibly first convert the US format to a
serial number format thereby making it an easy task use the dates in future
calculations. But is this necessary?

Best regards,


Eric G
Stockholm, Sweden

 

[Eric G]

Hi Peo,

Your speedy reply is much appreciated, and yes everything worked as
suggested. As a matter of fact, I have never seen, or more correctly,
observed this function previously, even though I've been a casual user of
Excel, and the other Office applications since the product inception,
sometime in the early 80's. But then, as I said, as a casual, very casual,
user. Also, I failed to inform about that I'm using English (international)
of Excel, but that makes no real difference, the functions, I presume, are
the same.

May I expand a little bit further in my problem definition and what my final
goal is?

I receive data from the US in csv format (collected from instrument
readings) once a day. Each report comprise between 45 and 200k lines. I want
to split the data into manageable chunks of data, ie less than 60k lines in
any Excel file. The data that is causing me trouble is, as previously
mentioned, the data formats, expressed as:

Start Time End Time

11/16/2005 11:38:08 11/16/2005 11:38:14

I'm not sure you can see it in the above example, but some, but not all, of
the dates (month representation) are preceded by a space. Using the function
suggested by yourself, I just did a Search and Replace, searching for " 11/"
(space 11 forward slash) and replaced with "11/". In the short test I did
400+ lines this worked fine - no problem. But with a large amount of data
this is not as easy.

Also, to manually do this little trick, even if it's perfectly viable,
introduces the possibility of errors, why I wonder if there is some little
elegant VBA (function) that would do the trick? Like placing the function in
an inserted column next to the original date?

Alternatively, I can get the data in xml format, a format that I have
absolutely no experience with. I have imported xml data into Excel, but have
not gone any further with my explorations.

Should you feel so inclined; I look forward hearing from you again.

Best regards,


Eric G
Stockholm, Sweden

 

[Peo Sjoblom]

You can use a formula to get the date and time regardless of leading space,
assume the values is in A1

=DATE(MID(TRIM(A1),FIND("
",TRIM(A1))-4,4),LEFT(TRIM(A1),2),MID(TRIM(A1),FIND("/",TRIM(A1))+1,2))+MID(TRIM(A1),FIND("
",TRIM(A1))+1,255)

this assumes that the year is always 4 digits and the month and the day are
2 digits, thus September 9 2005 12:35:14 should look like

09/06/2005 12:35:14

the Swedish version looks like

=DATUM(EXTEXT(RENSA(A1);HITTA("
";RENSA(A1))-4;4);VÄNSTER(RENSA(A1);2);EXTEXT(RENSA(A1);HITTA("/";RENSA(A1))+1;2))+EXTEXT(RENSA(A1);HITTA("
";RENSA(A1))+1;255)

Since you use English the first version should work although you might need
to replace the commas with semicolons
if your windows (assuming you are using windows) version uses Swedish
regional settings

--
Regards,

Peo Sjoblom

(No private emails please)

 

[Eric G]

Good morning Peo,

Many thanks for yesterdays solution. Your formula is almost what I'm looking
for.

I tried yesterday to figure out how to solve the final problems. As you
point out, your formula relies of double-digit months and days, as well as a
4-digit year. The year is no problem, but our friends in the US do not
always like the double digit months and dates. Some of the material I get
use double digits, but most do not.

I also looked at a search and replace solution to insert leading zeros, but
it's not easy on such a large document, while at the same time error prone.

Have you got any other suggestions, how to get around this problem?

Best regards,


Eric G
Stockholm, Sweden

 

[Peo Sjoblom]

How about

=DATE(MID(TRIM(A1),FIND("
",TRIM(A1))-4,4),LEFT(TRIM(A1),FIND("/",TRIM(A1))-1),MID(TRIM(A1),FIND("/",TRIM(A1))+1,FIND("^^",TRIM(SUBSTITUTE(A1,"/","^^",2)))-(FIND("/",TRIM(A1))+1)))+MID(TRIM(A1),FIND("
",TRIM(A1))+1,255)


in Swedish

=DATUM(EXTEXT(RENSA(A1);HITTA("
";RENSA(A1))-4;4);VÄNSTER(RENSA(A1);HITTA("/";RENSA(A1))-1);EXTEXT(RENSA(A1);HITTA("/";RENSA(A1))+1;HITTA("^^";RENSA(BYT.UT(A1;"/";"^^";2)))-(HITTA("/";RENSA(A1))+1)))+EXTEXT(RENSA(A1);HITTA("
";RENSA(A1))+1;255)


should work for

1/1/2005
11/1/2005
1/11/2005
--
Regards,

Peo Sjoblom

(No private emails please)

 

[Eric G]

Peo,

AND IT DOES!

I'm impressed, and also most grateful to you and your tenacity. Well done!

I don't want to impose on you, but what do I do with the hh:mm:ss? Is there
any way of extracting those, even if the end up in a separate column (they
can always be combined later)?

Best regards,


Eric G
Stockholm, Sweden

 

[Peo Sjoblom]

Eric,

this should work with both time and date, excel doesn't automate this so it
will default to date format when using the DATE function just select the
cells with the formulas and use a custom format of

YYYY-MM-DD hh:mm:ss

or

ÅÅÅÅ-MM-DD tt:mm:ss

the underlying values are there so you can calculate with them

 

[Eric G]

Peo,

I see. Once again many thanks, and I hope to converse with some time in the
future. It's been a pleasure.

Best regards,


Eric Green
Stockholm, Sweden

 

[tony h]

A couple of thoughts :

1. This is probably obvious when checking that any scheme works make
sure you test dates which can be valid (but different) eg 1st March as
1/3/2005 or 3/1/2005

2. Also you say the data arrives as a CSV. I am not sure how you are
importing this. If you just open a CSV Excel uses it's default
interpretations of data. If you rename the file to a text file
(xxx.TXT) then when you open it in Excel you will be allowed to define
the field types
: this will enable you to use a DATE type MDY to define it and may
solve all your problems.

Hope this helps