Converting and sorting US dates into European format?

This is a multi-part message in MIME format.

------=_NextPart_000_0006_01C5F7E2.A79C38F0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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
------=_NextPart_000_0006_01C5F7E2.A79C38F0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2900.2769" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV class=3DMsoNormal style=3D"MARGIN: 0cm 0cm 0pt"><SPAN=20
style=3D"mso-ansi-language: EN-US"><FONT face=3DArial>XPP SP2<BR>Excel =
2003=20
SP2<BR><BR><BR>Hello,<BR><BR>I have a long list of dates in =
<?xml:namespace=20
prefix =3D st1 ns =3D "urn:schemas-microsoft-com:office:smarttags"=20
/><st1:country-region w:st=3D"on"><st1:place=20
w:st=3D"on">US</st1:place></st1:country-region> date/time format, ie =
<SPAN=20
style=3D"mso-spacerun: yes">&nbsp;</SPAN>=9311/16/2005 11:38:08=94, that =
needs to be=20
sorted in date and time order.<BR><BR>Preferably, I should also be able =
to=20
convert this into the European date/time format of =93YYYY-mm-dd =
hh:mm:sec=94. How=20
do you do this in the simplest possible way in Excel?<BR><BR>I was =
thinking that=20
one should possibly first convert the US format to a serial number =
format=20
thereby making it an easy task use the dates in future calculations. But =
is this=20
necessary?<BR><BR>Best regards,<BR><BR><BR>Eric G<BR>Stockholm,=20
<st1:country-region w:st=3D"on"><st1:place=20
w:st=3D"on">Sweden</st1:place></st1:country-region><?xml:namespace =
prefix =3D o ns =3D=20
"urn:schemas-microsoft-com:office:office"=20
/><o:p></o:p></FONT></SPAN></DIV></BODY></HTML>

------=_NextPart_000_0006_01C5F7E2.A79C38F0--

0
someone913 (592)
12/3/2005 7:22:09 AM
excel 39879 articles. 2 followers. Follow

9 Replies
857 Views

Similar Articles

[PageSpeed] 15

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" <someone@microsoft.com> wrote in message 
news:OoqgNp99FHA.356@TK2MSFTNGP12.phx.gbl...
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 

0
terre081 (3244)
12/3/2005 8:54:41 AM
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" <terre08@mvps.org> wrote in message
news:ujAPId%239FHA.3760@TK2MSFTNGP14.phx.gbl...
> 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" <someone@microsoft.com> wrote in message
> news:OoqgNp99FHA.356@TK2MSFTNGP12.phx.gbl...
> 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



0
someone913 (592)
12/3/2005 11:04:38 AM
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" <someone@microsoft.com> wrote in message 
news:e1DKfl$9FHA.3480@TK2MSFTNGP10.phx.gbl...
> 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" <terre08@mvps.org> wrote in message
> news:ujAPId%239FHA.3760@TK2MSFTNGP14.phx.gbl...
>> 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" <someone@microsoft.com> wrote in message
>> news:OoqgNp99FHA.356@TK2MSFTNGP12.phx.gbl...
>> 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
>
>
> 

0
terre081 (3244)
12/4/2005 2:07:22 AM
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" <terre08@mvps.org> wrote in message
news:%23rg93dH%23FHA.160@TK2MSFTNGP12.phx.gbl...
> 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" <someone@microsoft.com> wrote in message
> news:e1DKfl$9FHA.3480@TK2MSFTNGP10.phx.gbl...
>> 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" <terre08@mvps.org> wrote in message
>> news:ujAPId%239FHA.3760@TK2MSFTNGP14.phx.gbl...
>>> 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" <someone@microsoft.com> wrote in message
>>> news:OoqgNp99FHA.356@TK2MSFTNGP12.phx.gbl...
>>> 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
>>
>>
>>
>



