SOLVED

Calculate Duration Up Until A Specific Interval

Iron Contributor

Hello,

 

I'm once again in need of your expertise.

 

We have a raw data source that lists an employee's status in intervals. It lists the data as Start Time and End Time. I need to sum the duration of a specific status up until a selected interval. The intervals, will be listed selected from a dropdown menu. For example, if an employee is on "Working Status" between 16:00 and 20:00 and I select the interval of 18:00 - 19:00 from the drop down, it should list the duration as 3 hours. But the data doesn't divide these statuses into intervals. It lists as Start time and end time. The employee could be on "Working Status" until 18:00 then take a break for 10 minutes. Then after the break it creates a new entry starting from 18:10. I was tinkering with SUMIFS. It feels like it's the function that I need to use. However, I've bumped into a programming issue with this. I'm somewhat a beginner in programming can't yet think of a possible solution.

 

 

=SUMIFS(RawMMP[Duration1],RawMMP[Name],A2#,RawMMP[Status],"Moderation Task",RawMMP[Time Value],SMALL(RawMMP[Time Value],1))

 

 

My not yet matured logic here is; I created direct cell references for time values and converted into numbers. Using SMALL function, I got the earliest start time and used it as a criteria. However, it's flawed. Since there will only be one smallest value, there will only be one criteria for that match. I'm thinking if i could modify the criteria to include all the time values up until that interval, it could be done. However, that's where I fell short. I couldn't think of how I could do that.

 

I hope I'm clear enough. If not, i can provide more info.

This is how the data source and dropdown menu looks like. I'm also attaching a sample of the data source.

 

kheldar_0-1646344377841.png

 

 

kheldar_1-1646344404229.png

7 Replies

@kheldar 

 

It's not really clear what you're trying to determine...I may have missed it, but after re-reading your description at leasts twice, I decided to take a stab at a formula that sums the "Duration1" field for a given employee and given task with the ability to select those from a dropdown list.

 

This formula uses the FILTER function, which does require the newest versions of Excel.

=SUM(FILTER(RawMMP[Duration1],(RawMMP[Email]=I16)*(RawMMP[Status]=P16)))

mathetes_0-1646360467166.png

 

 

If this is not what you were looking for, please come back and give an example from the data of what the result should be; let me or somebody else figure out how to deliver that result.  Your current description tells how you're trying to produce a result, but never really spells out what the result is that you're trying to produce (because you're so familiar with it, you think it's obvious).

 

@mathetes 

 

Thank you for your response. I'll try to be more precise. I'm an amateur in programming and I'm learning from experts on this community. I've started from almost zero knowledge. The way I express myself can be confusing because sometimes I'm not yet able to visualize what kind of information and explanation experts would exactly need in a programming perspective.

This a file I'll be using to track our employees in real time. It'll be used on a daily basis. So dates are irrelevant. Only durations and intervals are what I'm after. Raw data source includes their working statuses in intervals. "Moderation Task" and other forms of "non-moderation task" and "break". However, these intervals are not hourly. For example, an employee would start their shift on 16:00 be on "Moderation Task" for 1 hours and 50 minutes then take a break for 10 minutes. The entry from the raw data source will include 1 hours and 50 minutes in one row stating the start and end time. Then another entry for 10 minutes. then another entry for the remainder. What I want is to be able to sum values of "moderation task" up until selected interval from the dropdown menu. So If I select 18:00 - 19:00 interval I want to see the sum of "moderation task" duration up until 19:00.

 

This is how the main sheet looks like. I couldn't share it because it includes employee and company records.

 

kheldar_0-1646363683630.png

I thought of FILTER as well. I was just tinkering with it. Perhaps, I could do it if there is a way to reference an element in FILTER result array? So, lets say I want to reference the first value in the array, could it be done?

 

kheldar_1-1646363874254.png

 

 

 

@kheldar 

You wrote: 

I thought of FILTER as well. I was just tinkering with it. Perhaps, I could do it if there is a way to reference an element in FILTER result array? So, lets say I want to reference the first value in the array, could it be done?

 

Yes, FILTER can do amazing things. Your data is probably more complicated than it needs to be (I say that, of course, as somebody outside looking in, so I could be missing something). FILTER can be set to work with multiple criteria, including ranges of given values (like blocks of time). 

 

Rather than trying to give you a solution, which would take more time than I have at present, let me refer you to this YouTube video -- which is where I first learned of FILTER and some associated Dynamic Array functions -- and turn you over to your own devices. 

https://www.youtube.com/watch?v=9I9DtFOVPIg

 

If you still need assistance, come back and post your actual file after first replacing any real names with fictitious ones. And I have to say that you still are (at least to me) confusing in what you describe as the task. For example:

What I want is to be able to sum values of "moderation task" up until selected interval from the dropdown menu. So If I select 18:00 - 19:00 interval I want to see the sum of "moderation task" duration up until 19:00.

But don't you want the sum of "Moderation Task" for prior intervals (not just that one hour of 18:00-19:00) for the entire calendar day?  Or maybe, for the entire calendar day up to 19:00, but if that's the case, why worry about the interval?  Do you see the confusion here? And why I might view your data as far more detailed than would seem necessary? 

 

Now, I'm coming at this from the perspective of a person who worked as director of an HR/Payroll database and reporting system, so we did track working hours as well. I don't EVER recall seeing time tracked to this degree of detail, so I will acknowledge that you may be doing this for necessary government reporting or some other legal reporting reason.....But take this screen capture as an example of why I find your task confusing:

mathetes_0-1646408490653.png

There's a level of data redundancy in there that is mind-boggling. If what you're seeking is total time devoted to "Moderation Task" why not simply total the "Duration" column for a given calendar day (which is off to the left, off screen) between a start and end time? If that in fact is what you want--which may well be the case--then say that. More simply, though, is there a reason why it wouldn't be even more simple--total of "Duration" for "Moderation Task" for given employee in a single calendar day?

Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, but were limited to power users. With Dynamic Arrays we have rebuilt the calc engine, effectively turning all formulas into array formulas ...
best response confirmed by kheldar (Iron Contributor)
Solution

@mathetes I think the 'complexity' comes in because of the range chosen. So I believe @kheldar  wants to sum all the time on 'Moderation' UP TO the end of the selected range.
Some of the issues I see here include:
original formula was looking at [Name] when it should have been looking at [Email]
the range drop down is very hard to work with. why not just use an end time that is either actual time format or at least is text that can be easily converted (as opposed to TIMEVALUE(RIGHT([dropdown],5))
as for how to calculate I think you need to create a conditional duration:
=sumproduct( (check name)*(check activity)*
               if( [end time]<=[drop down],
                     [duration],
                     if ( [start time]<[drop down],
                             [drop down] - [start time],
                              0
                     )
              )
   )
assuming you have Excel 365 I did it using LET() so

=LET(dTime,INT(MIN(RawMMP[End Time]))+TIMEVALUE(RIGHT($V$6,5)),
           SUMPRODUCT((RawMMP[Email]="Random Employee 1")*(RawMMP[Status]="Moderation Task")*
              (IF( RawMMP[End Time]<=dTime,
                    RawMMP[Duration],
                     IF( RawMMP[Start Time] < dTime,
                        dTime - RawMMP[Start Time], 0)
           ))))

@mtarler 

 

You wrote: So I believe @kheldar  wants to sum....

 

You're probably right, Matt. One of the difficulties providing help here on this site is the necessity, so often, to make assumptions. It's so much easier if we could have face-to-face conversations, not rely on exchange of text messages.

 

Anyway, I hope your LET function does it.

 

so true. so true.
I can't thank you enough. I managed do what I wanted after tinkering with your formula. I thank all of you trying to help me out. Sharing knowledge is priceless. I learn by your help.
1 best response

Accepted Solutions
best response confirmed by kheldar (Iron Contributor)
Solution

@mathetes I think the 'complexity' comes in because of the range chosen. So I believe @kheldar  wants to sum all the time on 'Moderation' UP TO the end of the selected range.
Some of the issues I see here include:
original formula was looking at [Name] when it should have been looking at [Email]
the range drop down is very hard to work with. why not just use an end time that is either actual time format or at least is text that can be easily converted (as opposed to TIMEVALUE(RIGHT([dropdown],5))
as for how to calculate I think you need to create a conditional duration:
=sumproduct( (check name)*(check activity)*
               if( [end time]<=[drop down],
                     [duration],
                     if ( [start time]<[drop down],
                             [drop down] - [start time],
                              0
                     )
              )
   )
assuming you have Excel 365 I did it using LET() so

=LET(dTime,INT(MIN(RawMMP[End Time]))+TIMEVALUE(RIGHT($V$6,5)),
           SUMPRODUCT((RawMMP[Email]="Random Employee 1")*(RawMMP[Status]="Moderation Task")*
              (IF( RawMMP[End Time]<=dTime,
                    RawMMP[Duration],
                     IF( RawMMP[Start Time] < dTime,
                        dTime - RawMMP[Start Time], 0)
           ))))

View solution in original post