Spreadsheet to evenly distribute required apprenticeship hours

[ollitrop]

Greetings!


I am seeking assistance in building an EXCEL file that will tell its user how many hours of a certain task must be completed per day to achieve a much higher total number of hours over the course of six months.


The US Department of Labor (DOL) has been sponsoring Apprenticeship Certifications to the US Navy for a long time. In the program a Sailor must complete X amount of hours in various tasks, after all hours have been documented as complete the DOL sends a Certificate of Apprenticeship Completion to the Sailor.


There are a few important rules that need to be followed when creating the formulas.

1. A Sailor can only claim eight (8) hours per day, ten (10) if they are deployed.
2. Hours cannot be claimed on Sundays
3. Hours can be claimed on Saturdays if the Sailor is deployed.
4. Some Sailors receive pre-registration credit, cutting the required hours in half.

Here is an example of an apprenticeship tract:

COMPUTER OPERATOR

Total required hours (in the skills listed below): 2000


[TABLE="class: grid, width: 600, align: center"]
<tbody>[TR]
[TD]Skill
[/TD]
[TD]Description[/TD]
[TD]Hours[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]COMPUTER OPERATION
Monitor and control electronic computer …..[/TD]
[TD]600[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]COMMUNICATION
Communicating with supervisors, peers …..[/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]ANALYZING DATA OR INFORMATION
Operate spreadsheet programs …..[/TD]
[TD]350[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]USE INTERNET AND INTRANET OR NETWORKING
Navigate or research or execute or …..[/TD]
[TD]250[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]PERIPHERAL EQUIPMENT
Operate computer peripheral equipment …..[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]MEDIA ASSISTANCE
Assist workers in classifying, cataloging …..[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]ERROR MONITORING
Operate and observe computer and peripheral equipment …..[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD]MAINTENANCE AND INSTRUCTIONS
Read instructions and follow maintenance schedules…..[/TD]
[TD]100[/TD]
[/TR]
</tbody>[/TABLE]





[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD]Skill[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hours Required[/TD]
[TD]600.0[/TD]
[TD]400.0[/TD]
[TD]350.0[/TD]
[TD]250.0[/TD]
[TD]100.0[/TD]
[TD]100.0[/TD]
[TD]100.0[/TD]
[TD]100.0[/TD]
[TD]2,000.0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pre-registration Credit[/TD]
[TD]300.0[/TD]
[TD]200.0[/TD]
[TD]175.0[/TD]
[TD]125.0[/TD]
[TD]50.0[/TD]
[TD]50.0[/TD]
[TD]50.0[/TD]
[TD]50.0[/TD]
[TD]1,000.0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hours Submitted in all Semiannual Reports[/TD]
[TD]0.0[/TD]
[TD]0.0[/TD]
[TD]0.0[/TD]
[TD]0.0[/TD]
[TD]0.0[/TD]
[TD]0.0[/TD]
[TD]0.0[/TD]
[TD]0.0[/TD]
[TD]0.0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hours Remaining[/TD]
[TD]300.0[/TD]
[TD]200.0[/TD]
[TD]175.0[/TD]
[TD]125.0[/TD]
[TD]50.0[/TD]
[TD]50.0[/TD]
[TD]50.0[/TD]
[TD]50.0[/TD]
[TD]1,000.0[/TD]
[/TR]
[TR]
[TD]Hours Remaining %[/TD]
[TD]50.0[/TD]
[TD]50.0[/TD]
[TD]50.0[/TD]
[TD]50.0[/TD]
[TD]50.0[/TD]
[TD]50.0[/TD]
[TD]50.0[/TD]
[TD]50.0[/TD]
[TD]50.0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hours Submitted in Weekly Logs*[/TD]
[TD]0.0[/TD]
[TD]0.0[/TD]
[TD]0.0[/TD]
[TD]0.0[/TD]
[TD]0.0[/TD]
[TD]0.0[/TD]
[TD]0.0[/TD]
[TD]0.0[/TD]
[TD]0.0[/TD]
[/TR]
</tbody>[/TABLE]


So in a nut shell, I want to create a file that I can type in the total hours required, the total hours in each category and have a formula tell me how I can evenly divide the hours over the course of a normal workday/week, while staying within the rules listed above.

 

[ollitrop]

No one?