Rest Hours Deck

1. Initial data and task:

The application contains the approved form of the company REST HRS DECK (crew of 25 people, 12 months). The file already has old VBA modules, but they work with errors.

TASK: Write clean, stable VBA code (macros) and link it with formulas for full automation of the table.

STRICT PROHIBITION: The visual part, structure of sheets, column layout (including column C), header, and company logos must not be changed. This is the official form of the company, it must remain in its original form. The format of the final file is .xlsm.

2. Automation requirements (VBA):

When the value "Year" on the Crew list (or title) sheet changes, the macro must automatically:

* Update all dates in calendars on all sheets, correctly handling leap years (February 29).

* Clear working areas: automatically delete all previously entered user rest hours (for example E10:AZ40) for the previous year and column C (for example C10:C40), while preserving all hidden calculation formulas and formatting.

* Data carry-over: implement a mechanism for automatic or semi-automatic (via prompt/button) transfer of data for the last 6 days of December of the previous year, so that STCW rules are correctly calculated in the first days of the new January.

3. Calculation logic (MLC/STCW Rules):

Using macros and/or formulas, implement strict rule checks:

* Minimum of 10 hours of rest in any 24-hour period.

* Minimum of 77 hours of rest in any 7-day period (with seamless transition between months).

* Division of rest hours into no more than 2 periods, one of which is at least 6 hours continuously.

* The interval between rest periods does not exceed 14 hours.

4. Visualization:

If any of the rules are violated, the cell with the total sum (or a special indicator) should automatically be colored red.

5. CRITICAL REQUIREMENT: Compatibility and Language:

The file will operate on ship computers with fully English Windows and English Microsoft Office (versions from 2010 to 2016).

* The VBA code and any used formulas must be strictly compatible with Excel 2010.

* Any text values within the code and formulas (for example, macro pop-ups, status names) must be strictly in English. No Cyrillic behind the scenes!

* Date formats in the code and formulas should be specified considering English regional settings (for example, "DD/MM/YYYY").

The original file will be sent to the selected performer personally, as the ship's Wi-Fi blocks its upload to the site