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.
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!
The latest method looks easier. Thanks for your assistance. I will analyse and improvise this file.