Discussions

 View Only
  • 1.  Help with elapsed time formula

    Posted 08-22-2017 01:49
    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.  


  • 2.  RE: Help with elapsed time formula

    Posted 08-22-2017 11:58
    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")


  • 3.  RE: Help with elapsed time formula

    Posted 08-22-2017 12:17
    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.


  • 4.  RE: Help with elapsed time formula

    Posted 08-22-2017 12:37
    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".


  • 5.  RE: Help with elapsed time formula

    Posted 08-22-2017 16:55
    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?


  • 6.  RE: Help with elapsed time formula

    Posted 08-22-2017 17:02
    If( 
    ToDate([Date Created])-[CA Date] < Days(30), "1 Month", 
    ToDate([Date Created])-[CA Date] >= Days(180), "6 Month")