Help with recurring dates in formula

[ccreamer11]

Greetings! I am pretty new to Airtable, so forgive me if this is a silly question: For reference, I am maintaining a book of business for an insurance office. This office manages multiple lines of business (home, auto, life, business, etc... which are a single select field in my table).  I am trying to create a column that displays the "next renewal", based on the field "inception date".  Here is what I have so far:

IF(

  DATETIME_FORMAT({Inception Date}, "MMDD") > DATETIME_FORMAT(TODAY(), "MMDD"),

  DATETIME_PARSE(

    DAY({Inception Date}) & "-" & 

    MONTH({Inception Date}) & "-" &

    YEAR(TODAY()),

    "D-M-YYYY"

  ),

  DATETIME_PARSE(

    DAY({Inception Date}) & "-" & 

    MONTH({Inception Date}) & "-" &

    (YEAR(TODAY()) + 1),

    "D-M-YYYY"

  )

)

this works great for displaying the renewal date for all lines that are a 12 month term. What can I add to only add 6 months to the date if the {line} field is "auto"? basically I want to add a year to all lines except auto, then only add 6 months... is it a switch command? I've been stuck on this all day..Please help!

[Brian_Swanson]

@ccreamer11  You might want to try something like this.  I am not certain why you are using DATETIME_PARSE as it woudl seem you have formated the field to be a date given that you are using the DATETIME_FORMAT in the first line.  

IF({Inception Date}>TODAY(),

  DATETIME_FORMAT({Inception Date}, 'D-M-YYYY'),

IF({Line}="Auto",

  DATETIME_FORMAT(

     DATEADD({Inception Date}, 6, 'M'), 'D-M-YYYY'),

  DATETIME_FORMAT(

     DATEADD({Inception Date}, 1, 'y'), 'D-M-YYYY')

))

Hope this helps

[ccreamer11]

Thanks for helping. Your help is getting me closer. Your formula did add 6 months to auto and a year to all else, exactly as I asked. However, I was using Parse date because I want this column to be perpetually, accurately, displaying the "Next" renewal.. your formula did add the 6 months as asked, but it did not display the year as I am looking for.  For example, lets use a policy with an {inception date} of 4/1/2020.  I want this formula to display the date as 4/1/2024 unless its an auto policy, then I want it to display 10/1/2023..  my original formula displays the date as 4/1/2024, regardless of {line}, while your formula displays a date of 4/1/2021 or 10/1/2021.. I need to somehow merge them together.

[Ben_Young1]

Hey @ccreamer11! 

Is this along the lines of what you're looking for?

IF(
  {Inception Date},
  IF(
    AND(
      {Inception Date} > TODAY(),
      {Line} = "Auto"
    ),
    DATEADD(
      {Inception Date},
      6, "months"
    ),
    DATEADD(
      {Inception Date},
      1, "years"
    )
  )
)

[Brian_Swanson]

@ccreamer11 I worked on this for a minute this morning.  The diffifulity in what you are asking is that AT needs to know which side of the half year mark you are on.  I was hoping that DATETIME_DIFF would provide a decimal equivalent of 'years' to make this easier but it appears to only push integers.  Try this below and see if it works for you. 

IF(

   {Inception Date}>{TODAY()},

   DATETIME_FORMAT({Inception Date}, 'D-M-YYYY'),

   IF(

      AND(

         {Line}="Auto",

         (DATETIME_DIFF({TODAY()}, {Inception Date},'M')) <

         ((DATETIME_DIFF({TODAY()}, {Inception Date},'Y')*12)+6)

         ),

      DATETIME_FORMAT(

         DATEADD({Inception Date}, (DATETIME_DIFF({TODAY()}, {Inception Date},'y')*12)+6, 'M'),

         'D-M-YYYY'

         ),

      DATETIME_FORMAT(

         DATEADD({Inception Date}, DATETIME_DIFF({TODAY()}, {Inception Date},'y')+1, 'y'), 

         'D-M-YYYY'

         )

   )

)

This can be simplified a bit if you allow AT to format your dates which would make the equation:

IF(

   {Inception Date}>TODAY(),

   {Inception Date},

   IF(

      AND(

         {Line}="Auto",

         (DATETIME_DIFF(TODAY(), {Inception Date},'M')) <

         ((DATETIME_DIFF(TODAY(), {Inception Date},'Y')*12)+6)

         ),

         DATEADD({Inception Date}, (DATETIME_DIFF(TODAY(), {Inception Date},'y')*12)+6, 'M'),

         DATEADD({Inception Date}, DATETIME_DIFF(TODAY(), {Inception Date},'y')+1, 'y')

   )

)

It works within my test base https://airtable.com/shr3QB0wkQibMdKvB where I changed out TODAY() to a field called TODAY(C) so I could quickly change the "day of the year" and see how the formula behaved.  

Hopefully this gets you a bit closer.