Using If/And Function to figure out Eligibility of Benefit at a Future Date

I am trying to create a formula that tells me "Eligible" or "Not Eligible" based on two facts:
1) # of hours worked (i.e. 240 hours)
2) Employed for 90 days from hire date

In my spreadsheet, I have a total number of hours worked in a column, and the hire date in another column. I just found out the current formula I use isn't quite working (this is a formula I gathered from Googling).
Here is the current formula: =IF(AND(B2>240)*AND(MONTH(C2)>3),"ELIGIBLE", "NOT ELIGIBLE")

I have no idea how I got that formula as it was months ago that I entered it in my spreadsheet.

Can anyone help, please?

I take a different approach when I calculate benefit eligibility. If you are trying to determine eligibility when an employee works 240 or more hours and has 90 days of service or more, I would use the following:

Please Login or Register  to view this content.

Another tip I would give to make your formula me dynamic is to create named ranges for both the hours trigger as well as the months of service. That way if you need to modify those items at a future date you can change it in the named range and not in the formula itself. It would look something like this:

Please Login or Register  to view this content.

Hope it works.

Eric

Thank you! This one does work but when I entered in the formula it has the green box with the (!) saying it is an "inconsistent formula". However when I test it, it seems to work, so I am not sure what the error means?

Originally Posted by judgeh59

does this work for you?

Please Login or Register  to view this content.

Thank you! This one does work but when I entered in the formula it has the green box with the (!) saying it is an "inconsistent formula". However when I test it, it seems to work, so I am not sure what the error means?

Originally Posted by edp428

I take a different approach when I calculate benefit eligibility. If you are trying to determine eligibility when an employee works 240 or more hours and has 90 days of service or more, I would use the following:

Please Login or Register  to view this content.

Another tip I would give to make your formula me dynamic is to create named ranges for both the hours trigger as well as the months of service. That way if you need to modify those items at a future date you can change it in the named range and not in the formula itself. It would look something like this:

Please Login or Register  to view this content.

Hope it works.

Eric

Thank you, Eric. For some reason this one didn't work.

Thank you so much, I really appreciate it.