Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    New Lounger
    Join Date
    Oct 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date Gap analysis (Excel 2000)

    I have an excel file with a date column. I want to be able to sort the work sheet by that column and then run though the column and list all the gaps in the dates between consecutive records. For instance if cell C1 = 11/12/03 and C2 = 11/17/03 then the result would be 11/13/03 - 11/16/03. If C3=11/17/18 or 11/18/03 then the result would be No Gap. Not sure that this is possible?
    Thanks.

  2. #2
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Gap analysis (Excel 2000)

    There may be an easier way... but I created a little function using DateDiff() that will return a string with the results as you want them...
    See the attached example and the code in Module1...

    HTH

    P.S. There's no error handling or anything in there... It's just a quick example...

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Gap analysis (Excel 2000)

    Say that the dates are in column C, as in your example. In cell D2, enter the following formula:<pre>=IF(C2-C1=1,"No Gap",TEXT(C1,"mm/dd/yy")&" - "&TEXT(C2,"mm/dd/yy"))</pre>

    and fill down as far as needed.

  4. #4
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Gap analysis (Excel 2000)

    <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> I knew there'd be an easier way... Leave it to me to re-invent the wheel...
    Sorry Hans! I should just wait for you to share your wisdom...

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Gap analysis (Excel 2000)

    If an Excel formula counts as wisdom, it's easy to become a philosopher. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  6. #6
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Gap analysis (Excel 2000)

    It's not the formula itself... It's the fact that you know ALL of the formulas!!! LOL

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Gap analysis (Excel 2000)

    Wish that were true... <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

  8. #8
    New Lounger
    Join Date
    Oct 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Gap analysis (Excel 2000)

    Thanks to both of you for sharing the knowledge.
    Is there is way to turn this into a macro/script that would automatically run for a selected column?
    For example, if the dates are in column C, it would automatically populate column D with this formula for every record in C that had an entry?

    Thanks!

  9. #9
    New Lounger
    Join Date
    Oct 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Gap analysis (Excel 2000)

    I have a couple hundred excel files to work with that contain multiple date columns that this needs to be done on - so was just wondering if there was a way to make it a little more automated.
    Thanks.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Date Gap analysis (Excel 2000)

    Does the macro need to check for blanks within the data set or should you just fill in the formula from D2 to the last filled cell in C?
    Do you want to keep the formulas in it or do you want the macro to just put the result of the formula?

    What all do you want to automate?
    Should the macro work with just one sheet, all sheets in the activeworkbook, selected sheets in the activeworkbook, sheets with particular names in all open workbooks?

    You could do alot of it yourself, using the macro recorder and then generalize it.

    Steve

  11. #11
    New Lounger
    Join Date
    Oct 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Gap analysis (Excel 2000)

    Thanks Steve, I did attempt to use the macro recorder but was not sure how to generalize the column location, or the auto-fill to match the amount of records in the work sheet as that will vary.....

    First i would like the macro to sort the entire sheet by the date column, C. (If it is not always C, can this be a parameter, or will i need to set this in the macro?) For a given date column, C, fill in the value in column D for every record in column C.
    Macreo should only do the active sheet.

    Thanks.

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Gap analysis (Excel 2000)

    The macro below will insert a column to the right of the column containing the active cell, and will then put Hans' formula into rows 2 through the last row containing data in the column containing the active cell.

    <pre>Option Explicit
    Public Sub AddFormula()
    Dim I As Long, lLastRow As Long, lCol As Long
    Dim oCell As Range
    lCol = Selection.Column
    Set oCell = ActiveSheet.Cells(1, lCol)
    lLastRow = ActiveSheet.Cells(65536, lCol).End(xlUp).Row - 1
    oCell.Offset(0, 1).EntireColumn.Insert
    For I = 1 To lLastRow
    oCell.Offset(I, 1).FormulaR1C1 = _
    "=IF(RC[-1]-R[-1]C[-1]=1,""No Gap"",TEXT(R[-1]C[-1],""mm/dd/yy"")&"" - ""&TEXT(RC[-1],""mm/dd/yy""))"
    Next I
    oCell.Offset(0, 1).EntireColumn.AutoFit
    End Sub
    </pre>

    Legare Coleman

  13. #13
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Gap analysis (Excel 2000)

    Steve:
    <hr>Then select the cell and move the mouse to the bottom left corner of the cell <hr>

    Not wishing to be picky (honest! <img src=/S/innocent.gif border=0 alt=innocent width=20 height=20> ) but, to avoid confusion for anyone not familiar with Autofill, you meant, of course, the bottom right corner. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Tony.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Date Gap analysis (Excel 2000)

    <P ID="edit" class=small>(Edited by sdckapr on 06-Nov-03 07:49. Corrected "brain-fart". [I do know the difference between right and left- really] Thanks, Tony.)</P>Don't understand the need for a macro.
    Why not (after you sort the data) put the formula in the top cell. Then select the cell and move the mouse to the bottom right corner of the cell (the cursor will change from a fat plus to a narrow plus. When it is the narrow plus dbl-click and the formula will be copied down the rows to the bottom of the data set.

    Steve

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Date Gap analysis (Excel 2000)

    Tony,
    <img src=/S/doh.gif border=0 alt=doh width=15 height=15> Thanks for pointing out my mistake. I changed the post to be accurate and noted my error (as a penance).

    I very seldom things up mix <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    steve

Page 1 of 2 12 LastLast

Posting Permissions

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