0

Time interval use and sum

Hi,

there is something I don't seem to understand (odd, uh?) when use time interval fields.

I want to define the daily working time and since it doesn't matter when job starts or ends, I thought time interval could be the right choice of field.

Still, I don't understand how to tell Ninox that the number I'm going to insert it's gonna be hours or minutes or days and when I sum all the daily time intervals to have a full week working time, I get strange results.

My aim would be to have something like this.

Monday: 8,00 Hours

Tuesday: 8,00 hours

Wednesday: 8,00 hours

Thursday: 8,50 hours

Friday: 7,50 hours

Saturday: 0 hours

Sunday: 0 hours

Week: 40 hours (Mo + Tu + We + Th + Fr + Sa + Su)

8 replies

null
    • Fred
    • 1 yr ago
    • Reported - view

    can you post the code you are working with?

      • Gianluca
      • 1 yr ago
      • Reported - view

      Fred the single days (Lunedì, Martedì, etc...) have no code. They are just set as Time interval fields and I write in the amount of hours one works every day.

      For the sum I had to use a formula field, so the code is the following: 

      let XContOre := 'Lunedì' + 'Martedì' + 'Mercoledì' + 'Giovedì' + 'Venerdì' + Sabato + Domenica;
format(timeinterval(XContOre), "hh:mm")

      But I get a keep getting wrong results, no matter what way I insert the values in the fields. The only way it turns out to be right, it's when hours in day fields are all full hours.

    • Fred
    • 1 yr ago
    • Reported - view

    So here is official word from Ninox:

    Time Interval

    A Time interval is the time between two points in time. A Time interval is the most precise method to measure time, because several days, hours and minutes are supported. If you want to register worked hours, for example, you could use Time interval to record the idle time.

    If the time you want to measure is below 24 hours, it might make sense to ‘misapply’ an hour field, because the hour field is more user-friendly.

    If you are sure you only want to record "hours" worked then maybe it would be best to use a number field.

    The thing about Ninox is that all date/time fields are really kept in milliseconds. If you take your Time Interval fields and put it inside number(), you will see a very large number returned. Then if you take that number and divide by 3600000 (the number of milliseconds in an hour) then you will see a number that makes sense.

    When I added up a bunch of Time Interval fields it looks like it assumes you are tracking hours in a day so it will count up to 24 hours then start counting in days.

      • Gianluca
      • 1 yr ago
      • Reported - view

      Fred 

      Fred said:
      When I added up a bunch of Time Interval fields it looks like it assumes you are tracking hours in a day so it will count up to 24 hours then start counting in days.

      I don't really get the way ninox is counting times, because when i added time interval fields the result was strange: 6:50 hours from monday to friday and the 3:00 hours on saturday gave as result 1:33.00 (in a format of hh:mm). 

      Anyway...I don't mind at all using a number field to track hours, as long as I can format it to look like hours.

      But now i wanna take this one step further: once i have my working time set as number (in decimal mode), how can i create a "mirror" field to also show time in hours and minutes (to explain better: if i write 6,50 hours in number field, i want another field to show 6 hours and 30 mins). Is that possible? In filemaker I made a simple conversion but here i was not able to....

    • Fred
    • 1 yr ago
    • Reported - view

    Never have really worked with timeinterval before so I'm not sure what it is doing. From what Ninox said about tracking worked hours by recording idle time makes me think it is not as straight forward as we think.

    Looking at your code. 

    let XContOre := 'Lunedì' + 'Martedì' + 'Mercoledì' + 'Giovedì' + 'Venerdì' + Sabato + Domenica;
format(timeinterval(XContOre), "hh:mm")

    Line 1 adds up time intervals, then line 2 converts the total into a time interval. That is where things get tricky and I'm not sure what is happening.

    Gianluca said:
    But now i wanna take this one step further: once i have my working time set as number (in decimal mode), how can i create a "mirror" field to also show time in hours and minutes (to explain better: if i write 6,50 hours in number field, i want another field to show 6 hours and 30 mins). Is that possible?

    Most things are possible. We will start by remembering that Ninox works in milliseconds, so that means we have to convert 6.5 hours into milliseconds (multiply by 3600000). Then we can use the time function to convert the milliseconds into time. So something like: 

    let xConv := fieldname * 3600000
time(xConv)

    So if you type in 6.5 it will convert it to 06:30. Seems to me that sticking to decimal is easier for people to understand in terms of work hours.

      • Gianluca
      • 1 yr ago
      • Reported - view

      Fred indeed it’s easier, but unfortunately I also have to deal with people who prefer complicated stuff lol…

      thank you for that! 

      I’m going to use numbers for decimal and your calculations for hh:mm.

       You’re a rockstar!

    • Fred
    • 1 yr ago
    • Reported - view

    Playing around you can do the following as well: 

    let XContOre := number('Lunedì' + 'Martedì' + 'Mercoledì' + 'Giovedì' + 'Venerdì' + Sabato + Domenica);
format(time(XContOre), "hh:mm")

    So you don't have to do too many changes. It does mean you have to input data as time (6:30) not as decimal (6.5).

      • Gianluca
      • 1 yr ago
      • Reported - view

      Fred I've managed to get what I wanted.

      I also re-thought everything after deciding to use number fields for hours and time intervals.

      I set up a formula the way I used to do in Apple Numbers/Excel. I leave it here in case it may be interesting for someone. This way you can write times the easiest way (2 minutes to midnight would be 23,58) and if you want to calculate the time interval you can then just use a simple conversion:

      So...if you have a field named "FromHour" and another one named "ToHour", your time interval in decimals would be 

      let xFromH := floor(FromHour);
let xFromMin := FromHour - xFromH;
let XDecFrom := xFromH + xFromMin / 60 * 100;
let xToH := floor(ToHour);
let xToMin := ToHour - xToH;
let XDecTo := xToH + xToMin / 60 * 100;
let xTimeInterval := XDecTo - XDecFrom
xTimeInterval

      and then you can easily reverse this swapping operations.

      Beware, though: this formula needs more calculation if you use am and pm instead of 24 hours and if your time intervals exceed the 24 hours.

Login to reply

Content aside

  • Status Answered
  • 1 yr agoLast active
  • 8Replies
  • 264Views
  • 2 Following