Seafarers hours of rest - 24-hours roll period

Dear friends,

First of all sorry my first post will be a little bit longer.

I am working with an shipping company. As you probably know the Internationa Maritime Organization's (IMO) STCW and
Maritime Labour Convention 2006 require that seamans rest hours are to be very strictly planned, recorded and controlled.

Here are the IMO requirements:
-----
Minimum hours of rest shall not be less than:
(i) ten hours in any 24-hour period; and
(ii) 77 hours in any seven-day period.

Hours of rest may be divided into no more than two periods, one of which
shall be at least six hours in length, and the interval between consecutive periods of rest
shall not exceed 14 hours.
-----
Rest hours are to be recorded in the IMO's
I would like to help our Captains in planning, recording and control of rest hours for seamen.

I tried to prepare some form but I am faced with some problems (see attached sheet). I am able to
control rest hours time in 24-hours period (from 00.00 to 24.00) but not in ANY 24 hours. I also control
77 hours in 7 days.

My problem is how to control the requirements of maximum two rest periods, one of which shall be at least six hours in lenght
and the interval between consecutive rest periods which shall not exceed 14 hours.

Using conditional formating in the atached sheet the rest period less than 10 and 77 hours will be displayed in red.
Top part of form attached is for the first month on board and lower part for the second and subsequent months (due to
77 hours requirements)

Could you help me to solve my problems on an easy way in order to be useful and easy to use fo our Captains.

Brgds