Value Range Formula -- In-Between Multiple Ranges

[JET1234]

Hello Everyone. I signed up just to ask this question because I am completely stumped. It's lengthy but that's the only way I can paint the entire picture. I will try to be as direct as I can, thanks.

Facts: Medicare for Nursing Homes pays by 100 day benefit periods. The 100 days is broken down into 5 assessments, each paying for a specific day range. We submit claims monthly.

I want to be able to drop in the admit date with the dates of service for the claim and have excel automatically compute which assessments will be needed for the claim. I have uploaded what I have so far. If you need more info just let me know.

Thanks
Jet

 

[Hui]

Jet

Firstly, Welcome to the Chandoo.org Forums

I'm not sure what the question is but in B7 I would use:
=IF(AND($B$4<=K8,$D$4>=J8),"Yes","")
Copy down

This lists which assesments are required

Then apply a Conditional Format to B7:B11 to highlight cells with a Yes
Select B7:B11
Remove the Fill
Conditional formatting
New Rule
Use a Formula
=B7="Yes"
Apply a Format
Apply

 

[JET1234]

Thanks Hui! That worked perfectly. I guess I was having a case of excel block!

To expand on the original question, I would like to add 3 more columns next to the needed assessment column.

One column would count the days, the next two would display the date range for those days. For example, the 5-day assessment is needed so how many days would that assessment cover: 3 days; 12,13,14 (9/1/14 - 9/3/14).

What formula would be best to use to count the days with the IF= formula?

 

[JET1234]

Here is the file. Thanks.

 

[Somendra Misra]

Hi,

Can you explain the no. of days calculation in your file.

Regards,

 

[JET1234]

The Pay Date range (12 & 37)? Those are just a calculation of the day # in the benefit period.

You are allowed 100 days per benefit period. The claim dates (9/1 thru 9/26) are days 12 thru 37 in the 100 day period.

 

[Somendra Misra]

@JET1234

That is understood, but my question was how come you came to 3,16,and 7 and various benefit period?

1. So this is my understanding from 9/1 thru 9/26 there are 26 days, which you need to break in 5,14,30 day benefit? Is this correct?

2. Since person admitted on 8/21 and pay starts from 12th day so adding 12 to 8/21 gives 9/1, now 5-day period is from 1-14 so there are three days left in that period. IS this correct?

The portion I am unable to understand is 16???

Kindly, explain that?

Regards,

 

[JET1234]

@Somendra Misra

Thanks for the response. The 16 days is the 14-day assessment period (pay dates 15-30). Medicare calls it the 14-day assessment but it actually pays for 16 days.

So in my form, I simply dropped in what the assessment pay dates should be for each assessment; 3 days, 16 days, 7 days. I'm looking for a formula that will calculate those pay dates for me, by the assessment criteria.

Thanks
Jet

 

[Somendra Misra]

@JET1234

I am Still confused about column C "Days" calculation. From you above comment it's seems you will put days manually, if so than see the file, it does dates calculation in green cells.

Regards,

 

[JET1234]

See the attached form. I added the full assessment and payment days schedule for reference. I'm trying to find a formula that will calculate the days used and date range for each assessment.

The column C days are just a total of the days used from that assessment. The 14-Day assessment pays for days 15 thru 30 (which is 16 days). Since the claim dates will need to use the entire 14-Day assessment, I entered 16.

I'm needing a formula that will calculate the the Days and Date Ranges automatically, for any dates of service entered at the top.

 

[Somendra Misra]

See the file.

Regards,