0
someone913 (592)
12/4/2005 7:11:43 AM
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" <someone@microsoft.com> wrote in message 
news:e2JJAIK%23FHA.2708@TK2MSFTNGP12.phx.gbl...
> 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" <terre08@mvps.org> wrote in message
> news:%23rg93dH%23FHA.160@TK2MSFTNGP12.phx.gbl...
>> 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" <someone@microsoft.com> wrote in message
>> news:e1DKfl$9FHA.3480@TK2MSFTNGP10.phx.gbl...
>>> 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" <terre08@mvps.org> wrote in message
>>> news:ujAPId%239FHA.3760@TK2MSFTNGP14.phx.gbl...
>>>> 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" <someone@microsoft.com> wrote in message
>>>> news:OoqgNp99FHA.356@TK2MSFTNGP12.phx.gbl...
>>>> 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
>>>
>>>
>>>
>>
>
>
> 

0
terre081 (3244)
12/4/2005 9:36:57 PM
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" <terre08@mvps.org> wrote in message 
news:O1R9brR%23FHA.1148@tk2msftngp13.phx.gbl...
> 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" <someone@microsoft.com> wrote in message 
> news:e2JJAIK%23FHA.2708@TK2MSFTNGP12.phx.gbl...
>> 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" <terre08@mvps.org> wrote in message
>> news:%23rg93dH%23FHA.160@TK2MSFTNGP12.phx.gbl...
>>> 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" <someone@microsoft.com> wrote in message
>>> news:e1DKfl$9FHA.3480@TK2MSFTNGP10.phx.gbl...
>>>> 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" <terre08@mvps.org> wrote in message
>>>> news:ujAPId%239FHA.3760@TK2MSFTNGP14.phx.gbl...
>>>>> 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" <someone@microsoft.com> wrote in message
>>>>> news:OoqgNp99FHA.356@TK2MSFTNGP12.phx.gbl...
>>>>> 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
>>>>
>>>>
>>>>
>>>
>>
>>
>>
> 


0
someone913 (592)
12/5/2005 2:49:39 PM
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" <someone@microsoft.com> wrote in message
news:eVEYlsa%23FHA.2440@TK2MSFTNGP15.phx.gbl...
> 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" <terre08@mvps.org> wrote in message
> news:O1R9brR%23FHA.1148@tk2msftngp13.phx.gbl...
> > 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" <someone@microsoft.com> wrote in message
> > news:e2JJAIK%23FHA.2708@TK2MSFTNGP12.phx.gbl...
> >> 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" <terre08@mvps.org> wrote in message
> >> news:%23rg93dH%23FHA.160@TK2MSFTNGP12.phx.gbl...
> >>> 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" <someone@microsoft.com> wrote in message
> >>> news:e1DKfl$9FHA.3480@TK2MSFTNGP10.phx.gbl...
> >>>> 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" <terre08@mvps.org> wrote in message
> >>>> news:ujAPId%239FHA.3760@TK2MSFTNGP14.phx.gbl...
> >>>>> 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" <someone@microsoft.com> wrote in message
> >>>>> news:OoqgNp99FHA.356@TK2MSFTNGP12.phx.gbl...
> >>>>> 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
> >>>>
> >>>>
> >>>>
> >>>
> >>
> >>
> >>
> >
>
>


0
terre081 (3244)
12/5/2005 3:13:41 PM
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" <terre08@mvps.org> wrote in message 
news:uQSZ45a%23FHA.2816@tk2msftngp13.phx.gbl...
> 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" <someone@microsoft.com> wrote in message
> news:eVEYlsa%23FHA.2440@TK2MSFTNGP15.phx.gbl...
>> 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" <terre08@mvps.org> wrote in message
>> news:O1R9brR%23FHA.1148@tk2msftngp13.phx.gbl...
>> > 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" <someone@microsoft.com> wrote in message
>> > news:e2JJAIK%23FHA.2708@TK2MSFTNGP12.phx.gbl...
>> >> 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" <terre08@mvps.org> wrote in message
>> >> news:%23rg93dH%23FHA.160@TK2MSFTNGP12.phx.gbl...
>> >>> 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" <someone@microsoft.com> wrote in message
>> >>> news:e1DKfl$9FHA.3480@TK2MSFTNGP10.phx.gbl...
>> >>>> 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" <terre08@mvps.org> wrote in message
>> >>>> news:ujAPId%239FHA.3760@TK2MSFTNGP14.phx.gbl...
>> >>>>> 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" <someone@microsoft.com> wrote in message
>> >>>>> news:OoqgNp99FHA.356@TK2MSFTNGP12.phx.gbl...
>> >>>>> 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
>> >>>>
>> >>>>
>> >>>>
>> >>>
>> >>
>> >>
>> >>
>> >
>>
>>
>
> 


