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

Could I just check, in your example, day 1 does the person rest from 0:00 to 09:30 then work for 30minutes followed by a rest of 30 mins at 10am? Finally resting for another hour at 17:30?

Hi,

Negative. I put randomly rest time just to check whether criteria of minimum 10 hours of rest works.
It is not actual rest hours but randomly distributed some 'x's on selected days just to see whether
minimum 10 hours / 77 hours criteria works.

Any help??

Originally Posted by shg

It's going to be hard to do this in your table format without VBA. If the data input were in a single column (1440 rows for 30 days), a formula-based solution would be much easier to evaluate across all 24-hour periods.

Hi,
I must keep table format, as it is predifined and internationally recommended.
Sorry, I am not familiar with VBA.
Are there any solution to create additional sheets which could remain hiden?

Thanks

Are there any solution for this case?
Thanks in advance, we are very happy to hear from you all !

I would like to know how far you have come with this issue? I have been able to find the length of each rest period and the distance between them, but as you mentioned; this need to be done in ANY 24 hour period, not just between 00:01 and 23:59.

Work and rest in any 24h, can we count in any hour so any 24h can be show easily.

I think the solution will be one of the checking methods, making this table is not easy and it's not the same for every people when the time changes ahead and aback.

Good to see the completion, thanks and best regard. Good luck.