Converting minutes into days:hours:minutes

One of my clients wanted a report that was based on order statuses (as we know now, TODOLIST), displaying the time spent in each status in the format DD:mm:hh. The field TODOLIST.DURATION holds this time in minutes and the task is to convert it.

One might ask, what is the problem with using DTOA to convert the number? It seems that if one does not display a month, then the number of days in the current month is used. For 51936, a number that I had, the expression DTOA (51936, 'DD:hh:mm') gives the result 06:01:36, because there aren't more than 30 days in the current month (the expression is equivalent to 36 days and some change).

The form DOCTODOLISTLOG purports to give the number of days and number of hours in separate columns but this isn't what the client wants and it's also not exact (a fraction of hours is given, not the number of the minutes). The number of days is defined as (0.0 + TODOLIST.DURATION)/ 1440.0. I don't understand why the number is promoted to a real, as TODOLIST.DURATION / 1440 gives the same answer (36). But I'm not taking a chance, so I will use the same expression. As I want an integral number of days, I need to use ITOA, but this won't work on a real, so first I have to round the number, giving the expression (ITOA (ROUND((0.0 + TODOLIST.DURATION)/ 1440.0), 2). The final 2 is to ensure that the result will have at least two digits, where leading zeroes are added as necessary. If I had kept the number as a real and used RTOA, then I could have chopped off any remainder but there would be no leading zeroes.

What about the hours and minutes? The way that I did this in a report was first to define a value that is not displayed: TODOLIST.DURATION MOD 1440. This is done in order to simplify the remaining calculations. Using 51936 as input, this expression results in 96; clearly this is equivalent to 1 hour and 36 minutes, or #207 / 60 and #207 mod 60, where #207 is the number of the expression.

Thus the final string is given by the following formula

STRCAT (ITOA (ROUND((0.0 + TODOLIST.DURATION)/ 1440.0), 2), ':',

ITOA (#207 / 60, 2), ':', ITOA (#207 MOD 60, 2))

If TODOLIST.DURATION is 51936, then this monstrosity results in the string 36:01:36  (yes, the client wanted that if the duration were over 12 days then it would appear in red - but that's another story).