Adding unique days in a range

[BillP]

We have a client that has to total the number of days that people received medical care (for insurance purposes). BUT...they can't count duplicate days. Some of the dates show a range of days, others only one day. They've asked for my help. (We're actually a computer services firm, but I'm pretty good with Excel...until now!)

In column C I have placed the correct totals for the date ranges, according to the payment policies. But I can't figure out how to write the formula that accommodates all the variables (mostly since all the unique dates aren't entered--only ranges).

Each line is a different medical charge, so line 1 might be 22 days of hospital stay. Line 2 is for a different charge, but covers the same dates, so it can't count. Lines 3, 4, and 5 all occur within the previously calculated range, so they don't count. The date range in line 6 is six days but 4 of them have been counted already, only 2 days fall outside an already calculated range.

It would be easy enough to do this manually for a small list, but this patient has 1367 entries in 4 months of care! And there are others that are much larger.

Fortunately, all the dates are in calendar order. Also, fortunately, they don't need line-by-line totals, just the grand total at the end of all the unique days in the range. So if I can figure out how many unique days are in the columns, I'm good.

Any ideas?

Thanks.

Bill

P.S. Sorry for the horrible layout below, I had the HTML maker in the last version of Excel, but not this one. This was quicker.


............A.................B....................C
..........Start.............End...............Total
1.....05-Aug-07.....27-Aug-07..........22
2.....05-Aug-07.....27-Aug-07........... 0 (same as a previously counted range)
3.....05-Aug-07.....05-Aug-07........... 0 (in a previously counted range)
4.....05-Aug-07.....05-Aug-07........... 0 (in a previously counted range)
5.....15-Aug-07.....22-Aug-07........... 0 (all in a previously counted range)
6.....23-Aug-07.....29-Aug-07...........2 (4 of 6 days in a previously counted range)
7.....24-Sep-07.....06-Oct-07..........13
8.....28-Sep-07.....28-Sep-07........... 0 (in a previously counted range)
9.....29-Sep-07.....29-Sep-07........... 0 (in a previously counted range)
10....18-Oct-07.....19-Oct-07...........2 (not in a previously counted range)
11....20-Oct-07.....20-Oct-07...........1 (not in a previously counted range)
12....20-Oct-07.....22-Oct-07...........2 (1 of the 3 days is in a previously counted range
....................................Total.......42

 

[DonkeyOte]

If the start dates are sequential perhaps something like:

Excel Workbook
	A	B	C
1	05-08-07	27-08-07	22
2	05-08-07	27-08-07	0
3	05-08-07	05-08-07	0
4	05-08-07	05-08-07	0
5	15-08-07	22-08-07	0
6	23-08-07	29-08-07	2
7	24-09-07	06-10-07	13
8	28-09-07	28-09-07	0
9	29-09-07	29-09-07	0
10	18-10-07	19-10-07	2
11	20-10-07	20-10-07	1
12	20-10-07	22-10-07	2
Sheet5

I've not really worked through this in great detail as yet so I may have missed something obvious !

 

[Richard Schollar]

...I may have missed something obvious !

C1 formula maybe:

=B1-A1

?

 

[DonkeyOte]

I saw the email... RichardSchollar has replied to the Thread... and thought, after all that I'm gonna get shown up... so not as bad as normal

I'll await the more elegant formulae later... will have sparked your interest now I'm guessing ...

 

[Richard Schollar]

Hi

Given the data in Luke's data table and the formula

=B1-A1

in C1, then in C2 the following copied down:

=SUMPRODUCT(1-ISNUMBER(MATCH(ROW(INDIRECT(A2&":"&B2)),ROW(INDIRECT(MIN($A$1:$A2)&":"&MAX($B$1:$B1))),0)))

 

[DonkeyOte]

and there you have it... ... very nice Richard... I really must get used to using the INDIRECT ROW approach for these date formulae.

 

[BillP]

Whew!

Thank you, lasw10. I'm playing around with it now, and it even seems to work if some dates are out of order. I'll keep trying to break it and let you know.

Bill

 

[BillP]

Wow!

I apparently have an embarrasment of riches here.

Thank you, Richard. I'm going to put that through its paces too.

Some of these reports have thousands of rows, and apparently they've been scanning them by hand to come up with a total.

You guys are the bestest!

Bill

 

[Richard Schollar]

I actually think your first row total (in C1) should be 23 rather than 22 as it is calculated on an inclusive basis (ie the same day in both A column and B column would give a value of 1 not zero). Then you can use the following formula in C2 copied down:

=B2-MEDIAN(A2-1,MAX($B$1:$B1),B2)

 

[barry houdini]

I actually think your first row total (in C1) should be 23 rather than 22 ....

I agree.

That would make the total number of unique days 43. This formula will calcuate that in a single cell

=SUM((MMULT((TRANSPOSE(A1:A12)<=ROW(INDIRECT("1:"&MAX(B1:B12)-A1+1))+A1-1)*(TRANSPOSE(B1:B12)>=ROW(INDIRECT("1:"&MAX(B1:B12)-A1+1))+A1-1),(ROW(A1:A12)>0)+0)>0)+0)

This is an array formula which must be confirmed with CTRL+SHIFT+ENTER so that curly braces appear around the formula in the formula bar

Note: Because column A in your example is sorted ascending the minimum value [i.e. earliest date] in A1:A12 is always A1. If that might not be the case you should replace A1 references in the formula with MIN(A1:A12)