Creating aggregate excel

Help Studio
, , , ,
1

Hi,

I have an excel sheet which looks like this:
image

Now I have the following assignment: In this excel sheet, they want to have another column with total time worked that day per truck while taking into account this calculation. Every first half hour of the day and every last half hour of the day shouldn’t count towards the total time worked. For example the very first entry has a duration of 29 minutes so it shouldn’t count towards the total time worked. If it were to be 35 minutes it should only add 5 minutes to the total time. So there needs to be some sort of aggregate per truck per day to calculate the total time worked minus the calculation. Would anyone know how to approach this?

Thanks in advance.

2

looks like a group by case and aggregate the group members

3

So, if Duration [h] is 1h 30m then we need to exclude the first half hour of the day, i.e., 30m, and the last half hour of the day, i.e., 30m from Duration [h]. So the total duration will be 30m. Is my understanding right?

4

Not entirely or I am understanding you wrong. Let’s say the truck drivers day starts at 06:30, that’s when he leaves from home. Then he arrives at 07:05, so his total driving time will be 35 minutes. That means that the first half hour, so the first 30 minutes will not add up to the total time, only those 5 minutes. For every duration that comes after that the rule doesn’t matter anymore, even if the time substracted is for example 25 minutes. Now this same rule applies to the last duration, if that duration is from 15:00 - 16:40, that’s the time he arrives at home, it would mean that only 30 minutes will be substracted from the total time. If you have more questions, please ask.

6

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