bmegal4909
New Member
Hello excel gurus.
I'm trying to help a rural local bakery sort out their time sheets. They rent out their kitchen to different businesses and charge pricing based on 4 different variables: Non-overlapping time (excluding night hours), Night specific hours, Overlap with 2 businesses, and overlap with 3 businesses. Night hours and overlapping (i.e. sharing the space) provides the businesses with separate discounts. The overlapping discount overrides the night hours.
I started reading some of the older threads here and tried a sumproduct formula to at least notify if it was an overlap or not and it seems to work most of the time unless the hours overlap days (i.e. 10pm to 2am). At this point i'm held up in frustration.
I've attached a sample file with the desired outcomes. Does anyone have ideas that can break out overlapping vs. non overlapping hours by business name and allocate them accordingly?
I appreciate any and all suggestions/help.
thank you in advance!
brad
I'm trying to help a rural local bakery sort out their time sheets. They rent out their kitchen to different businesses and charge pricing based on 4 different variables: Non-overlapping time (excluding night hours), Night specific hours, Overlap with 2 businesses, and overlap with 3 businesses. Night hours and overlapping (i.e. sharing the space) provides the businesses with separate discounts. The overlapping discount overrides the night hours.
I started reading some of the older threads here and tried a sumproduct formula to at least notify if it was an overlap or not and it seems to work most of the time unless the hours overlap days (i.e. 10pm to 2am). At this point i'm held up in frustration.
I've attached a sample file with the desired outcomes. Does anyone have ideas that can break out overlapping vs. non overlapping hours by business name and allocate them accordingly?
I appreciate any and all suggestions/help.
thank you in advance!
brad