Forum Discussion
Calculate Duration Up Until A Specific Interval
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) ))))
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)))
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).
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.
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?
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:
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?
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) ))))- 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.