Help

Help with recurring dates in formula

Topic Labels: Formulas
150 4
cancel
Showing results for 
Search instead for 
Did you mean: 
ccreamer11
4 - Data Explorer
4 - Data Explorer

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 4
Brian_Swanson
6 - Interface Innovator
6 - Interface Innovator

@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
10 - Mercury
10 - Mercury

Hey @ccreamer11

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

Snag_5fde05ef.png

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

@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.