Splitting Date Range Not Adding Up

[xjohnson]

Hello,

Can someone please see what I'm doing wrong with my formulas in the Shift Hours! sheet please? If I change the date in B4 for all dates in the range C4:D4 the hours in I26 and K26 should add up to E26. Also I don't think there should be negative hours in the yellow areas.

Maybe my logic is not correct please help.

I appreciate it very much,
XJ

[XLent]

I think there are a couple of factors here

1. you are going to include the anniversary "day" twice in your Weekday # checks - so you have 16 days (I,K) rather than 15 (E:F)
2. similar to above you are discounting holidays twice over... in reality your holiday checks need to be relative to the periods covered in each anniversary section

so, I guess the first question is, in which section should the anniversary date itself be included, Column I or Column K?

[xjohnson]

Thank you for replying. Not sure if it matters but maybe the column I since it is first.

[XLent]

OK, in that case, without wanting to change logic too much from what you have:

Please Login or Register  to view this content.

I have highlighted in red-font the changes required in Column J, the change to the AND is to account for anniversary date falling on end-date, the +1 in the WEEKDAY check is to account for fact the anniversary date itself is being calculated in Column I.

[xjohnson]

Thank you so much. I tested every day in the range and it works perfectly.