Help with elapsed time formula

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
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.  
Photo of Pro Survey Solutions

Posted 1 year ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,786 Points 50k badge 2x thumb
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")
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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,786 Points 50k badge 2x thumb
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".
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?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,786 Points 50k badge 2x thumb
If( 
ToDate([Date Created])-[CA Date] < Days(30), "1 Month", 
ToDate([Date Created])-[CA Date] >= Days(180), "6 Month")