Forum Discussion
Formula Assistance Needed
Left all your columns/formulae in place, but added 22 (!!) helper columns (grouped J:AF) with the end result in AF. The first six columns restructure the dates and time a bit so that it becomes easier to calculate with. Then I do a range of tests that are needed to calculate the overlap of down-time and working hours.
Not very elegant, but still manageable. And I couldn't think of a better way to do it.
Yeah both of us working on the same excel together actually.
Sorry for the confusion.
I have cleared most of the problems on the sheet. Attached for your reference.
Understood that there will be a problem if the value goes negative for Operation Downtime Duration column. Is there any other method that we can try with ? Else then I guess we should calculate it manually and state it.
Kindly advise, please.
Thank you.
Left all your columns/formulae in place, but added 22 (!!) helper columns (grouped J:AF) with the end result in AF. The first six columns restructure the dates and time a bit so that it becomes easier to calculate with. Then I do a range of tests that are needed to calculate the overlap of down-time and working hours.
Not very elegant, but still manageable. And I couldn't think of a better way to do it.
Great to have your guidance and assistance! I did tried cross-checking the output, there is several invalid / wrong outputs shown. For example : Station 12671 & Station 8749. Downtime outside the working hours is still being shown.
Is it due to the date and time range ? But on other dates or on other stations mostly the data is valid.
Attached for your reference. Kindly assist to advise, please.
Giving it one more shot, after thinking through the complexity of what seemed to be a simple question. Scrapped my previous approach and thought of something completely different. The file is large now (over 13MB) and a bit on the slow side.
A while ago, I helped someone creating an hourly shift scheduler. Taking a start and end time and map those (with ones and zeros) on a 24 hour scale. Relatively easy! Have now applied the same idea for mapping the shifts and down times. By comparing the two sequences of zeros and ones (using SUMPRODUCT) you can work out the number of overlapping hours without having to deduct start times and end times and account for the fact that these may carry-over midnight. But then you still need to do quite some fiddling around by counting in the minutes. And that proved to be quite a challenge. Am still not certain that I captured everything. If it's still wrong, I give up. Sorry!