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.
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.
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$).
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.
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));