In the image below
... based on the plan year, effective date, metal level, plan name, rating area, and Date of Birth
A Vlookup formula searches a table of data to return the Value based on the information.
This Date formula =IF(MONTH(DATEVALUE(L$24&"1"))>=MONTH($C$9), $I26, "")
fills in the rates based on the effective date - which is working well,.. it leaves any month except for the EFFECTIVE month blank and fills in the RATES for
the remaining months.
What I would like to achieve is, and I may need to add a field under the Effective date for Termination Date, then based on these two dates the
rates fill in for the Effective date through the termination Date...
So lets say
Effective date 05/01/2021
Termination date 08/31/2021
the RATE found would only be filled in for the months of May, June, July, August
the months of Jan, Feb, Mar, Apr, Sep, Oct, Nov, Dec
I sort of understand what this formula is doing but not enough to achieve what I would like.
Thanks
... based on the plan year, effective date, metal level, plan name, rating area, and Date of Birth
A Vlookup formula searches a table of data to return the Value based on the information.
This Date formula =IF(MONTH(DATEVALUE(L$24&"1"))>=MONTH($C$9), $I26, "")
fills in the rates based on the effective date - which is working well,.. it leaves any month except for the EFFECTIVE month blank and fills in the RATES for
the remaining months.
What I would like to achieve is, and I may need to add a field under the Effective date for Termination Date, then based on these two dates the
rates fill in for the Effective date through the termination Date...
So lets say
Effective date 05/01/2021
Termination date 08/31/2021
the RATE found would only be filled in for the months of May, June, July, August
the months of Jan, Feb, Mar, Apr, Sep, Oct, Nov, Dec
I sort of understand what this formula is doing but not enough to achieve what I would like.
Thanks