Hello, can anyone help?
At work we have time frames when work is blocked by system errors.
What I want to do is calculate the total blocked time without the overlaps in blocker time frames.
Please have a look at this example picture:
excel_question.png
What I want to calculate is E9.
Thank you very much!
Please upload a workbook. It is always easier to advise if we can see your request in its context.
Untested but have you tried in say D2 copied down
=Min(G$2,B2)-Max($G1,A1)
Then just sum D2:D7
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
thank you Richard, but this solution doesn't work
I have tried your advice, but column D doesnt show the expected (column E) values..
I have uploaded the excel file though: http://www.file-upload.net/download-...t_01.xlsx.html
Last edited by happyhippo79; 12-16-2016 at 06:44 PM.
Please upload the workbook to this forum. Many of us avoid file hosting sites whose provenance is uncertain.
Choose Go Advanced and look below the post for the 'Manage Attachments' area.
I have followed your advice and uploaded the excel workbook.
Hope that helps
It will if you make G1 & G2 proper time numbers. At the moment they're just text. i.e. 08:00:00 and 16:30:00
There was a typo in the formula $G1 should of course have been an absolute. i.e. $G$1
Hello Richard, thank you for your comment. It still doesn't do the trick.
If I get it right, your formula only calculates the overlaps with "start of day" and "end of day" times.
But I also wanted to calculate the overlaps of every time frame and subtract it from the total blocker time in order to get the "real" blocked time (without overlaps).
I have included the File Test_02.xlsx in order to make more obvious what I want to calculate.
Hi,
Leave the formula I gave you in D2, (the first data row) and in D3 copied down
Formula:
Please Login or Register to view this content.
WOW Richard. It works! You're officially my new hero!
Thank you so much for your kind advice and your patience with an excel amateur
Okay,the two formulas are working under certain conditions.
But unfortunately they don't work when a) the second timeframe starts earlier than the first one or b) ends earlier than the first one.
Can anyone help to solve that problem?
(I have uploaded the file Test_03.xlsx to demonstrate the problem)
Does this correction in D3 work?
Or to be strictly correct since I know it works in the example given, does it work with other permutations of data or does it still need tweaking?
Formula:Please Login or Register to view this content.
Hello Richard, thank you so much for you quick reply!
I have found one more permutation where the formula doesn't work: When the second timeframe starts earlier AND ends later than the first timeframe..
(I've included the file Test_04.xlsx)
correction: I have found that the formula doesn't work in cell D2-D6(sorry for my confusion) Explanation: The correct results (the ones I want to get) are displayed in column E.
(I've included the file Test_06.xlsx)
Last edited by happyhippo79; 01-28-2017 at 02:42 PM.
..You forgot to attach the file
sorry for my mistake. I'll try to attach again!
I'm confused.
What are the actual results you want? I was under the impression that the results are in column C and that column E is the difference that existed at some previous time.
Are you saying that the times currently showing in in column E are the results you expect?
Yes, that's exactly what I am saying
Column D is supposed to display the contents of column E. That's what I need the automatic correction for. I'm sorry if I didn't express that clear enough. My English is very bad...
OK,
Try in D3 copied down
Formula:Please Login or Register to view this content.
WOW. Richard, you are the BEST! I cannot thank you enough for your help and patience!
Richard, I'm sorry to bother you again. The formula you gave me is almost perfect, but something doesn't work
It displays the wrong time in D3, when the second timeframe starts earlier and ends later than the first timeframe... Cell D3 is supposed to show the content of E3.
(I've included the file Test_07.xlsx in order to show what's happening in D3)
Last edited by happyhippo79; 01-28-2017 at 09:44 PM.
Presumably that rule about the start time being earlier than, and the end time being later than on any row, is comparing that rows start & end to ANY previous rows start & end and not just the preceding rows start/end?
In which case use in D3
Formula:Please Login or Register to view this content.
but please check with other permutations
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks