How does one autofilter time?

Topic

New Reply

Hi,

How does one autofilter all time values between 12am and 6am that span over several days?

I only want records in a list who’s time is between 12am and 6am. the list has a CALL Column that has a date and time stored in it…((similar to the format that =NOW() uses.)) These dates and times span several days, but I am only interested to extract CALLS that were made in the early morning between 12 and 6am irrespective of day!

TIA

Reply | Quote

Replies

Another Question…

I have just noticed that the CALL column content is actually TEXT (ie… it is not a valid date / time format…the import has converted the entry to :
May 5 2009 1:18AM)

So…is there a way to convert this to a date/time format or can we filter the 12am-6am request even if it is text?

TX

Reply | Quote

I have just noticed that the CALL column content is actually TEXT (ie… it is not a valid date / time format…the import has converted the entry to : May 5 2009 1:18AM)

So…is there a way to convert this to a date/time format or can we filter the 12am-6am request even if it is text?

See if it will convert with =TIMEVALUE(SUBSTITUTE(TRIM(RIGHT(A1,7)),RIGHT(A1,2),” “)&RIGHT(A1,2))
(optionally cell-formatted as time), and then filter. Very lightly tested, so be sure it works!

Reply | Quote

Hi John…(And WebGenii)

TX a stack!

That formula solved my problem. It created the correct time value which allowed me to apply the necessary filter. It worked great!!

Cheers!!!

Reply | Quote

Another Question…

I have just noticed that the CALL column content is actually TEXT (ie… it is not a valid date / time format…the import has converted the entry to :
May 5 2009 1:18AM)

So…is there a way to convert this to a date/time format or can we filter the 12am-6am request even if it is text?

TX

Easier to filter on time – but one has to remove the date from the time.
I added a column using the following to extract the time and then reformated using timevalue (this will give all calls the same date).
Filtering in 2007 is easy after this.

=TIMEVALUE(TEXT(A3,”hh:mm:ss”))

Reply | Quote

=TIMEVALUE(TEXT(A3,”hh:mm:ss”))

No fair not telling how you removed the date. This would conversion be much easier if there was space between the time and the “AM/PM” indicator. I would usually handle this with Find and Replace.

Reply | Quote

No fair not telling how you removed the date. This would conversion be much easier if there was space between the time and the “AM/PM” indicator. I would usually handle this with Find and Replace.

um I assumed the date was in date format. The formula simply extracts the time from that date. Then all times will have the same date (but you knew that anyway).

Reply | Quote

I have just noticed that the CALL column content is actually TEXT (ie… it is not a valid date / time format…the import has converted the entry to: May 5 2009 1:18AM)

Reply | Quote