Skip to main content

Help with recurring dates in formula

  • May 3, 2023
  • 4 replies
  • 50 views
C
Forum|alt.badge.img+2

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!
 

4 replies

B
Forum|alt.badge.img+7
  • Brian_Swanson
  • Participating Frequently
  • May 3, 2023

@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
    C
    Forum|alt.badge.img+2
    • ccreamer11
    • Author
    • New Participant
    • May 3, 2023

    @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

    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
    Forum|alt.badge.img+22
    • Ben_Young1
    • Brainy
    • May 3, 2023

    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" ) ) )
    B
    Forum|alt.badge.img+7
    • Brian_Swanson
    • Participating Frequently
    • May 4, 2023

    @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. 
    Terms & ConditionsAccessibility statement