Blocking together a range of dates after performing a test on them. 1

[Hasit]

I think I am being stupid. I have a range of "lease start" and "lease end" dates in 2 columns (about 250 in each column).

I would like to produce a trend graph of lease dates (showing that lease times have reduced hence financial management is improving). The leases range from starting in early 1980s and ending in 2010. However,because theres around 250 entries, the graph looks horrible and is difficult to read.

I thought I would then produce 3 seperate lines on a single graph showing trend in decades. So, 1981 - 1990, 1991 - 2000, 2001 - 2010.

The problem is, in trying to test whether a date falls between the two dates in each column doesn't work. Additionally, if it does, then there would be gaps left in the column where there was no match. If I then tried to create a graph from that for say the 1981 - 1990 range, there would be gaps in the line.

So. What I am trying to do is, test a date to see if it falls in a range. Then, collate that information in one BLOCK of data to allow me to produce a graph (a similar result if I were trying to get external data from another source).

Help!

 

[logius]

I'm not really too clear on the issue here, but if you want to group data together to form a graph, then you can do it in a macro using the

Union

function. This returns a single Range after combining two separate ranges. There can be gaps in the ranges (you can Union 3 cells here, 4 cells there, etc). A way to do it would be:

Dim CellItem As Object
Dim FinalRange as Range

For Each CellItem in MyRange
    If *Test* Then
       If FinalRange IS Nothing Then
          Set FinalRange = CellItem
       Else
          Set FinalRange = Union(FinalRange, CellItem)
       End If
    End If
Next CellItem

At the end just include a

FinalRange.Select

and

Charts.Add

and the chart will be created with the data. Granted, this is completely untested (I'm not sure if you can Union a Cell or if it has to be a range item), but I've used it in the past to generate charts based on specific ranges in large sets of data and it worked.

 

[Hasit]

Yikes! Thanks for the reply. Sadly, I am not a VB person, so macros, I can record, but not manipulate.

Appreciate the thinking though.

 

[Hasit]

Let me add something else. The main problem I am having is comparing dates. So for example, I have a date 22/6/01 (English format). How do I compare just the year so that it meets the criteria I have highlighted above.....

 

[jebry]

Hi!

Excel has a Year function which returns just the year portion of a date. I hope it is something you can use.

Jeff Bridgham

 

[JVFriederick]

Create 3 columns to the right of your data that define how you wish to summarize into groups. The write a formula in the first column that checks the dates,
IF($B2-$A2=Something,1,0)
Copy the formula for all 3 columns and all rows, then total the columns. Contruct a graph of the totals for each category.

 

[Hasit]

JVFreiderick, thanks for the input. Getting closer, BUT, the dates are all different, so the "something" could/would be different in all cases. What would the something be, if I want the date range to fall between say 1981 and 1990?

 

[JVFriederick]

Whatever it takes !

=IF(A2>1980,IF(A2<1990,1,0),0)

 

[Hasit]

OK. The reason why I think I was being stupid is because I used the formula above and it works on just the year. What if the date format was dd/mm/yyyy?

I have changed the formula in many different ways and cannot get the right result.

Advice?

 

[JVFriederick]

Try :
=IF(A2>DATE(1980,1,1),IF(A2<DATE(1990,12,31),1,0),0)

 

[Hasit]

JVFriederick: Thanks a lot! It seems to work. I'm just pleased that experts like you give up your time to help out!

 

[JVFriederick]

Your thanks is thanks enough.