Expiration Date Calculations Required for MVP
tobiasaw34002 opened this issue · 4 comments
What are the most common scenarios for organizations calculating expiration dates and which of those scenarios should be included in an MVP?
Examples:
- Today + 365 days
- Today's date next year (e.g. 03/06/2023 to 03/06/2024)*
- Today + 365 days rounded to the end of the month (e.g. 03/06/2023 to 03/31/2023)*
- Calendar Year
- Fiscal Year (e.g. July 1 - June 30)
*Considerations needed for Feb. 29/leap years
We have two schemes, one that uses logic from 2, and another that uses logic May 1- April 30 (our fiscal year is April 1 - March 31)
i.e. With annual start date 1st May, paying April to Feb is this year but March is counted as next year.
Here are my flow calculations if they help understand the logic.
Start Date:
IF(ISPICKVAL({!$Record.npe01__Member_Level__c},"Life"),{!$Record.CloseDate},
IF(AND(ISPICKVAL({!$Record.npe01__Member_Level__c},"Affiliate") ,MONTH({!$Record.CloseDate})>=4), DATEVALUE(TEXT(YEAR({!$Record.CloseDate}))+"-05-01"),
IF(AND(ISPICKVAL({!$Record.npe01__Member_Level__c},"Affiliate") ,MONTH({!$Record.CloseDate})<3), DATEVALUE(TEXT(YEAR({!$Record.CloseDate})-1)+"-05-01"),
IF(AND(ISPICKVAL({!$Record.npe01__Member_Level__c},"Affiliate") ,MONTH({!$Record.CloseDate})=3), {!$Record.CloseDate},
IF(OR(ISBLANK({!$Record.Member_Date_Check__c}),ISPICKVAL({!$Record.npe01__Membership_Origin__c},"New")),{!$Record.CloseDate},
IF(AND({!$Record.Member_Date_Check__c}-{!$Record.CloseDate}<60, {!$Record.Member_Date_Check__c}-{!$Record.CloseDate}>-60),{!$Record.Member_Date_Check__c}, {!$Record.CloseDate}))))))
End Date:
IF(ISPICKVAL({!$Record.npe01__Member_Level__c},"Life"),ADDMONTHS({!$Record.CloseDate},1200),
IF(AND(ISPICKVAL({!$Record.npe01__Member_Level__c},"Affiliate") ,MONTH({!$Record.CloseDate})>=3), DATEVALUE(TEXT(YEAR({!$Record.CloseDate})+1)+"-05-01"),
IF(AND(ISPICKVAL({!$Record.npe01__Member_Level__c},"Affiliate") ,MONTH({!$Record.CloseDate})<3), DATEVALUE(TEXT(YEAR({!$Record.CloseDate}))+"-05-01"),
ADDMONTHS({!$Record.npe01__Membership_Start_Date__c},12))))
In one use case I have (and not listed above), the membership is Dec 31 of the current year. However, if they pay after October 1, the expiration date becomes Dec 31 of the following year.
This is part of the formula used in a flow for this calculation:
IF(
MONTH ({!$Flow.CurrentDate} ) < 10,
DATE(YEAR({!$Flow.CurrentDate}), 12,31),
DATE((YEAR({!$Flow.CurrentDate})+1),12,31))