Within a date range

[miyengar]

Hello,
I want to find out in a range of dates which dates are within 5 days of each other.
Thanks for your help.

 

[ranman256]

Dates in 2 cells:

=if(b2-a2<6,"within 5","")

 

[miyengar]

Thanks,
But this just compares 2 cells, right?
I'm looking for each cell to be compared to all others in a range.

 

[MickG]

Is VBA Code acceptable???

 

[miyengar]

Definitely! Idea is to find dates that are within 5 days of each other.

 

[MickG]

Try this for dates in column "A" starting "A2"

[COLOR="Navy"]Sub[/COLOR] MG13Feb28
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, L1 [COLOR="Navy"]As[/COLOR] Range, L2 [COLOR="Navy"]As[/COLOR] Range, nStr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nn [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] n = 1 To Rng.Count
    [COLOR="Navy"]For[/COLOR] nn = n To Rng.Count
        [COLOR="Navy"]If[/COLOR] Not Rng(n).Address = Rng(nn).Address [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]If[/COLOR] Abs(CDbl(DateValue(Rng(n))) - CDbl(DateValue(Rng(nn)))) <= 5 [COLOR="Navy"]Then[/COLOR]
                nStr = nStr & IIf(nStr = "", Rng(n) & " / " & Rng(nn), vbLf & Rng(n) & " / " & Rng(nn))
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] nn
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]If[/COLOR] nStr <> "" [COLOR="Navy"]Then[/COLOR]
    MsgBox "The following dates are within 5 days of each other:-" & vbLf & nStr
[COLOR="Navy"]Else[/COLOR]
    MsgBox "All dates within 5 day Limit"
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[miyengar]

Hi Mick,

Worked like a charm! Many thanks!

 

[MickG]

You're welcome