Date Gap analysis (Excel 2000)

Topic

New Reply

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.

Reply | Quote

Replies

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…

Reply | Quote

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…

Reply | Quote

Say that the dates are in column C, as in your example. In cell D2, enter the following formula:

=IF(C2-C1=1,"No Gap",TEXT(C1,"mm/dd/yy")&" - "&TEXT(C2,"mm/dd/yy"))

and fill down as far as needed.

Reply | Quote

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…

Reply | Quote

If an Excel formula counts as wisdom, it’s easy to become a philosopher.

Reply | Quote

It’s not the formula itself… It’s the fact that you know ALL of the formulas!!! LOL

Reply | Quote

Wish that were true…

Reply | Quote

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!

Reply | Quote

(Edited by sdckapr on 06-Nov-03 07:49. Corrected “brain-fart”. Thanks, Tony.)

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

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

Reply | Quote

Thank you – you’re the best! This works great! Thank you!

Reply | Quote

Thank you – you’re the best! This works great! Thank you!

Reply | Quote

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.

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Steve:
[indent]

---

Then select the cell and move the mouse to the bottom left corner of the cell

---

[/indent]

Not wishing to be picky (honest! ) but, to avoid confusion for anyone not familiar with Autofill, you meant, of course, the bottom right corner.

Tony.

Reply | Quote

Tony,
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

steve

Reply | Quote

Tony,
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

steve

Reply | Quote

Steve:

Be assured, the vast amount I have learnt from your posts far outweighs an occasional slip.

for all the help you give on this lounge.

Tony.

Reply | Quote

Steve:

Be assured, the vast amount I have learnt from your posts far outweighs an occasional slip.

for all the help you give on this lounge.

Tony.

Reply | Quote

Steve:
[indent]

---

Then select the cell and move the mouse to the bottom left corner of the cell

---

[/indent]

Not wishing to be picky (honest! ) but, to avoid confusion for anyone not familiar with Autofill, you meant, of course, the bottom right corner.

Tony.

Reply | Quote

(Edited by sdckapr on 06-Nov-03 07:49. Corrected “brain-fart”. Thanks, Tony.)

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

Reply | Quote

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!

Reply | Quote

Wish that were true…

Reply | Quote

It’s not the formula itself… It’s the fact that you know ALL of the formulas!!! LOL

Reply | Quote

If an Excel formula counts as wisdom, it’s easy to become a philosopher.

Reply | Quote

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…

Reply | Quote

Say that the dates are in column C, as in your example. In cell D2, enter the following formula:

=IF(C2-C1=1,"No Gap",TEXT(C1,"mm/dd/yy")&" - "&TEXT(C2,"mm/dd/yy"))

and fill down as far as needed.

Reply | Quote