Discussions

 View Only
  • 1.  Duration Date/Time and Dates

    Posted 11-22-2022 23:25

    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
    ------------------------------


  • 2.  RE: Duration Date/Time and Dates

    Posted 11-23-2022 09:16
    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
    ------------------------------



  • 3.  RE: Duration Date/Time and Dates

    Posted 11-28-2022 11:31
    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
    ------------------------------