Histogram / Count Programming Problem

[Jan Grinwis]

Hi,

I have a large excel sheet (8000+ lines)
The sheet is filled with error messages etc. etc.

I want to build a chart of how many errors are there in a certain
date/time interval.

Simplified Data example:

Date Start Finish Remark
31-07-2004 23:03:00 23:30:00 Error 1
01-08-2004 00:00:00 00:15:00 Error 2
01-08-2004 00:12:00 00:25:00 Error 3
01-08-2004 01:05:00 01:31:00 Error 4
01-08-2004 01:50:00 01:58:00 Error 5
01-08-2004 01:45:00 02:02:00 Error 6
01-08-2004 01:30:00 02:00:00 Error 7
01-08-2004 01:38:00 02:10:00 Error 8
01-08-2004 03:00:00 03:15:00 Error 9
01-08-2004 04:00:00 04:12:00 Error 10
31-07-2004 06:25:00 06:45:00 Error 11
31-07-2004 06:30:00 06:45:00 Error 12
31-07-2004 06:40:00 07:00:00 Error 13
01-08-2004 04:39:00 04:46:00 Error 14


The chart should be like:
Xax is Date/time-interval
Yax is how many errors active

for instance:
Interval Errors
in the interval between 01-08-2004 01:31-01:35 2 '(error4 & 7)
01-08-2004 01:36-01:40 2 '(error7 & 8)
01-08-2004 01:41-01:45 3 '(error6 & 7 & 8)


Does anyone have a smart solution for this problem.

Jan

 

[Rob van Gelder]

I have a formula on my website: Hours affected by dates.
It's not a drop-in solution, but you might be able to get something working
from it.