Rollup hours

Hello there! Could anybody help me to rollup hours total for each participant. I tried to rollup but only two option are proposed (count and count unique).

How can i rollup hours?

If you only get count/count unique, that means the column you are trying to rollup is not recognised as a number type.

Can I see a screen shot of that column (including the column header) from the Glide Data Editor please?

1 Like


Here is the screenshot. Thank you for your assistance

S1HD = Session 1 : Start’s hour
S1HF = Session 1 : end’s hour
S2HD = Session 2 : Start’s hour
S2HF = Session 2 : end’s hour
Cumul/Jr = Addition of the two session in a day = total duration of the task

I would like to rollup the column “cumul/Jr” in order to get the time spent by each name

Okay, that column is returning a duration, and you can’t roll up a duration.
You would need to convert it to either days/hours/minutes/seconds first (whichever makes sense), then rollup that value, and then convert it back (if required).

Can you show me your current formula in the Cumul/Jr column?

How to convert it to days/hours/minutes/seconds?

That’s why I asked to see your formula :wink:

Okay, to get your total duration in hours, use the following:

Trunc((S1HF - S1HD)*24)
+ Trunc((S2HF - S2HD)*24)

This will give you a result in decimal hours, which you should be able to use for a Rollup->Sum

What format is required for the result of the rollup?

The result should be the total of hours spent (10:30:00 for exemple witch means 10 hours et 30 mn)

Do you care about the seconds, or would hh:mm be good enough?

hh:mm is good enouch

Okay…

First of all, change that earlier formula that I gave you to the following:

Trunc((S1HF - S1HD)*1440)
+ Trunc((S2HF -S2HD)*1440)

This will give you a result for each row in minutes instead of hours.

Then do your rollup->sum on that column.

To get that back into hh:mm, you need 4 additional columns…

  • First one is to extract the number of hours from the rollup result:
Trunc(Total/60)

  • Next one is to extract the number of minutes:
Mod(Total,60)

  • Then an if-the-else column to determine if the minutes should be zero-padded:

  • And then finally a template column to join all the bits together:
{hh}:{pad}{mm}:00

I realise that’s all quite convoluted for something you’d think should be pretty simple.
To be honest, this is a case where I would probably replace all of that with a single JavaScript column.
But I just wanted to show you how to do it without using code.

2 Likes

VERY VERY GREAT. THANKS A LOT DARREN

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.