Results 1 to 8 of 8
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post
    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
    Regards,
    Rudi

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post
    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
    Regards,
    Rudi

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    [quote name='Rudi' post='775207' date='14-May-2009 10:48']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?[/quote]
    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!
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    [quote name='Rudi' post='775207' date='14-May-2009 10:48']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[/quote]
    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"))
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    [quote name='WebGenii' post='775249' date='14-May-2009 13:29']=TIMEVALUE(TEXT(A3,"hh:mm:ss"))[/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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post
    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!!!
    Regards,
    Rudi

  7. #7
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    [quote name='JohnBF' post='775265' date='14-May-2009 15:05']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.[/quote]


    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).
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    Quote Originally Posted by WebGenii' post='775910 View Post
    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)
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •