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

 

[BillP]

Richard:

Yes, I counted the days on my fingers and you're right, it's 23. I fixed that.

I think I'd like to give the client worksheet with blank columns A & B and formulas in a bazillion cells in C so that they could just copy the applicable ranges in and get a total. I'd give them enough cells to fulfill their biggest dataset, and more.

Unfortunately, when all the cells in A & B are empty (as all the ones will be that don't get data pasted into them), they display 0.5, and when any of them get filled in, all the empty ones show -19542.

I'm trying to set some conditions to always return 0 when cells are empty, I'll keep you posted.

Bill

 

[BillP]

Barry:

That's clever. I'll ask them tomorrow if they want to be able to see each of the days that count (for their comfort level), in which case, I'll give them a column of formulas. Otherwise, I'll see.

You guys are clever. I think I need another book besides Mr. Excel (which is incredibly handy) to cover some of this stuff.

I'm going to spend some time tonight figuring out how each of these solutions works.

You learn something new every day (if you're not careful).

Take care.

Bill

 

[Richard Schollar]

Bill

To adjust my last formula so it returns nothing if the corresponding A & B cells are not filled in you could use:

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

which will only return a value if there is a date (actually a number) in the B column.

 

[BillP]

Richard,

Thank you.

But, I'm having a problem with that formula and the others that's driving me NUTS!

They return the wrong answer for some dates, but the right figures for others (to refresh your memories--we need to count the first and last day, not just the difference):

07-Jan-08 to 15-Jan-08 = 8 (this should be 9, it's on row 1288)
16-Jan-08 to 28-Jan-08 = 13 (this is correct, it's on row 1316)

The formulas were simply copied down the range, and they look right.

Any ideas?

 

[cornflakegirl]

Bill - what's the largest end date prior to row 1288?

 

[BillP]

They seem to have the same relationships to the previous rows:
Row
1287 06-Jan-08 06-Jan-08 0
1288 07-Jan-08 15-Jan-08 8
1289 07-Jan-08 07-Jan-08 0
(snip)
1315 15-Jan-08 15-Jan-08 0
1316 16-Jan-08 28-Jan-08 13
1317 16-Jan-08 16-Jan-08 0

(FYI, in case you missed a previous post the other dates really should be 0 because they fell in a previous range, so they don't get counted twice.)

 

[cornflakegirl]

What about earlier than row 1287? Is there a period that ends on 07-Jan-08?

I'm assuming that your formula for row 1288 is =IF(ISNUMBER(B1288),B1288-MEDIAN(A1288-1,MAX($B$1:$B1287),B1288),""). If so, that suggests to me that MAX($B$1:$B1287) = 07-Jan-08

 

[Richard Schollar]

I was going to say something similar, but the ShreddedWheatChick got there first...

 

[cornflakegirl]

Some days you're just surrounded by young whippersnappers with faster fingers...

(Thank you for the introduction to ROW(INDIRECT) btw!)

 

[BillP]

Cornflakegirl:
Yes. That's the formula.

Here's an idea...
Could it be a rounding problem? In Richard's original formula, it returned 0.5 if the cells were empty. I fixed that with an IF statement. But is there a 0.5 being returned in some calculations that screws this up?

BTW, the new formula gives me the same results.