Is there a formula?

New Contributor

I am trying to figure out how or what formula to convert thousands of enrollees from a 30 day coverage period to a calendar month triggered by payment and ensuring no gap in coverage.

 

Here is an example: 

 

A member whose payment processed 3/5/22, their coverage period would be 3/6/22 thru 4/5/22 

I need it to be payment on 3/5/22 coverage is 3/1/22 thru 3/31. 

 

While that sounds "simple," here is another example 

Payment 3/25/22 coverage period 3/26/22 thru 4/25/22 

need it to be payment 3/25/22 coverage period 4/1/22 thru 4/30/22 

keeping in mind that their previous coverage was payment 2/25/22 coverage 2/26/22 thru 3/24/22, of course, Feb. Being a short month is already an outlier. 

 

Currently, payment dates are daily and open to adjusting to accomplish the desired result. 

 

 

2 Replies
HEY Jaime! I have understood the problem to some extent and I guess I might have the solution. Would you please share the link of sample excel file ( not containing the actual data) so as to solve this! Here is my email if you can share . Email address removed
Date Time User Submitted Coverage Start Settled Date Amount Method CC Type Processor Status Transaction ID Response Text Auth Code Void Complete Refund Chargeback Refunded Chargedback Type Payment Number Product ID Product Label Product Benefit Product Period Product Created Product Active Product Inactive
2/28/2022 4:00:30 AM Scheduler (3031) 2/24/2022 2/28/2022 377 Credit Card Visa Agentra Esquire $7.5 Million Approved 7016681272 Transaction was Approved 982027 No Yes No No No No 41,41,41 41 21690 CAT50 Member 10/23/2018 10/24/2018
2/28/2022 4:00:30 AM Scheduler (3031) 2/28/2022 2/28/2022 377 Credit Card Visa Agentra Esquire $7.5 Million Approved 7016681272 Transaction was Approved 982027 No Yes No No No No 41,41,41 41 21688 HSP6 Member Monthly 10/29/2018 10/30/2018
2/28/2022 4:00:42 AM Scheduler (3031) 3/15/2022 2/28/2022 348 Credit Card Visa Agentra Esquire $7.5 Million Approved 7016681581 Transaction was Approved 28315 No Yes No No No No 41,40 41 21687 HSP3 Member 10/29/2018 11/15/2018
2/28/2022 4:00:46 AM Scheduler (3031) 3/6/2022 198 ACH ABC - ACH - 1 Approved 63564879288 NULL No No No No No No 41,41 41 21687 HSP3 Member 10/29/2018 11/6/2018