Discussions

Expand all | Collapse all

Difference between dates

  • 1.  Difference between dates

    Posted 22 days ago
    I wonder if anyone could help? 

    I'm looking to set up a formula that shows- The difference between an initial date and the earlier of a) today's date or a specified date. 

    I.e- Bob introduces a site 01.01.2020. We respond within 7 days (and record that date in the system- 08.01.2020) which returns a result of 7. However, if we haven't responded, yet, and the date is say 12.01.2020 it records a figure of 11. 

    Thank you.

    ------------------------------
    James Ahearne
    ------------------------------


  • 2.  RE: Difference between dates

    Posted 22 days ago
    np
    I like to use formula variables even for simple formuals as it makes them easier to read later.  A formula variable is like a mini field that only has life inside the formula box. 

    var date EndDate = IF(not IsNull([Date Responded]), [Date Responded], Today());
    ToDays($EndDate - [Initial Date])

    // subtracting dates gives a result as a Duration, which is a sense of elapsed time in no particular units.
    // So we use the ToDays function to convert to the numeric number of Days.
    // lines beginning with // slashes are comments which are allowed on formula boxes.

     


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Difference between dates

    Posted 22 days ago
    Mark

    Thank you so much for the fast response. Apologies, but I am having a little trouble making this work. I assume I only need to copy and paste your formula and link the correct [Date Responded] field (which is the same field noted twice). I've then tried to link the [Initial Date] field but I am told that the system is 'expecting a duration but found a number'. I am not sure why when I am linking it to a date field. 

    Many thanks


    ------------------------------
    James Ahearne
    ------------------------------



  • 4.  RE: Difference between dates

    Posted 22 days ago
    Try changing the field type to formula numeric. I am thinking that your field type is currently formula duration.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: Difference between dates

    Posted 22 days ago
    Perfect. That worked. Thank you Mark.

    ------------------------------
    James Ahearne
    ------------------------------