+ Reply to Thread
Results 1 to 17 of 17

Finding Gaps in time Series

  1. #1
    Registered User
    Join Date
    06-03-2013
    Location
    Barrow, Alaska
    MS-Off Ver
    Excel 2007
    Posts
    12

    Finding Gaps in time Series

    Hi Everybody,
    I am trying to find gaps in my time sequence.

    Each successive row increases by 30 minutes. My time format appear as

    00:15:00
    00:45:00
    01:15:00
    01:45:00
    02:45:00 ---
    03:15:00
    04:15:00 ---

    I need to find every cell that is not the immediate successive and that skipped the 30 minutes difference.
    Is there any way?

    Thank you all in advance

    Sal

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Finding Gaps in time Series

    In A1 cell
    00:15:00
    and the rest of the data down...

    In B2 Cell
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Drag it down...


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Registered User
    Join Date
    06-03-2013
    Location
    Barrow, Alaska
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Finding Gaps in time Series

    Thank you for the quick response,
    I tried but It gives me error and keeps highlighting the hour
    time series.JPG

    I checked if the time in cell is considered a number and the response is TRUE.

    Thanks again...any advice would be precious.

    H

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Finding Gaps in time Series

    Why you have selected the entire Data range? it should be single cell comparison like A2-A1. Please refer the suggested formula and your sceenshot formula and do the necessary correction.

  5. #5
    Registered User
    Join Date
    06-03-2013
    Location
    Barrow, Alaska
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Finding Gaps in time Series

    Sorry that was just because copying the screenshot I must have touched something but even in two cells comparison (A2-A1) gives me the same error.
    thank you

    H

  6. #6
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Finding Gaps in time Series

    It should work.... Please attach a sample workbook if possible

  7. #7
    Registered User
    Join Date
    06-03-2013
    Location
    Barrow, Alaska
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Finding Gaps in time Series

    Thank you
    How do I attach here the worksheet?

    H

  8. #8
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Finding Gaps in time Series

    Please refer my signature area for step by step instruction

  9. #9
    Registered User
    Join Date
    06-03-2013
    Location
    Barrow, Alaska
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Finding Gaps in time Series

    YS_2006_EC_all.xlsx

    Here it is!
    Thanks a lot.
    H


  10. #10
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Finding Gaps in time Series

    Revised Formula in B2 Cell
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Drag it down...

    Refer the attached file for details
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    06-03-2013
    Location
    Barrow, Alaska
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Finding Gaps in time Series

    THANKS!!!
    You are simply great....and I am a poor excel novice.
    Thank you again,
    you saved me a lot of time!

    H.

  12. #12
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Finding Gaps in time Series

    Just come here we help you

    Glad it helps you and thanks for the feedback

  13. #13
    Registered User
    Join Date
    06-03-2013
    Location
    Barrow, Alaska
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Finding Gaps in time Series

    Absolute number ONE!
    The only thing it flags in every passage between 23:45:00 and 00:15:00
    but it's a small price to pay!

    Thank you!

  14. #14
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Finding Gaps in time Series

    Oopss... Not noticed it...

    Try this revised formula, which will take care of that issue

    In B2 Cell
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Drag it down...

  15. #15
    Registered User
    Join Date
    06-03-2013
    Location
    Barrow, Alaska
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Finding Gaps in time Series

    Sorry but It gives me error.
    I don't want to bother too much,
    as it was before was perfect because I then just need to insert the empty rows by hand and it's done!
    Thank you very much
    H

  16. #16
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Finding Gaps in time Series

    Please refer the attached file for details
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    06-03-2013
    Location
    Barrow, Alaska
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Finding Gaps in time Series

    I have no words....
    thank you.
    Simply genius.


    H

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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