Forum Discussion

BretOsborne's avatar
BretOsborne
Qrew Member
3 years ago

Duration Date/Time and Dates

I am trying to figure out a formula for duration. my problem is that one set of date is Date/Time, while the other is only Date.

QC Date - Date/Time
Start Date - Date

The QC Date pulls from a relationship, so I don't think I can simply change the QC Date to "date" only because of the relationship.

However, what I am trying to do is determine only the number of days between the two and exclude the "time" portion, if possible.

I thought i was close with this formula, but it does not like the subtraction.

DAYOFYEAR(TODATE([QC DATE CREATED]))-[DESIGN START DATE])

All i need it to do is pull the date and ignore the time stamp - QC date 11/22 - start date 11/20 = 2 days. Is this possible? I looked at other formula functions as well, but to no luck so far.
Thanks!



------------------------------
Bret Osborne
------------------------------
Formulas and functions
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    3 years ago
    This will  give you a Duration result

    ToDate([QC DATE CREATED])-[DESIGN START DATE])

    Personally I find duration fields confusing to work with.  I prefer to have a  numeric field representing the # of days when dealing with short term time ranges.

    This would be a numeric field​

    ToDays(ToDate([QC DATE CREATED])-[DESIGN START DATE]))


    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
    • BrianSeymour's avatar
      BrianSeymour
      Qrew Assistant Captain
      3 years ago
      I agree with Mark as this can keep the units consistent as well, although, displaying smart units can be nice too depending on the use case and actual time windows.

      Often times we'll store data in a datetime field so it's available if we need more granular timeframes, but have "helper formula fields" to cast the datetime to a date. This can reduce keeps noise on reports by omitting timestamps when necessary and can make your formulas a bit easier to read.

      For example, the field "Start Timestamp" and a helper "Start Timestamp To Date" that is:
      ToDate([Start Timestamp]). Then, use consistent field types in your duration calculations.

      [QC Timestamp To Date] - [Start Timestamp To Date]

      And as Mark pointed out you could represent these Durations as Numeric Days instead using ToDays() and changing the formula field type to numeric.

      ------------------------------
      Brian
      ------------------------------