Missed Premium deduction Formula.
So I need a formula to compare the date an insured coverage began against the date they selected benefits and provide the statement either one missed payment or two missed payments.
So I have two Columns:
Coverage Start Date  Date Coverage Elected  Missed Payments
20220201  2022.02.10  1 missed payment
20220201  2022.02.20 2 missed payments
The premise is if they select coverage after the 5th of the month then they missed one payment if they select coverage after the 20th of the month then they missed 2 payments due to how payroll is ran.
I am looking for a formula to calculate this and enter the words 1 or 2 missed payments in the column.
Best Answer

I think you are looking for something like:
=IF(VALUE(MID([Date Coverage Elected]@row, 4, 2)) > 20, "missed 2", (IF(VALUE(MID([Date Coverage Elected]@row, 4, 2)) > 5, "missed 1")))
Basically we look to see if coverage was elected after the 20th, if so we put missed 2, if not we see if to was elected after the 5th, if so we put missed 1, if it was not after the fifth, we put nothing.
Answers

I think you are looking for something like:
=IF(VALUE(MID([Date Coverage Elected]@row, 4, 2)) > 20, "missed 2", (IF(VALUE(MID([Date Coverage Elected]@row, 4, 2)) > 5, "missed 1")))
Basically we look to see if coverage was elected after the 20th, if so we put missed 2, if not we see if to was elected after the 5th, if so we put missed 1, if it was not after the fifth, we put nothing.

This works thank you so much!
Help Article Resources
Categories
Check out the Formula Handbook template!