Excel function to perform compliance check on Seafarers Hours Record

I am trying to create an excel tool that helps me check if a Seafarers Hours Of Rest record complies with the minimum requirements of the Law.

By law all seafarers are required to keep a record of their HOR in a monthly sheet similar to the test one I have attached (Usually done manual with a blank sheet and pen)

The minimum criteria that must be met to comply is:

There must be at least 10h of rest in ANY 24h period,
There must be at least 77h of rest in ANY 7 day period,
The 10h rest may be split into no more than 2 periods of rest, one of which must be at least 6h long, (To help with this i should explain that there can be more than just 2 rest periods, as long as the "10h's" is not divided into more than 2)
No more than 14h between consecutive rest periods.

From the test sheet I have attached you will see that it is simple to "Check" and highlight (Using conditional formatting) >10h rest on specific days and similarly for >77h over 7 days. The next hurdle for me is performing a check on this data to see that out of the rest period(s) for that day, there is at least 2 that make up 10h, were one of which is at least 6h (or: 7h+3h, 8h+2h, etc). My initial thoughts on this is that writing a function in VB that performs the check on a given range of 48 cells (48 half hour periods) and returns a true or false for compliance? This function could then be called upon in a formula that runs through each 24h period on the sheet. My knowledge of VB is basic at best though..

The next hurdle after that is applying this check to ANY 24h period (this is when my head gets rather sore....). For example: from 2330 today to 2330 tomorrow, 2300 to 2300, etc, etc.


Any thoughts/help with this would be much appreciated.

Sam