Sum of hours, minutes and seconds

At my work we use a system for time tracking.
It outputs data in this format:
00:30:00
00:15:00
02:15:00
00:45:00
03:15:00
00:45:00
so it is hours:minutes:seconds

I would like a sum of the column, but still in the same format.
Is it possible without having to first convert the time to a decimal format, sum it up and convert back to hours:minutes:seconds?

don’t see a transformation for it. But I think it is not easy, as you need to define rules for calculation if hours sum up for more than 24. I think conversion into decimal numbers is the saver way. But maybe Admin has something in the pocket we don’t see.

1 Like

You can do it using Calculate with modulus and divide functions. But it would be very long winded.

Probably better to do it with some Javascript:

sum-times.transform (3.8 KB)

2 Likes

In the Calculate transform we have MSecsToDateTime and DateTimeToMSecs functions. Perhaps we also need:

-a TimeToMSecs function, e.g. that would convert “00:01:00” to 60000.0.

-a MSecsToTime function, e.g. that would convert 60000.0 to “00:01:00”.

2 Likes

That could also help me in this use case.

Here is my take on it. I am assuming that you want the sum at the end of the column, if it is not the case then @Admin solution is enough.

image

Transform file.
SumTime.transform (4.3 KB)

1 Like

I have added TimeToMSecs and MSecsToTime fucntions to the Calculate transform. These will be available in the next release and should make time-based arithmetic like this a lot easier.

4 Likes

Brilliant!
I took the liberty to add an If statement that adds :ss in case only hh:mm (no seconds) were used as input - this works for 24h format only, no AM/PM
(matching regex: ^(?:[01]\d|2[0-3]):[0-5]\d:[0-5]\d$).


See enclosed.
sum-times-2.transform (5.0 KB)

1 Like

Or, if you can live with a warning in a transform in case the data is already correctly formatted, use date-time transform instead of the if transform.

1 Like

A closer look at my data showed there never was any seconds.
So I went with the JavaScript option and used your script as input for ChatGPT to help refine it. And ended up with this:

function formatTime(h, m) {
    // Ensure the input values are treated as integers
    var hours = parseInt(h, 10);
    var minutes = parseInt(m, 10);

    // Calculate total hours and remaining minutes
    hours += Math.floor(minutes / 60);
    minutes = minutes % 60;

    // Convert hours and minutes to a formatted time string
    var formattedTime = hours.toString().padStart(2, "0") + ":" + minutes.toString().padStart(2, "0");

    return formattedTime;
}
return formatTime($(h), $(m));
1 Like

The new MSecsToTime and TimeToMSecs operations in Calculation are now available in v2.1.0:

1 Like