Date Count Aggregation

[MarkHa]

Hi all,

I have a list of Startdates and Finishdates (one per row), and want to aggregate count of all dates spanned to a fixed linear calendar as per example below.

Can anyone think of a way to do this?

The aim is to use the aggregated data to create a graph with linear Calendar as X-axis, and aggregated days covered as Y-axis.

Startdate Finishdate
04/12/2007 06/12/2007
05/12/2007 07/12/2007
09/12/2007 10/12/2007

Calendar
04/12/2007 1
05/12/2007 2
06/12/2007 2
07/12/2007 1
08/12/2007 0
09/12/2007 1
10/12/2007 1

Many thanks,
MarkHa

 

[Fazza]

hi Mark.

With the start dates range named "start" and finish dates range "finish", and the particular date you want to check in cell A10, array enter with CTRL-SHIFT-ENTER as below. Cheers, Fazza

=SUM((A10>=start)*(A10<=finish))

 

[MarkHa]

Hi Fazza,

Works perfectly! Many thanks!

 

[Fazza]

You're very welcome, Mark. Beer time for me now. Regards, Fazza