DA Form 6

[SavageMind]

Hi all this is my first post.

I am creating a DA Form 6 wich is used to determine duty cycles. The first two colums A&B are for rank and name by row. the next 40 colums are to put numbers and symbols to determine who's turn it is. the first person will have a / then will count down from the number of names on the worksheet. so if there are 30 names, the first person will have / in colum C and will couunt down from 29 until it reaches 1 then it will have another / until it reaches the end of the 40 colums. The second name will have a 1 in colum C, a / in colum D and then it will count down. I would like to automate this document so it will autofill these cells, will allow exceptions and caculate for exceptions. The proper filing out of this document is covered in AR 220-45 and has an example wich can be googled. My Excel knowledge is limited any help would be great thanks.

SavageMind

 

[dakota727]

Not sure I understand what you are trying to do.

Are you trying to make something like the following:

Duty Roster.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3	DUTYROSTER		NATUREOFDUTY
4
5	GRADE	NAME	Month
6			Day	1	2	3	4	5	6	7	8	9	10
7		Name1		/	15	14	13	12	11	10	9	8	7
8		Name2		1	/	15	14	13	12	11	10	9	8
9		Name3		2	1	/	15	14	13	12	11	10	9
10		Name4		3	2	1	/	15	14	13	12	11	10
11		Name5		4	3	2	1	/	15	14	13	12	11
12		Name6		5	4	3	2	1	/	15	14	13	12
Sheet1

 

[dakota727]

If so I used =COUNTA(B7:B47)-1 in cell AR6 to count the number of people and then hand entered the row for the first person then used
=IF($B8="","",IF(D7="/",1,IF(D7=$AR$6,"/",D7+1)))
in cell d8 and copied down and across the 40 by 40 grid. Not sure if it gets you any closer but you may want to describe opporationally how it should work and what types of exceptions you encounter.

 

[StephenGKorea]

If so I used =COUNTA(B7:B47)-1 in cell AR6 to count the number of people and then hand entered the row for the first person then used
=IF($B8="","",IF(D7="/",1,IF(D7=$AR$6,"/",D7+1)))
in cell d8 and copied down and across the 40 by 40 grid. Not sure if it gets you any closer but you may want to describe opporationally how it should work and what types of exceptions you encounter.

**sidenote** SavageMind you need to civilianize some of your terms

Your example is not too far off of what SavageMind is saying. The title got my attention because I am in the Army also and knew exactly what he is looking to do. I attempted to try it once a long time ago, but my head felt like it was going to explode.

A DA Form 6 determines whos turn is it to do a particular duty or task.

You start with a list of names in Rank and Alphabetical order (COlumns A&B) and then in Column C you start with giving the first person (Cell C1) with the Number 1 then you add a number all the way down (C2=2, C3=3) etc.

The Person with the Highest Number for that day/shift (Column C) has to perform that task.

Day/Shift 2 (Column D) Everyone's Number increases by one. The largest Number again pulls detail.

On The Form, the person that is pulling the task/Duty for that day gets an X in that square.

Here is the fun part, when an individual goes to School, Vacation or gets a temporary exemption to the roster because of something outstanding, their number is suspended until they return.

I know this makes it as clear as mud, I can try to explain if need be.

Stephen

 

[dakota727]

OK if someone is exempted from the duty roster can that be tracked in columns off the printed page?

I guess I need more info on how for you are trying to get the form to go. Is the goal to automatically fill out the grid and then manually adjust it through the month or is it to capture a lot more info and try and cover every different option?

How often is this thing printed and changes made?

As a start you could take my previous example with all the formulas and the copy the 40x40 grid and past special the values on it self. This would remove the formulas and let you make changes to a single cell without then having the other ones change.

If you are going to try and make a big project you will probably have better luck getting started and asking smaller more direct questions to work towards your desired finished form.

 

[SavageMind]

Reply

what i would like to do is have it recognize values like L for leave, S for school and make it so if a cell has L or S that the number sequence is only continued after the last L or S. I would also like to put E for exempt so that person is not counted at all. I apologize if i'm not civilianized enough..lol. 15 yrs Infantry dumbs you down a bit...JK. thanks for the help. I had to mod the formula a bit Dakota, but it did work. The Form is reltively simple but very intimidating to some people. Part of the problem is that weekends start a new sequence. So I have chosen to keep the current sequencing but make seperate DA6 for weekends only. I know alot of 1SG's that do it that way.
Thanks

 

[SavageMind]

WORKS KINDA

well it works but it fills in 1 line past the last name so it actually messes up to roster when there is always a blank line that has duty. but it does get close...lol thanks

 

[dakota727]

Let me ask you this then. How would you use the form? Would your start with it blank then fill in the L, S and E for varying people and dates and then take each day and generate the incrementing numbers.

For example if my form has 16 people total, and varian people are exempt for for varying reasons on varying days, what should the form look like.

Maybe you could post a small example done by hand and it would be helpful.

 

[StephenGKorea]

OK if someone is exempted from the duty roster can that be tracked in columns off the printed page?

Yes and No, from a technical aspect you can, but it still has to be marked on the final sheet

I guess I need more info on how for you are trying to get the form to go. Is the goal to automatically fill out the grid and then manually adjust it through the month or is it to capture a lot more info and try and cover every different option?

The goal is to have the grid automatically fill out based on the number sequence on the first column and having the vacation/exemptions/schools already marked out

How often is this thing printed and changes made?

At a minimum 30 days or 30 shifts. Changes are made occationally when something comes up, like a Soldier goes on Emergency Leave.

As a start you could take my previous example with all the formulas and the copy the 40x40 grid and past special the values on it self. This would remove the formulas and let you make changes to a single cell without then having the other ones change.

If you are going to try and make a big project you will probably have better luck getting started and asking smaller more direct questions to work towards your desired finished form.

First Thing would be is how to determine what is the biggest number in a column, and then on the next Column put a D.

2nd would be when increasing the number, if there is something besides a D or a number look to the left until you find a number and use that as the baed for the increase.

 

[StephenGKorea]

Here is an example DA Form 6:

You will notice how the largest number has duty the next day (In case of a tie, first one listed gets the duty)

Then Number is frozen when SGT Merrick went on pass from the 7th to 10th and SPC Jackson went to School on 6th-7th.

The persons number starts all over the day after Duty.

DAForm6.htm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1			5	6	7	8	9	10	11	12	13	14	15	16	17	18
2	SGT	Smith	5	6	7	8	9	10	11	12	13	14	15	D	1	2
3	SGT	Jones	6	7	8	9	10	11	12	13	14	15	D	1	2	3
4	SGT	Johnson	7	8	9	10	11	12	13	14	15	D	1	2	3	4
5	SGT	Merrick	4	5	P	P	P	P	6	7	8	9	10	11	D	1
6	SGT	Schroeder	9	10	11	12	13	14	15	16	D	1	2	3	4	5
7	SGT	Noel	10	11	12	13	14	15	16	D	1	2	3	4	5	6
8	SGT	Sledge	11	12	13	14	15	16	D	1	2	3	4	5	6	7
9	SGT	Brown	12	13	14	15	D	1	2	3	4	5	6	7	8	9
10	SGT	White	13	14	15	D	1	2	3	4	5	6	7	8	9	10
11	SGT	Black	14	15	D	1	2	3	4	5	6	7	8	9	10	11
12	SPC	Houston	15	D	1	2	3	4	5	6	7	8	9	10	11	12
13	SPC	Jersey	D	1	2	3	4	5	6	7	8	9	10	11	12	D
14	SPC	Jackson	14	S	S	15	16	D	1	2	3	4	5	6	7	8
Sheet1