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

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)


"Eric G" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
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

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" <(E-Mail Removed)> wrote in message
news:ujAPId%(E-Mail Removed)...
> 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)
>
>
> "Eric G" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> wrote in message
news:e1DKfl$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:ujAPId%(E-Mail Removed)...
>> 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)
>>
>>
>> "Eric G" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> 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
>
>
>

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" <(E-Mail Removed)> wrote in message
news:%23rg93dH%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:e1DKfl$(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:ujAPId%(E-Mail Removed)...
>>> 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)
>>>
>>>
>>> "Eric G" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>> 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
>>
>>
>>
>

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" <(E-Mail Removed)> wrote in message
news:e2JJAIK%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:%23rg93dH%(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:e1DKfl$(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:ujAPId%(E-Mail Removed)...
>>>> 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)
>>>>
>>>>
>>>> "Eric G" <(E-Mail Removed)> wrote in message
>>>> news:(E-Mail Removed)...
>>>> 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,

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" <(E-Mail Removed)> wrote in message
news:O1R9brR%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:e2JJAIK%(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:%23rg93dH%(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:e1DKfl$(E-Mail Removed)...
>>>> 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" <(E-Mail Removed)> wrote in message
>>>> news:ujAPId%(E-Mail Removed)...
>>>>> 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)
>>>>>
>>>>>
>>>>> "Eric G" <(E-Mail Removed)> wrote in message
>>>>> news:(E-Mail Removed)...
>>>>> 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,

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


--

Regards,

Peo Sjoblom


"Eric G" <(E-Mail Removed)> wrote in message
news:eVEYlsa%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:O1R9brR%(E-Mail Removed)...
> > How about
> >
> > =DATE(MID(TRIM(A1),FIND("
> >
",TRIM(A1))-4,4),LEFT(TRIM(A1),FIND("/",TRIM(A1))-1),MID(TRIM(A1),FIND("/",T
RIM(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" <(E-Mail Removed)> wrote in message
> > news:e2JJAIK%(E-Mail Removed)...
> >> 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" <(E-Mail Removed)> wrote in message
> >> news:%23rg93dH%(E-Mail Removed)...
> >>> 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" <(E-Mail Removed)> wrote in message
> >>> news:e1DKfl$(E-Mail Removed)...
> >>>> 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" <(E-Mail Removed)> wrote in message
> >>>> news:ujAPId%(E-Mail Removed)...
> >>>>> 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)
> >>>>>
> >>>>>
> >>>>> "Eric G" <(E-Mail Removed)> wrote in message
> >>>>> news:(E-Mail Removed)...
> >>>>> 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,

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


"Peo Sjoblom" <(E-Mail Removed)> wrote in message
news:uQSZ45a%(E-Mail Removed)...
> 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
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
>
> "Eric G" <(E-Mail Removed)> wrote in message
> news:eVEYlsa%(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:O1R9brR%(E-Mail Removed)...
>> > How about
>> >
>> > =DATE(MID(TRIM(A1),FIND("
>> >
> ",TRIM(A1))-4,4),LEFT(TRIM(A1),FIND("/",TRIM(A1))-1),MID(TRIM(A1),FIND("/",T
> RIM(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" <(E-Mail Removed)> wrote in message
>> > news:e2JJAIK%(E-Mail Removed)...
>> >> 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" <(E-Mail Removed)> wrote in message
>> >> news:%23rg93dH%(E-Mail Removed)...
>> >>> 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" <(E-Mail Removed)> wrote in message
>> >>> news:e1DKfl$(E-Mail Removed)...
>> >>>> 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" <(E-Mail Removed)> wrote in message
>> >>>> news:ujAPId%(E-Mail Removed)...
>> >>>>> 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)
>> >>>>>
>> >>>>>
>> >>>>> "Eric G" <(E-Mail Removed)> wrote in message
>> >>>>> news:(E-Mail Removed)...
>> >>>>> 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
>> >>>>
>> >>>>
>> >>>>
>> >>>
>> >>
>> >>
>> >>
>> >
>>
>>
>
>

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


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=490378