Question

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.

James Keuning · Accepted 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.