I've hit a road block and have spent a lot of time trying
to over come it.

Field One is of category 'general' and will always
contian dates in the following format, "dd.MMM. YYYY" (the
space is there intentionally). Field Two is a custom
category with the following format "h:mm:ss" where h can
be either one or two digits.

Due to the nature of the system, I get data from last
year. I'm only interested in data from a specific date &
time (that I'll get from a file) to the most recent date &
time.

How do I delete all the extrenuous information?

I'd appreciate any help you can give
Thanks

Hafeez Esmail

Have you tried using the custom option of the autofilter dropdown.

It is unclear what is actually in your cells. If it is stored as a date,
then if you format it as General, it should look something like
? clng(date)
37937

That is the date serial for today. If it has a fraction on the end
37937.5 would be 12 noon today. Time is stored as a fraction of a day.

If your values are actually stored as excel date/time, then you should be
able to filter with the autofilter.

--
Regards,
Tom Ogilvy


"Hafeez Esmail" wrote in message
...
I've hit a road block and have spent a lot of time trying
to over come it.

Field One is of category 'general' and will always
contian dates in the following format, "dd.MMM. YYYY" (the
space is there intentionally). Field Two is a custom
category with the following format "h:mm:ss" where h can
be either one or two digits.

Due to the nature of the system, I get data from last
year. I'm only interested in data from a specific date &
time (that I'll get from a file) to the most recent date &
time.

How do I delete all the extrenuous information?

I'd appreciate any help you can give
Thanks

Hafeez Esmail

The autofilter doesn't work...

Here's some sample data that has been sorted by field 1
In this selection, the latest date is: 06.NOV. 2003

field 1 field 2
01.NOV. 2003 20:27:50
01.OCT. 2003 14:38:06
05.OCT. 2003 22:19:50
05.OCT. 2003 22:21:56
06.NOV. 2003 2:08:15
06.NOV. 2003 0:23:03
14.OCT. 2003 8:55:46
15.MAR. 2002 8:34:30
15.MAR. 2002 9:00:07
15.MAR. 2002 14:10:25
15.OCT. 2003 5:24:01

Notice how there are some 2002 records. I want the data
to be sorted chrononlogically so that it's easier to
delete every record that has a date previous to (for
example let's say) 14.Oct. 2003

I want this sorting and deleting process to be fully
automated.

Nothing seems to change when I change the data type to
General or even Data.

Any help would be greatly appreciated!
Hafeez Esmail

-----Original Message-----
Have you tried using the custom option of the autofilter
dropdown.

It is unclear what is actually in your cells. If it is
stored as a date,
then if you format it as General, it should look
something like
? clng(date)
37937

That is the date serial for today. If it has a fraction
on the end
37937.5 would be 12 noon today. Time is stored as a
fraction of a day.

If your values are actually stored as excel date/time,
then you should be
able to filter with the autofilter.

--
Regards,
Tom Ogilvy


"Hafeez Esmail" wrote in message
...
I've hit a road block and have spent a lot of time
trying
to over come it.

Field One is of category 'general' and will always
contian dates in the following format, "dd.MMM. YYYY"
(the
space is there intentionally). Field Two is a custom
category with the following format "h:mm:ss" where h can
be either one or two digits.

Due to the nature of the system, I get data from last
year. I'm only interested in data from a specific date
&
time (that I'll get from a file) to the most recent
date &
time.

How do I delete all the extrenuous information?

I'd appreciate any help you can give
Thanks

Hafeez Esmail


.

You miss the point. It is how excel sees the data - if it sees it as a
string/text, then it is sorted as string/text. If it is stored as a date,
then it will be sorted chronologically. From the data you show, it looks
like you are doing a text sort. It doesn't make any difference how you
format the cell if the data is text. Formatting the cell doesn't change how
the data is stored.

in a separate column try putting in a formula

=DateValue(A1)

(assuming your dates are in A1). Then if that produces a number like
37937

or for the first date you show
? clng(datevalue("Nov 1, 2003"))
37926


then you can format that cell as you want it to appear, then drag fill down
the column. You can then do the same with your times
=TimeValue(B1)

or =DateValue(A1)+TimeValue(B1) use a custom format like dd.mmm. yyyy
hh:mm:ss

Then sort on those columns

--
Regards,
Tom Ogilvy

"Hafeez Esmail" wrote in message
...
The autofilter doesn't work...

Here's some sample data that has been sorted by field 1
In this selection, the latest date is: 06.NOV. 2003

field 1 field 2
01.NOV. 2003 20:27:50
01.OCT. 2003 14:38:06
05.OCT. 2003 22:19:50
05.OCT. 2003 22:21:56
06.NOV. 2003 2:08:15
06.NOV. 2003 0:23:03
14.OCT. 2003 8:55:46
15.MAR. 2002 8:34:30
15.MAR. 2002 9:00:07
15.MAR. 2002 14:10:25
15.OCT. 2003 5:24:01

Notice how there are some 2002 records. I want the data
to be sorted chrononlogically so that it's easier to
delete every record that has a date previous to (for
example let's say) 14.Oct. 2003

I want this sorting and deleting process to be fully
automated.

Nothing seems to change when I change the data type to
General or even Data.

Any help would be greatly appreciated!
Hafeez Esmail

-----Original Message-----
Have you tried using the custom option of the autofilter
dropdown.

It is unclear what is actually in your cells. If it is
stored as a date,
then if you format it as General, it should look
something like
? clng(date)
37937

That is the date serial for today. If it has a fraction
on the end
37937.5 would be 12 noon today. Time is stored as a
fraction of a day.

If your values are actually stored as excel date/time,
then you should be
able to filter with the autofilter.

--
Regards,
Tom Ogilvy


"Hafeez Esmail" wrote in message
...
I've hit a road block and have spent a lot of time
trying
to over come it.

Field One is of category 'general' and will always
contian dates in the following format, "dd.MMM. YYYY"
(the
space is there intentionally). Field Two is a custom
category with the following format "h:mm:ss" where h can
be either one or two digits.

Due to the nature of the system, I get data from last
year. I'm only interested in data from a specific date
&
time (that I'll get from a file) to the most recent
date &
time.

How do I delete all the extrenuous information?

I'd appreciate any help you can give
Thanks

Hafeez Esmail


.

I think I see your point now. Excel sees the data as
General so it's sorting it as General.

I applied the DateValue function to it and the result was
#VALUE. I guess this means that it's stored as text.

I replaced every "." with a "-" within Field 1
(Find/Replace) and that automatically converted it to a
date! I confirmed this by verifying the sort was
chronological for dates.

I figure I can get this in code by using the trick you
taught me......"record macro".

Thanks for all your help!!
Hafeez Esmail


-----Original Message-----
You miss the point. It is how excel sees the data - if
it sees it as a
string/text, then it is sorted as string/text. If it is
stored as a date,
then it will be sorted chronologically. From the data
you show, it looks
like you are doing a text sort. It doesn't make any
difference how you
format the cell if the data is text. Formatting the cell
doesn't change how
the data is stored.

in a separate column try putting in a formula

=DateValue(A1)

(assuming your dates are in A1). Then if that produces a
number like
37937

or for the first date you show
? clng(datevalue("Nov 1, 2003"))
37926


then you can format that cell as you want it to appear,
then drag fill down
the column. You can then do the same with your times
=TimeValue(B1)

or =DateValue(A1)+TimeValue(B1) use a custom format
like dd.mmm. yyyy
hh:mm:ss

Then sort on those columns

--
Regards,
Tom Ogilvy

"Hafeez Esmail" wrote in message
...
The autofilter doesn't work...

Here's some sample data that has been sorted by field 1
In this selection, the latest date is: 06.NOV. 2003

field 1 field 2
01.NOV. 2003 20:27:50
01.OCT. 2003 14:38:06
05.OCT. 2003 22:19:50
05.OCT. 2003 22:21:56
06.NOV. 2003 2:08:15
06.NOV. 2003 0:23:03
14.OCT. 2003 8:55:46
15.MAR. 2002 8:34:30
15.MAR. 2002 9:00:07
15.MAR. 2002 14:10:25
15.OCT. 2003 5:24:01

Notice how there are some 2002 records. I want the data
to be sorted chrononlogically so that it's easier to
delete every record that has a date previous to (for
example let's say) 14.Oct. 2003

I want this sorting and deleting process to be fully
automated.

Nothing seems to change when I change the data type to
General or even Data.

Any help would be greatly appreciated!
Hafeez Esmail

-----Original Message-----
Have you tried using the custom option of the
autofilter
dropdown.

It is unclear what is actually in your cells. If it is
stored as a date,
then if you format it as General, it should look
something like
? clng(date)
37937

That is the date serial for today. If it has a
fraction
on the end
37937.5 would be 12 noon today. Time is stored as a
fraction of a day.

If your values are actually stored as excel date/time,
then you should be
able to filter with the autofilter.

--
Regards,
Tom Ogilvy


"Hafeez Esmail" wrote in message
...
I've hit a road block and have spent a lot of time
trying
to over come it.

Field One is of category 'general' and will always
contian dates in the following format, "dd.MMM. YYYY"
(the
space is there intentionally). Field Two is a custom
category with the following format "h:mm:ss" where h
can
be either one or two digits.

Due to the nature of the system, I get data from last
year. I'm only interested in data from a specific
date
&
time (that I'll get from a file) to the most recent
date &
time.

How do I delete all the extrenuous information?

I'd appreciate any help you can give
Thanks

Hafeez Esmail


.



.

If your Windows settings recognise "dd/MMM/yyyy" as a valid date format,
then you could add the following formula into another column to convert
your date (which is currently being recognized as text) and time into an
Excel-recognised date:

=DATEVALUE(SUBSTITUTE(SUBSTITUTE(A2," ",""),".","/"))

What this does is to remove the space from your date string and then
changes the full stops(.) to slashes (since these are often recognised
as valid date separators in Windows). Then it takes the resulting string
and applies DATEVALUE to it to turn it into a date.

If you need to add the time to it, then

=DATEVALUE(SUBSTITUTE(SUBSTITUTE(A2," ",""),".","/"))+B2
or
=DATEVALUE(SUBSTITUTE(SUBSTITUTE(A2," ",""),".","/"))+TIMEVALUE(B2)

Use the second one if Excel thinks B2 is a string rather than a time.

Then use this column to filter your data.

--
HTH,
Dianne

In ,
Hafeez Esmail typed:
I've hit a road block and have spent a lot of time trying
to over come it.

Field One is of category 'general' and will always
contian dates in the following format, "dd.MMM. YYYY" (the
space is there intentionally). Field Two is a custom
category with the following format "h:mm:ss" where h can
be either one or two digits.

Due to the nature of the system, I get data from last
year. I'm only interested in data from a specific date &
time (that I'll get from a file) to the most recent date &
time.

How do I delete all the extrenuous information?

I'd appreciate any help you can give
Thanks

Hafeez Esmail

Tom, Dianne, I can't thank you guys enough!
You've been a tremendous help!!!

Thanks again
Hafeez Esmail

-----Original Message-----
If your Windows settings recognise "dd/MMM/yyyy" as a
valid date format,
then you could add the following formula into another
column to convert
your date (which is currently being recognized as text)
and time into an
Excel-recognised date:

=DATEVALUE(SUBSTITUTE(SUBSTITUTE(A2," ",""),".","/"))

What this does is to remove the space from your date
string and then
changes the full stops(.) to slashes (since these are
often recognised
as valid date separators in Windows). Then it takes the
resulting string
and applies DATEVALUE to it to turn it into a date.

If you need to add the time to it, then

=DATEVALUE(SUBSTITUTE(SUBSTITUTE(A2," ",""),".","/"))+B2
or
=DATEVALUE(SUBSTITUTE(SUBSTITUTE(A2," ",""),".","/"))
+TIMEVALUE(B2)

Use the second one if Excel thinks B2 is a string rather
than a time.

Then use this column to filter your data.

--
HTH,
Dianne

In ,
Hafeez Esmail typed:
I've hit a road block and have spent a lot of time
trying
to over come it.

Field One is of category 'general' and will always
contian dates in the following format, "dd.MMM. YYYY"
(the
space is there intentionally). Field Two is a custom
category with the following format "h:mm:ss" where h can
be either one or two digits.

Due to the nature of the system, I get data from last
year. I'm only interested in data from a specific date
&
time (that I'll get from a file) to the most recent
date &
time.

How do I delete all the extrenuous information?

I'd appreciate any help you can give
Thanks

Hafeez Esmail


.