0

convert formula to NINOX LANGUAGE

HOW CAN I GET THIS FUNCTION WORKING IN NINOX?
I am trying to have a table that keeps the working hours  for each day of the week, and a holiday table to store closed dates , then the formula to calculate end date based on that.

 

This formula first checks if the day is not a holiday. If not, it looks up the work hours for that day of the week in the WorkHours table. It then calculates the overlap between the work hours and the period from startDate to endDate, and adds the duration of the overlap to totalHours. It then moves on to the next day.

The hoursBetween function is used to calculate the duration of the overlap in hours. The max function is used to ensure that overlapStart is not before the start of the work hours or startDate, whichever is later. The min function is used to ensure that overlapEnd is not after the end of the work hours or endDate, whichever is earlier.

This formula assumes that startDate and endDate are datetimes that can be any time during the day or night. It also assumes that StartTime and EndTime in the WorkHours table are times on the same day, with EndTime after StartTime, and that EndTime can be after midnight.

 

let startDate := 'Your start date here'; // start datetime
let endDate := 'Your end date here'; // end datetime
let totalHours := 0;

while startDate <= endDate begin
    if not (select Holidays where HolidayDate = date(startDate)) then begin
        let workHours := first(select WorkHours where DayOfWeek = dayOfWeek(startDate));
        if workHours then begin
            let workStart := dateTime(date(startDate), time(workHours.StartTime));
            let workEnd := dateTime(date(startDate), time(workHours.EndTime));
            if workEnd < workStart then workEnd := workEnd + 1; // add a day if end time is after midnight
            let overlapStart := max(startDate, workStart);
            let overlapEnd := min(endDate, workEnd);
            if overlapStart < overlapEnd then begin
                totalHours := totalHours + hoursBetween(overlapStart, overlapEnd);
            end;
        end;
    end;
    startDate := date(startDate) + 1; // move to the next day
end;

totalHours
 

2 replies

null
    • Fred
    • 1 yr ago
    • Reported - view

    Ninox does a good job figuring datetime. The only thing you have to be aware of is that it is all done in milliseconds. So you only have to divide any results by 3600000 (number of milliseconds in a hour).

    I didn't setup a sample DB so I can't verify it. I think your code would look something like:

    let startDate := date(YYYY,MM,DD);
let endDate := date(YYYY,MM,DD);
let totalHours := 0;
if not (select Holidays where HolidayDate = startDate) then
    let workHours := first(select WorkHours where DayOfWeek = weekdayIndex(startDate));
    if workHours then
       let Inmilliseconds := workHours.EndTime - workHours.StartTime;
       let totalHours := Inmilliseconds/3600000;
    end
end

    Line 7 assumes EndTime and StartTime are datetime fields. If they are not then maybe you can consider switching over as you can take advantage of Ninox's date abilities.

    • Ppc
    • titanium_fish
    • 1 yr ago
    • Reported - view

    Thank you, for your time.

    The start Datetime and End datetime are formula fields.
    workhours table has a choice field for the day of week. and a time field for start hours and end hours.

Login to reply

Content aside

Categories

View all topics
  • 1 yr agoLast active
  • 2Replies
  • 89Views
  • 2 Following