0

calculate time elapsed between two concomitant records

Hi, I would like to know how to calculate time elapsed between to concomitant records; for example a record have a field named date_time and I would like to know the time elapsed between record 1 and next record 2 and so on (between 3 and 4; 5 and 6 ect.) I guess I should use a script for this, any idea?

20replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hi,

    as formula field

    ---

    let myID := number(_id);
    let nextDateTime := first(select 'Your Table' where number(_id) > myID).'date_time';
    nextDateTime - 'date_time'

    ---

    Leo

    Like
    • ESM
    • ESM
    • 4 yrs ago
    • Reported - view

    Hi Leo,

    First at all tks a lot. I was able to make it works even if I do know 0 about Ninox scripting. Hope I can learn more and have time to time a little help.

    Captura de pantalla 2018-08-16 a la(s) 11.44.34 p.m.

    Like
    • ESM
    • ESM
    • 4 yrs ago
    • Reported - view

    Hi Leo,

    First at all tks a lot. I was able to make it works even if I do know 0 about Ninox scripting. Hope I can learn more and have time to time a little help.

    Captura de pantalla 2018-08-16 a la(s) 11.44.34 p.m.

    Like
    • ESM
    • ESM
    • 4 yrs ago
    • Reported - view

    Hi Leo,

    How can I format the column 'Duracion' in hh:mm only? I tried but once it gave me 7 hours added.

    Like
    • ESM
    • ESM
    • 4 yrs ago
    • Reported - view

    Hi Leo,

    How can I format the column 'Duracion' in hh:mm only? I tried but once it gave me 7 hours added.

    Like
  • Bildschirmfoto 2018-08-18 um 07.44.00

    Like
    • ESM
    • ESM
    • 4 yrs ago
    • Reported - view

    Hi Leo,

    Tks for the hint, but the formula field I want to format as hh:mm which I did named initially as "Duracion" but to avoid name confusion I renamed as "Tiempo" as far I understand only can be formatted as a number. So there is a way toformat the result of this formula field called now "Tiempo" as hh:mm or I have to create another field type Duración to point to this on each record?

    Tiempo field

    Like
    • ESM
    • ESM
    • 4 yrs ago
    • Reported - view

    Hi Leo,

    Tks for the hint, but the formula field I want to format as hh:mm which I did named initially as "Duracion" but to avoid name confusion I renamed as "Tiempo" as far I understand only can be formatted as a number. So there is a way toformat the result of this formula field called now "Tiempo" as hh:mm or I have to create another field type Duración to point to this on each record?

    Tiempo field

    Like
  • let myID := number(_id);
    let nextDateTime := first(select 'Your Table' where number(_id) > myID).'date_time';

    let myT := number(nextDateTime - 'date_time') / 1000 / 60 / 60;
    floor(myT) + ":" + round((myT - floor(myT)) * 60, 0)

    Like
    • ESM
    • ESM
    • 4 yrs ago
    • Reported - view

    Hi, tks again. I have to give a try to learn this scripting language. There is any document(s) regarding? By the way the funcions fields can be alligned; for exaple the field "Tiempo" to the right?

    Captura de pantalla 2018-08-18 a la(s) 8.07.19 p.m.

    Like
  • Hi,

    Tiempo is formatted as a text, and a text is aligned to left. Try this:

    html("<p align='right'>" + 'Text field' + "</p>")

    Leo

    Like
    • ESM
    • ESM
    • 4 yrs ago
    • Reported - view

    Hi,

    Is there where it goes?

    Screen2

    But it does nothing...

    Screen1

    Like
    • Nick
    • Nick
    • 4 yrs ago
    • Reported - view

    This code works only with Form view and NOT in table view...

    Like
    • ESM
    • ESM
    • 4 yrs ago
    • Reported - view

    Hi,

    Well where I should put the scirpt you send me before on the table?

    Captura de pantalla 2018-08-20 a la(s) 2.42.28 a.m.

    Like
  • Sorry, that's not possible yet. The formula really only works in form fields.

     

    Leo

    Like
    • Nick
    • Nick
    • 4 yrs ago
    • Reported - view

    The 'Right' field is a formula field with the code -> html("<p align='right'>" + Period + "</p>")

     

    Screen Shot 2018-08-20 at 11.18.04

    Like
  • You can display the time in format "00:00". Then the data is shown evenly in the column

    ---

    let myID := number(_id);
    let nextDateTime := first(select 'Your Table' where number(_id) > myID).'date_time';

    let myT := number(nextDateTime - 'date_time') / 1000 / 60 / 60;
    format(floor(myT),"00") + ":" + format(round((myT - floor(myT)) * 60, 0),"00")

    ---

    Leo

    Like
    • ESM
    • ESM
    • 4 yrs ago
    • Reported - view

    Hi Leo,

    After applying your script of above, below is the result, which is ok for me so I'm moving on with this little proyect. Tks a lot for the help, the formula on this field is doing his job well.

    Only a complain with / for Ninox: I cannot find documentation on their website, to explain more thoroughly the script language.

    Captura de pantalla 2018-08-20 a la(s) 12.15.12 p.m.

    Like
    • ESM
    • ESM
    • 4 yrs ago
    • Reported - view

    Noted. Tks

    Like
Like Follow
  • 4 yrs agoLast active
  • 20Replies
  • 7149Views