0
someone913 (592)
12/5/2005 3:33:11 PM
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?action=getinfo&userid=21074
View this thread: http://www.excelforum.com/showthread.php?threadid=490378

0
12/5/2005 3:38:21 PM
Reply:

Similar Artilces:

auto sort?
Is there any way to make a dated entry insert itself in the worksheet in the correct order without doing a sort operation, or inserting a new row? would be a big help for my reservations workbook thanks D You could try the worksheet change event: Private Sub Worksheet_Change(ByVal Target As Range) <<Sort Code>> End Sub This should re-sort your selection everytime the worksheet changes. Hope this helps Sunil Jayakumar "Dave" <post@site.com> wrote in message news:%23czt38POFHA.2132@TK2MSFTNGP14.phx.gbl... > Is there any way to make a dated entry insert ...

Sort by Credit Card Type on EDC Detail Report
What do I have to do to get my EDC Detail Report to show different credit card types, like Visa, MC or Amex. Now under Tender Type, it just shows Credit card...well...I kinda knew that. How do I fix it? add tender types for each credit card type rather than just "credit card". then z out. all transactions after this change will reflect what you want in the reports. "tl" <tl@discussions.microsoft.com> wrote in message news:A54EEC95-208C-4962-A089-84B22A67632A@microsoft.com... > What do I have to do to get my EDC Detail Report to show different credit >...

Oldest date for Duplicate Cust. #
I'm trying to get the oldest date associated with a customer number, and in the Cust# column, i'll have many duplications of the same customer number. Let's say A is "Date", and B is "Cust#". (I won't be able to allow my users to sort the data, so i'll need a formula that returns either the oldest date, or the cell which contains the oldest date.) Any help is much appreciated! Nevermind. I found it using Google/Groups. {=MIN(IF($B$1:$B$10=B1,$A$1:$A$10))} >-----Original Message----- >I'm trying to get the oldest date associated with a...

Dates #9
The problem of a date code... I need to address this so that fo example, 5/6/04 can be correctly entered as either 5th of June or 6t of May, depending from where the date emanted. regards -- Message posted from http://www.ExcelForum.com Couldn't you format the cell as mmmm dd, yyyy so that the user sees what date they entered in a non-ambiguous manner right away? Or maybe provide 3 inputs: Month, day, and year. You could combine them elsewhere. "adn4n <" wrote: > > The problem of a date code... I need to address this so that for > example, 5/6/04 can be c...

Converting Opportunity to Account
How do I convert an Opportunity to a Account? Thanks This is not possible, opportunities are opportunities for existing accounts/contacts. This is why when you convert a Lead to an Opportunity you must select the potential customer also. Regards Dan "circulent" wrote: > How do I convert an Opportunity to a Account? Thanks how would I select a potential customer? does this mean I have to create an account for them as well? it wouldnt make sense since they may not ending up being an account. thoughts? "Dan Quinton" wrote: > This is not possible, opportunities...

Qrp Date functions
Where do I find the various functions to modify the Reports like cast(DatePart(Dd,[Transaction].Time) as nvarchar) and others and what they mean???? Barry Found the information at MSDN Transact-SQL Reference Barry "Barry L" <barryl@eryanjewelers.com> wrote in message news:usd3uP1CIHA.1188@TK2MSFTNGP04.phx.gbl... > Where do I find the various functions to modify the Reports > like cast(DatePart(Dd,[Transaction].Time) as nvarchar) and others > and what they mean???? > > Barry > ...

Fractions Number format
Is it possible to do a custom number format for cells such that fractions are displayed. I'm guessing not because there'd be a limit on what fractions you could display, but I would probably only be interested in showing a half as the little 1/2 (alt,0189 I think) rather than .5 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.478 / Virus Database: 275 - Release Date: 06/05/03 Lee There are a limited number of fractions that can be shown under Format>Cells>Fractions. Gord Dibben Excel MVP - XL97 SR2 & ...

