Formula to define overlapping occuring dates

Pls attach a sample worksheet and show expected result.
Enter some typical example of data and explain the logic you want to use to figure out results.

Please attach an excel file. We don't want to create the file and enter all the data.

You'll get help if you make it easy for us to help you.

To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

• be sure to desensitize the data
• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

Try array entering this in F15 and fill down. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

Please Login or Register  to view this content.

Can't tell with the Formatting in a screenshot.

It's best if you let the formula "explain" itself.

On the FORMULAS ribbon find 'Evaluate Formula' ... Fx for short.

With one of the formula cells active apply Fx. Clicking repeatedly on the 'Evaluate' button will reveal step by step how Excel calculates the equation.

Of note: At some steps Excel returns an arrays of TRUE/FALSE. It is important to know that math operations (like multiplication) coerce those into their underlying values of 1/0. They in turn are often multiplied (usually) by some other value or array. Those results are often summed (as in this case).

The part I do not feel qualified to credibly explain is the FREQUENCY function. I've learned how to use it mostly by experimenting on my own, observation and dissecting (read Fx) formulas others have written. I am still learning some of its subtler features. This formula has some of those. So explanation is currently beyond me.

Don't be intimidated. Start with the "Help" files, experiment and examine formulas that use that function.

Oh yes. I almost forgot. With one of the formula cells active you can select just a portion(s) of the formula and hit the F9 function key. It will show you what Excel has done with that part. Try selecting just the IF(E15=$E$2:$E$9,$A$2:$A$9) part in cell F15. Upon hitting the F9 key you will see
{43287;43287;43385;43387;FALSE;FALSE;FALSE;FALSE} just as Fx will show. In this case you will be examining the parts out of context. Just be aware of that.

(By the way those 5 digit numbers are actually dates.) If you are not aware of it dates/times are numbers. The integers are the dates. They are the number of days since 1/1/1900.

I hope this helps.