Assistant in Adding to this DATE formula

[thhynes]

=IF(MONTH(DATEVALUE(L$24&"1"))>=MONTH($C$9), $I26, "")

What this Does - It looks at CELL C9 which contains a DATE - Example ( 05/01/2021 )
The datasheet looks up a PREMIUM PRICE based on several factors... and the formula fills in the rates
that the premium should be for the MONTH the enrollment starts... so in this case, it leaves the months Jan, Feb, Mar, April as $0
and then it fills in May, Jun, July, Aug, Sept, Oct, Nov, Dec with the premium cost.

Cell B9 and C9 looks like this EFFECTIVE DATE: [ user inputs a date ]

I am thinking that if I add the following to my spreadsheet as follows

Cell B10 AND C10

Termination Date: [ user inputs end date ]

So that the RATE only fills in for the exact months in which the RATES apply

The Formula above is in EACH CELL for each month...

[AliGW]

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

[Pete_UK]

What does the cell L24 contain, and why are you adding the text value "1" to it inside the DATEVALUE function ?

Pete

[AliGW]

Probably one of these, Pete:

https://www.extendoffice.com/documen...number.html#a1

[Pete_UK]

Thanks Ali, so you think L24 contains a month name? You don't really need the quotes around "1".

Pete

[AliGW]

I would imagine so, but I've asked the OP to provide a workbook.

[thhynes]

Sorry I am breaking down a 20L aquarium for reset...halfway done

example.jpg

As can see the EFFECTIVE DATE is copied over to the Effective Date
The RATES are pulled via a Vlookup

Currently I have the RATES filled in from April through December - thats fine and dandy

I am thinking that if I add a USER input field under the Effective date in the above section
and add a TERM date and in this exmaple lets say it is fro 08/31/2021

Then The RATES get filled in for April through August
While SEP-DEC fields are blank like Jan-Mar

The formula for each cell in the MONTHS is

=IF(MONTH(DATEVALUE(L$24&"1"))>=MONTH($C$9), $I26, "")

RATES are pulled via lookup which are dependent on the Data filled under each name
specifically, the Plan Level, Plan Name and Rating Area - these do not change for all members as the PRIMARY applicants
always stay the same for the household

The RATES are then also based on the Date of birth and the age of that individual based on the DATE of Birth and the age of the individual
on the first of the month in which the plan became effective.

so in this example, the person is AGED 20 with the effective date of 04/01 because this person birthday is NOT until JULY
at which time the RATES would increase based on the AGE if there was a reported change

[AliGW]

I asked for a workbook, not a picture of one.

[thhynes]

me and computers.... so the only option I can do is link a picture

[thhynes]

see if this worked

[thhynes]

Okay I have tried to upload the actual book however there is no way for me to upload it,
I relaly appreciate your thank you.

Perhaps there is somewhere I could read on adding to the formula I have tried and IF( and AND and if and statements but excel just burps!

thanks!

T

[AliGW]

Unless your workbook is huge, then yes, if course you can attach it. I pointed you to the instructions at the top of the page, which you need to follow precisely.

[thhynes]

Thanks again the file is too big to upload, and if I remove any thing the work book won't work.
your attempt to help though is still greatly appreciated!

[AliGW]

Administrative Note:

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. As you are quite new here, I will do it for you this time only: https://www.mrexcel.com/board/thread...eeded.1192892/)

[Pete_UK]

If you ZIP the file, or save it with an .xlsb extension, it might be small enough to attach. As it stands, I can't read any of the details in the picture that you posted.

Pete

[Glenn Kennedy]

Save it as an .xlsb or zip it. The allowances for those file types are (for whatever reason) much greater.

[Pete_UK]

Snap, Glenn.

Pete

[Glenn Kennedy]

I was just too slow...

[Pete_UK]

Only by a few seconds.

Pete