date tracking
I am entering clients into a 2007 Excel spreadsheet. How do I make the entry turn color when 14 days have passed? Gene This is a multi-part message in MIME format. ------=_NextPart_000_0018_01CAC8D4.5688AC60 Content-Type: text/plain; format=flowed; charset="UTF-8"; reply-type=original Content-Transfer-Encoding: 7bit As part of the "client" entries, do you enter the date the client was entered? This would be the key in doing this task. In a cell on the worksheet you could enter the formula for today's date like this =TODAY(). Then use the con...

Unable to sort on customer CRM 4.0
Hi When you try to change the campaign response views to have customer as sorting CRM gives an error. You can't even click on customer column to sort when it's now customer that is default sorting. Is this a bug ? same here. I think its a bug "Help needed" wrote: > Hi > > When you try to change the campaign response views to have customer as > sorting CRM gives an error. You can't even click on customer column to sort > when it's now customer that is default sorting. > > Is this a bug ? > > > > ...

Some Excel formatting functions taking a long time to work #2
Excel 2000 SP3 When I use some formatting functions for the first time in a session, such as bold, increasing font size etc, it takes up to 30 seconds to work. Meantime Excel is locked up until it completes that formatting call. I suspect faulty DLL? Has anyone experienced this? How to fix (other than a complete re- install) ? Any advice appreciated. Piri On Nov 5, 8:39=A0am, Piri <wiremu.pare...@hotmail.com> wrote: > Excel 2000 SP3 > When I use some formatting functions for the first time in a > session, such as bold, increasing font size etc, it takes =A0up to 30 > secon...

Short time vs short date
In my form I have a fldOutOfRoom which the user inputs a short time into the field, i.e, 0900. I have the following code in the open event of another form called frmRNnotes: If DateDiff("h", Forms!frmPtDemographicNew!frmVisitNewEdit.Form!OutOfRoom, Now) >= 1 Then Me.cmdRNnotesEdit.Visible = False This code gives the RN one hour to complete a note and then he/she can no longer edit the note. What I want to know is the Short Time format going to let the cmdRNnotesEdit button be visible everyday within one hour of the of the original time? That is, is the short time just a tim...

US and UK online banking affiliates
I am thinking about buying MS Money 2004 to assist in managing my personal finances, but I have accounts in the US and the UK. Can Money cope with this? It looks like there are US and UK versions and a UK international version (I have downloaded and played with the trial version), but I can't figure out if it does what I want it to if you buy the US version you have access to some US banks, If you buy the UK version, you have access to some UK banks. If you buy the international version, I don't think there are any banks !! "Jo" <anonymous@discussions.micros...

How to save Japanese characters in CSV format of Excel ?
Hi, I would like to know, how to save the Doubly-Byte characters.For example, Japanese in CSV of Excel.Here are the steps, I tried. 1. Open Excel 2. Copy pasted the Japanese charactesrs in a cell.It is displaying the characters correctly. 3. Save as CSV(Comma seperated file) It is saving as ??????. Thanks in advance for the info. ...

Date Calculation
Good Afternoon, I have a DB which tracks training of employees. The grace periods allowed with the training is that new training can be completed within 90 days of the expiry date without changing the anniversary date (e.g. the training is due on 1 April 2010, the employee conducts the training on 2 January 2010 but gets to keep the 1 April anniversary date). The table I am working with is mainly based on the date of training and the training type (which determines whether the training expires on the 1st of the 13th, 25th or 37th months or if it keeps the same date); what I w...

