search

SFDO-Community-Sprints / Membership-Essentials-App

Open Source Community Sprint project focused on building a schema for memberships, renewals, payments, multiple members, families, benefits, etc, that actually works. Eventually automation.
BSD 3-Clause "New" or "Revised" License
24 stars 13 forks source link

Expiration Date Calculations Required for MVP #83

Open tobiasaw34002 opened 1 year ago

tobiasaw34002 commented 1 year ago

What are the most common scenarios for organizations calculating expiration dates and which of those scenarios should be included in an MVP?

Examples:

  1. Today + 365 days
  2. Today's date next year (e.g. 03/06/2023 to 03/06/2024)*
  3. Today + 365 days rounded to the end of the month (e.g. 03/06/2023 to 03/31/2023)*
  4. Calendar Year
  5. Fiscal Year (e.g. July 1 - June 30)

*Considerations needed for Feb. 29/leap years

DebbieN-SF commented 1 year ago

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.npe01Member_Levelc},"Life"),{!$Record.CloseDate},
IF(AND(ISPICKVAL({!$Record.npe01Member_Levelc},"Affiliate") ,MONTH({!$Record.CloseDate})>=4), DATEVALUE(TEXT(YEAR({!$Record.CloseDate}))+"-05-01"), IF(AND(ISPICKVAL({!$Record.npe01Member_Levelc},"Affiliate") ,MONTH({!$Record.CloseDate})<3), DATEVALUE(TEXT(YEAR({!$Record.CloseDate})-1)+"-05-01"), IF(AND(ISPICKVAL({!$Record.npe01Member_Levelc},"Affiliate") ,MONTH({!$Record.CloseDate})=3), {!$Record.CloseDate}, IF(OR(ISBLANK({!$Record.Member_Date_Checkc}),ISPICKVAL({!$Record.npe01Membership_Originc},"New")),{!$Record.CloseDate}, IF(AND({!$Record.Member_Date_Checkc}-{!$Record.CloseDate}<60, {!$Record.Member_Date_Checkc}-{!$Record.CloseDate}>-60),{!$Record.Member_Date_Checkc}, {!$Record.CloseDate}))))))

End Date: IF(ISPICKVAL({!$Record.npe01Member_Levelc},"Life"),ADDMONTHS({!$Record.CloseDate},1200), IF(AND(ISPICKVAL({!$Record.npe01Member_Levelc},"Affiliate") ,MONTH({!$Record.CloseDate})>=3), DATEVALUE(TEXT(YEAR({!$Record.CloseDate})+1)+"-05-01"), IF(AND(ISPICKVAL({!$Record.npe01__Member_Levelc},"Affiliate") ,MONTH({!$Record.CloseDate})<3), DATEVALUE(TEXT(YEAR({!$Record.CloseDate}))+"-05-01"), ADDMONTHS({!$Record.npe01Membership_Start_Date__c},12))))

Kathy-Waterworth commented 1 year ago

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))

DebbieN-SF commented 1 year ago

I have something similar. With annual start date 1st May, paying April to Feb is this year but March is counted as next year.

On Wed, 29 Mar 2023, 20:19 Kathy-Waterworth, @.***> wrote:

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))

— Reply to this email directly, view it on GitHub https://github.com/SFDO-Community-Sprints/MembershipSchemaAndBenefits/issues/83#issuecomment-1489170302, or unsubscribe https://github.com/notifications/unsubscribe-auth/AUMH7IG574BF74GC5SDJLX3W6SDNBANCNFSM6AAAAAAVSZIC5Y . You are receiving this because you commented.Message ID: <SFDO-Community-Sprints/MembershipSchemaAndBenefits/issues/83/1489170302@ github.com>

tobiasaw34002 commented 1 year ago

I have a client with a similar business rule. This will help.

On Wed, Mar 29, 2023 at 16:24 DebbieN-SF @.***> wrote:

I have something similar. With annual start date 1st May, paying April to Feb is this year but March is counted as next year.

On Wed, 29 Mar 2023, 20:19 Kathy-Waterworth, @.***> wrote:

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))

— Reply to this email directly, view it on GitHub < https://github.com/SFDO-Community-Sprints/MembershipSchemaAndBenefits/issues/83#issuecomment-1489170302 , or unsubscribe < https://github.com/notifications/unsubscribe-auth/AUMH7IG574BF74GC5SDJLX3W6SDNBANCNFSM6AAAAAAVSZIC5Y

. You are receiving this because you commented.Message ID: <SFDO-Community-Sprints/MembershipSchemaAndBenefits/issues/83/1489170302@ github.com>

— Reply to this email directly, view it on GitHub https://github.com/SFDO-Community-Sprints/MembershipSchemaAndBenefits/issues/83#issuecomment-1489346030, or unsubscribe https://github.com/notifications/unsubscribe-auth/AXDC7UD26WAZBS4FBG55MDLW6SR7DANCNFSM6AAAAAAVSZIC5Y . You are receiving this because you authored the thread.Message ID: <SFDO-Community-Sprints/MembershipSchemaAndBenefits/issues/83/1489346030@ github.com>

-- T. Ward