Date Formats

[John Duchowski]

Hi,

One of our diagnostic devices downloads the data in the format of:

26/08/2003 13:26:15,13.69
26/08/2003 14:26:15,13.46
26/08/2003 15:26:15,13.46

where the first column is the date, the second time and the third the
differential pressure reading. I import these into Excel, eliminate the
comma column and end up with pretty much what I want, except that I cannot
get Excel to "understand" the European (UK) date/time format of 26/08/2003
13:26:15 or especially the date format of 26/08/2003. I tried using the
Custom Cell Format of dd/mm/yyyy but when I add the date and time fields I
don't get the expected combined value; I get the #VALUE error. I then
manually (sigh...) convert all date values to 08/26/2003 and everything
works A-Okay. However, these files tend to be quite large (ca. 1956 rows and
greater) so converting this manually is quite laborious not to mention
tedious. Ideally, the data should look like this:

8/26/2003 13:26 13.69
8/26/2003 14:26 13.46
8/26/2003 15:26 13.46


where the date and time are combined and the differential pressure can
readily be plotted against date/time. I would greatly appreciate any hints
as to either how to convert the date format more easily or how to get Excel
to recognize the UK formatting. Thanks.

Regards, John

 

[Peo Sjoblom]

You can split the column using fixed width and split between the date and
the time, do data>text to columns,
select fixed width, click next, insert the split, click next and in step 3
select the column with the dates and under column data format select Date
and DMY from the dropdown worked for me using your example.
Or assuming you already have separated them from each other just select the
date column and text to columns, click next twice and select Date and DMY
and click Finish

 

[John Duchowski]

Thank You, Thank You, Thank You Peo - this worked like a charm! The
conversion in step 3 and Date selection to DMY did the trick !
Thanks again for your help - this makes our lives sooo much easier! Cheers
and Best Regards, John

 

[Peo Sjoblom]

My Pleasure John..

Peo

Thank You, Thank You, Thank You Peo - this worked like a charm! The
conversion in step 3 and Date selection to DMY did the trick !
Thanks again for your help - this makes our lives sooo much easier! Cheers
and Best Regards, John

fields