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
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
Last edited: