Forum Discussion

AmptivaInc's avatar
AmptivaInc
Qrew Cadet
8 years ago

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.  
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    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's avatar
    AmptivaInc
    Qrew Cadet
    8 years ago
    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.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    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's avatar
    AmptivaInc
    Qrew Cadet
    8 years ago
    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?
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    If( 
    ToDate([Date Created])-[CA Date] < Days(30), "1 Month", 
    ToDate([Date Created])-[CA Date] >= Days(180), "6 Month")