Long Excel Nested formula

Question

I have seen a formula (someone else wrote it) that can do some nifty nesting. I have the following cells data:

a2 = 9:07, and b2 = 9:33

Cells d1:h1 are time intervals, 8:30, 9:00, 9:30, 10:00 & 10:30

I want to see in cells d2:h2 how much time from the a2/b2 cells are in each interval. So, for instance I want to see 0:00 at 8:30, 0:23 at 9:00, 0:03 & 9:30 and 0:00 for the rest (in a real world it would have each 30 minute interval of the day. Anyone have any thoughts?

sergeibaklan · Answer

Hi Danny,
&nbsp;
That could be like
=(E1&gt;=$A$2)*(D1&lt;=$B$2)*(MIN($B$2,E1)-MAX($A$2,D1))

and attached

danny gordon · Answer

This worked perfectly! Thank you sir

Forum Discussion

Long Excel Nested formula

Share

Resources