sort by day of the week?
Excel 2002 I have 10 years of grain prices...one price for each trading day of th year. In 'A' we have the date, in 'B' the price. What I need to do i get a list of prices for Mondays, Tuesdays, Wednesdays, etc... Possible? (I really, really don't want to do this manually) Than -- Message posted from http://www.ExcelForum.com add a column = weekday(a1,1) etc and do a sort on this colum -- Message posted from http://www.ExcelForum.com Thank you. You saved me 3 or 4 hours of excruciating data entry. Thi was the last step of a vital report. Now I can do my char...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

date modified
I have two sheets Data and Summary The "data" sheets macro extracts data from external file and paste into "Data" sheet Everytime the m acro is run to get latest data... The macro delete all contents of the "data" and then paste new data into the "data" sheet. Is there a way.. I can put a date on the "summary" sheet, when was the time the macro was run ( or in other words.. the data updated) This little macro records the date in the selected cell and formats it: Sub Macro2() Dim d As Date Dim s As String d = Now() s...

Date function quit working
Hi, I have an Access 2002 application that I have been running on Windows XP SP2 without issue. I just installed the application (running in Access Runtime) on a Windows Vista Home Premium machine. Now, anywhere I used the =Date() function, it fails and just shows #Name? I also have a subform on one of my forms that has now gone blank. It also uses the date function. I had this problem when I converted to Windows XP several years ago and updating the OWC10.dll to version 6619 fixed both issues. However, everything I have read says that reference file makes no difference to the Access...

How do I convert a concatenated value into a know value
Hi all I am trying to get the results of a multiple input table, which get concatenated, read out as usable values eg. If the concatenated values are for example *llbbt* , I need this t be read as Simon, or *lbttd* must result in Fred etc... I will attact the spreadsheet. Thanks Colli Attachment filename: book3.xls Download attachment: http://www.excelforum.com/attachment.php?postid=54116 -- Message posted from http://www.ExcelForum.com You are probably better off by describing your problem, most regulars won't open files.. -- Regards, Peo Sjoblo...

Date format 04-11-06
Hi, Is there a possibility that the dates used in all the entities are not in the default format mm/dd/yyyy but in dd/mm/yyyy. I already adapted the Organisatonal settings, that only adapts the journal but nog the dates of an appointment. Does anyone have an idea? Thanks, ...

sorts not sorting
OK- Thanks to everyones help I now i have a list i can work with... but there are a few problems.... the zips are all 5 digit and now i have to sort them... the problem is, THEY WONT SORT CORRECTLY!!!!!!! The numbers are not in order... some are but a lot are not... is there anyway to fix this???? i need to sort them by zip code but cant seem to get them to do it correctly.. any ideas??? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ You are going to be far better of...

Sorting by file extension in a worksheet
Hello, I have an excel worksheet which has a list of file names from a directory and various stats about each file. I need to be able to sort them by the file extension. Eg. c:\documents\folderA\picture1.eps c:\documents\folderB\document.doc I would like to sort them by the ".eps" extension. I have tried using the Data,Sort menu and something like *?*.eps as the criteria. But am obvisouly missing something. Any help would be most appreciated. Thanks Karl You could use a helper column to extract just the extension then include this in your sort range and sort on the helper col...

convert to stored proc
Hello. I have been given a conversion assignment to convert Access db (frontend) with sql server 2005 backend to be included in a new webapp that is being constructed. Unfortunately, my stored proc creating is a bit weak and I'm not afraid to admit it. With that said, I came across an Access query that is puzzling me. Here it is; SELECT Purchase Orders *, POSO Relationships.SONumber AS RelatedSONumber, IIf([POSOType], [POSOType], 'Standard') AS POSOTypeFull, POSORelationships.MTXRMA, POSORelationships.SupplierRMA, POSORelationships.POSOType FROM PurchaseOrders ...

Formatting in CSV
Hi I create a Comma Separated Value file (csv) from my VB application, then open that file with Excel. Excel recognises the commas, and splits the records into separate fields as expected. Can I set properties for rows or columns or cells ? Like bolding, or font size, etc. Thanks Robert -- RobertLees ------------------------------------------------------------------------ RobertLees's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26580 View this thread: http://www.excelforum.com/showthread.php?threadid=486488 Yes. Even though the .csv file contains no fo...

How to Identify Records with Overlapping Dates
I need to be able to systematically identify any instance where there are overlapping date ranges in a data set. I need to pull records like those listed below out of a larger data set. I previously posted a question similar to this and was advised to pull the same table in a query, match on Member Number, and qualify that the product code from TableA did not match product code in TableA_1 where the Begin Date TableA was < Term date TableA_1 and TableA_1 Begin Date < TableA End Date. Which worked great for me finding overlapping records where the product was different, bu...