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

2022-02-01 --------- 2022.02.10 ---------------- 1 missed payment

2022-02-01 -------- 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

  • James Keuning
    James Keuning ✭✭✭✭✭
    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

  • James Keuning
    James Keuning ✭✭✭✭✭
    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.

  • This works thank you so much!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!