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