In the attached:
- Columns Q:T of Master sheet is your manually created expected results
- Columns K:N my results (conditionally formatted to highlight differences)
- Columns V:Y a comparison of my results versus your expected results (conditionally formatted to highlight differences)
- Column O is the state applicable to each row to get your results
- Column B of the PH sheet has highlights where you had some unusual dates
- I've altered the format of column B of the Master sheet to show days of the week (only to help visual checking of results)
I have made alterations to the existing
ShiftSplit user-defined function:
There is no change to how it's used in columns E:H of the
Master sheet.
Here's how it's used in columns K:N:
=IFERROR(ShiftSplit(B10,C10,D10,PH!$A$2:$B$109,O10)*24,"")
Singling out the function itself in that formula:
ShiftSplit(B10,C10,D10,
PH!$A$2:$B$109,
O10)
you can see an additional
2 arguments;
- the first new argument being the 2-column range containing the dates and states (in that order),
- the second new argument being the state for that row.
The second new argument (
O10) could be a literal string eg.:
ShiftSplit(B10,C10,D10,
PH!$A$2:$B$109,
"ACT")
or a reference to a single cell containig the state for the sheet eg.:
ShiftSplit(B10,C10,D10,
PH!$A$2:$B$109,
$J$1)
These last 2 new arguments are optional; their very presence indicates that the results will be the public holiday aspect of the working hours.
The formula is, as before, an array-entered one, 4 cells across entered in one go.
So far I haven't streamlined the code in the function; if I get time/inclination I will do this.
So now it's over to you to
test thoroughly.
There is an outstanding difference between your expected results and mine on row 112; I think that your expected results are awry (you can't expect Sunday public holiday hours on working overnight from Thursday to Friday!).