Grouping dates according to set periods which may overrule each other

[NorthernLars]

I'm trying to build a formula for pilot theoretical exams. There are a set of rules for how and when to take them, and I'm not sure where to start.

Rules are:
-There are 9 exams
-Each exam can be attempted up to 3 times
-All must be completed within an 18 month period.

Now, I've already got a spreadsheet for the above, but here's where it gets tricky:

-Exams must be taken in a maximum of 6 sittings
-Each sitting lasts 10 days from the first exam attempted in that sitting.
-Any number of exams can be taken within one sitting, however:
-An exam in one subject can only be attempted once within a sitting, so a retake of a failed exam in the same sitting automatically starts the next sitting, even if the previous sitting has not ended yet. If a retake is taken in a different sitting from the first attempt, this does not start another sitting.

So I need a function to determine the first date, and group any exams within the next 10 days. I then need to do the same for the next set of exams once the first 10 days has elapsed. Furthermore, a retake (date in a separate column, so it's easy to reference to) starts a new sitting if taken within 10 days of the first attempt.

I'm familiar with Excel, but haven't had practise with formulas in a long time, so I don't really know where to start.

Let me know if this needs clarification; it is an awkward set of rules...

Thanks in advance!

[Pepe Le Mokko]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[NorthernLars]

Sorry about the delay.

I've attached a workbook showing an approximation of the before/after situation. Basically needs to group the dates into blocks of 10, with certain rules for overlapping blocks.

Let me know if there's still confusion (it is quite awkward...)

Thanks a bunch for the help!