Forum Discussion

Jan-WillemSpeck's avatar
Jan-WillemSpeck
Qrew Assistant Captain
7 years ago

about duration and variables

My objective is the calculate the duration between end and start (in minutes).
In the end I want to allow the user different ways to enter end and start:
- set a flag in a checkbox that will create a 'DateTime-field' timestamp for end and start
- entering a manual end and start time as 'DateTime-field'. In case they missed the TimeStamp itself)
- entering a duration manual (bypas end and start time) in minutes as 'Numeric-field'. In case they completed a task without knowing precise end and start anymore
You can plan everything via a strict procedure in QB or another tool but the daily realistic fact is that they will perform always tasks without being able to flag a task or without having access to the app for a longer period and I still want to track some basic indication on spend time without the need to simulate a precision level they don't have.


I was thinking to create variables for the different fields and do some ?f-else calculations with these variable to calculate the best possible final duration.
If a timestamp is known use it, if not use the manual given time instead if not use the manual estimated duration.

But...step by step creating the right format I encounter the following mystery that I can't solve.
This time and duration conversion is so confusing.

I'm testing with just 3 fields now
  1. 'FormulaDuration' field [Duration]
  2. 'DateTime' field [EndTimeStamp]
  3. 'DateTime' field [StartTimeStamp]
The following formula works:
[Duration]=
[EndTimeStamp]-[StartTimeStamp];

But when I convert it to a variable formula it doesn't work anymore
[Duration]=
//declaration
var DateTime end=[EndTimeStamp];
var DateTime start=[StartTimeStamp];
//calculation duration
end-start;

What is my mistake here?


  • When you say something does not work, you should explain if it is a syntax error (and post the error) or an unexpected calculation result.
    In your case it is a syntax error.  That will solicit quicker responses from the Forum Community.

    When you define a formula variable and then subsequently to refer to it in a formula, you need to put a $ in front of the formula variable name so Quick Base knows that you are referring to a formula variable.

    So, that last line needs to be:

    $end - $start.