Skip to main content
Question

Help with elapsed time formula

I am looking for help with a formula field I need to create.  The goal is to decipher between a record that contains a call_date that was less than one month in the past and a call_date that is more than 6 months in the past.  The field will be used as a filter in a summary report.  I am not sure if this can be achieved as one field for duration since the call_date field or if it would be better to create two checkbox fields that can be filtered individually.  
      This topic has been closed for replies.

      The question is confusing.
      Are you looking to create a field which will calculate a text value based on how old it is?

      For example,
      The formula for agree group would be,

      If(
      today()-[call_date] < Days(30), "Under 30",
      today()-[call_date] < Days(180), "Under 180",
      today()-[call_date] >= Days(180), "Over 180")
      • AmptivaInc
      • Author
      • Registered
      • August 22, 2017
      Sorry for the confusing explanation!  I am looking to create a field that will determine time elapsed based on two fields.  The difference between the [date created] minus the [call_date] field.  So really, it should look something like:
      [Date Created]-[Call_Date] but I am challenged with how to add to the formula to take it one step further to decipher between under 30 days and over 180 days for use in our report.
      The formula  for a formula numeric  would be

      ToDays(Today() - [call_date])

      Is that your question?

      I am not understanding your question.

      If you want to summarize data and group by buckets of over or under some days value, then you should use my first formula as one of the Group by.

      If I am still not understand, maybe you can explain with an example.

      It does not sound complicated, its just that I am not understanding your "ask".
      • AmptivaInc
      • Author
      • Registered
      • August 22, 2017
      Hi -- thanks for the help.  Here is the formula example:
      If( 
      [Date Created]-[CA Date] < Days(30), "1 Month", 
      [Date Created]-[CA Date] >= Days(180), "6 Month")

      The issue I am getting a syntax error because [Date Created] is a date/time field type and [CA Date] is a date type.  Is there an easy way to change this without changing the field type?
      If( 
      ToDate([Date Created])-[CA Date] < Days(30), "1 Month", 
      ToDate([Date Created])-[CA Date] >= Days(180), "6 Month")
      Terms & ConditionsAccessibility statement