+ Reply to Thread
Results 1 to 3 of 3

need to filter based on difference in time between records

  1. #1
    Registered User
    Join Date
    03-17-2009
    Location
    Chicago, Illinois
    MS-Off Ver
    Excel 2003
    Posts
    2

    need to filter based on difference in time between records

    Thanks in advance to any help given I have been struggling for 2 days trying to figure out a way to do this. I have animal locations that were recorded every 10 minutes and I want to remove all locations that are not at least 50 minutes apart. I have over 10000 records making it difficult to do by hand. I have combined date and time so that it is in serial format if that makes it any easier. I have attached a small segment of data that will hopefully be helpful.

    Thanks
    Attached Files Attached Files
    Last edited by wildliferesearch; 03-17-2009 at 05:26 PM.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    16,037

    Re: need to filter based on difference in time between records

    In first row add FILTER, and then you can choose GREATER THAN... (50)
    Never use Merged Cells in Excel

  3. #3
    Registered User
    Join Date
    03-17-2009
    Location
    Chicago, Illinois
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: need to filter based on difference in time between records

    What I am trying to do does not seem to work with basic filtering.
    I want to only keep records that have at least 45 minutes between them and filter out all others. As an example I have included a small set of data and I want to keep only the data that has <-- next to it. I hope this makes what I am looking for a little clearer. I also attached a small amount of data as well I could not figure out how to space it appropriately so I alternated bold and not bold for each column.

    coy-id date time serial-date.time
    14 9/20/02 2:14 37519.0930555556<--
    14 9/20/02 2:24 37519.1
    14 9/20/02 2:35 37519.1076388889
    14 9/20/02 2:46 37519.1152777778
    14 9/20/02 2:55 37519.1215277778 <--
    14 9/20/02 3:05 37519.1284722222
    14 9/20/02 3:15 37519.1354166667
    14 9/20/02 3:25 37519.1423611111
    14 9/20/02 3:36 37519.15
    14 9/20/02 3:45 37519.15625 <--
    14 9/20/02 3:54 37519.1625
    14 9/26/02 4:13 37525.1756944444
    14 9/26/02 4:24 37525.1833333333
    14 9/26/02 4:34 37525.1902777778 <--
    14 9/26/02 4:43 37525.1965277778
    14 9/26/02 4:54 37525.2041666667
    14 9/26/02 5:04 37525.2111111111 <--
    14 9/26/02 5:15 37525.21875
    14 9/26/02 5:25 37525.2256944444
    14 9/26/02 5:33 37525.23125
    14 9/26/02 5:44 37525.2388888889
    14 9/26/02 5:54 37525.2458333333<--
    14 10/7/02 20:20 37536.8472222222<--
    14 10/7/02 20:30 37536.8541666667
    14 10/7/02 20:40 37536.8611111111
    14 10/7/02 20:50 37536.8680555556
    14 10/7/02 21:00 37536.875
    14 10/7/02 21:10 37536.8819444444 <--
    14 10/7/02 21:20 37536.8888888889
    14 10/7/02 21:30 37536.8958333333
    14 10/7/02 21:40 37536.9027777778
    14 10/7/02 21:50 37536.9097222222
    Last edited by wildliferesearch; 03-18-2009 at 10:16 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1