Overlapping time formula help

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.

Attachments

• 18 KB Views: 14

Peter Bartholomew

Well-Known Member
The recent post
is also relevant here. There seem to be two distinct strategies. The first is to create a timeline at the appropriate granularity (15min here) and perform a headcount for each interval. The results are given by counting 15min intervals.
The second approach was to perform the headcount only when someone arrives or departs. That gives fewer calculations but involves summing intervals between events.

Peter Bartholomew

Well-Known Member
I have made some progress. At the moment it is full of (dynamic) array formulas because that is the way I work. I might be able to build the formulas back into a table once they are working.

Attachments

• 21.8 KB Views: 6

bmegal4909

New Member
Wow, thanks Peter! These formulas are certainly way over my head so I will need to take some time and study them. It seems that your current set up is able to distinguish the overlapping business times but is lumping the non-overlap and night times together. I'll see if i can help differentiate in some way. Thanks again.

Peter Bartholomew

Well-Known Member
Sorting the overlap between the non-night hours and a given business's rental period is not a trivial task in itself. The attached workbook does it but you will find it complicated and difficult to extend. It may be that splitting the day into quarter-hour intervals and evaluating the state at each might provide a more straightforward solution.

Attachments

• 29 KB Views: 7

bmegal4909

New Member
Thanks Peter! Is there a way to remove the arrays and utilize non-array formulas for this type of scenario as you referenced above? The .xlfnarray functions don't allow me to process what the formula is actually doing. I'm familiar with named ranges but struggle to follow what columns J through W are actually calculating. The thought of expanding these formulas to a full time sheet and the weekly up keep seems nonfeasible to me with a array function, but thats likely because i've rarely dealt with them.

Peter Bartholomew

Well-Known Member
I have used Office 365 with dynamic arrays so, for me, arrays offer the path of least resistance. I had enough of a challenge working through the complexities of your requirement without the handicap of trying to remember legacy techniques. The source data is an Excel table that grows as you add data and, unlike CSE arrays, the DA formulas just grow to match. I could probably split some of the formulas so that they fit within the table but , as yet, I have little knowledge of how you plan to handle future days and whether the reporting is then by the day or by the month.

All the functions that generate the .xlfn descriptions are doing is extracting a list of distinct companies in sorted order. You could probable afford to overwrite the formula with hand-calculated lists.

vletm

Excel Ninja
bmegal4909
If You would like to other kind of solution then test this
by pressing [ SOLVE ]-button.
It will give same results with Your sample data ... but ... shown by time (hours:minutes).
Your 'input-dropdowns' are 15min steps ... if You would use other steps then modify m15-variable as needed.
Ps. You won't see/ use any formulas there (which could someone ... eg modify or delete even by mistake).

Attachments

• 29.1 KB Views: 11

Peter Bartholomew

Well-Known Member
I think this does it with formulas. I have replaced the dynamic arrays with tables in order to retain extensibility. I am rather less fluent with non-array solutions so it was a bit of a struggle at times.

Attachments

• 42.4 KB Views: 10

p45cal

Well-Known Member
Macro solution attached, click button near cell K3.
You're not limited to 15 minute intervals, if you want to input times with minutes and seconds it should give the correct result to minutes and seconds.
A few small tweaks to the code would allow you to enter periods exceeding 24 hours.

Attachments

• 34.4 KB Views: 10

Peter Bartholomew

Well-Known Member
@p45cal
That looks like a serious bit of coding! There are too many choices these days: code; dynamic arrays; power query …

bmegal4909

New Member
Thank you all for taking the time to look at this. I've been traveling so i haven't had the pleasure to review the options provided but I plan to review in the next day. Thanks again.

bmegal4909

New Member
I think this does it with formulas. I have replaced the dynamic arrays with tables in order to retain extensibility. I am rather less fluent with non-array solutions so it was a bit of a struggle at times.
Hi Peter, Should all of these formulas work in excel 2010? that is the version i am running but when i try to type your formulas into my core file i receive "function not valid" errors or "the name you entered is not valid" errors.

bmegal4909

New Member
Thank you all again for providing fixes for me. Im trying to avoid macros as i need to be able to trouble shoot issues in the file as they arise with the Bakery versus always asking for help from you guys. I apologize for my lack of macro knowledge.

Peter Bartholomew

Well-Known Member
I had rather forgotten this . Tables have existed from Office 2007 and I think SUMIFS was there in 2010. To use SUMIF you would need a helper range to combine the criteria. MAXIFS is the most likely sticking point, but I think replacing
= MINIFS( Times, Times, ">"&[@EventDatetime2] )
by
= MIN( IF( Times>[@EventDatetime2], Times ) )
should help. The latter formula requires a single cell CSE array formula unless you use a Named Formula and output the single values
= IntervalEnd1
= IntervalEnd2

[The attached has formulas in these defined names]

Attachments

• 49.1 KB Views: 3
Last edited: