Automate a mundane task

[kat73]

Hello all,

Hoping you can provide some help to a monthly task...

I have a table with a list of individuals with a last date of attendance in one column followed by the 12 months. I have provided an example below:


[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Actual Date[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[/TR]
[TR]
[TD]X1[/TD]
[TD]30/08/2018[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]X2[/TD]
[TD]Cont.[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]X3[/TD]
[TD]25/01/2018[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]X4[/TD]
[TD]Cont.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


What would be extremely helpful, would be to automate the monthly process (instead of me having to manually enter 1 for the last month they attended)
Some individuals do not have an actual end date (so is blank) as they are still attending so they will need a 1 going until Dec
Any guidance or ideas would be extremely helpful!

Kind regards,
Kat

 

[AlanY]

try

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Actual Date	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct
2	X1	30/08/2018	1	1	1	1	1	1	1	1	0	0
3	X2	Cont.	1	1	1	1	1	1	1	1	1	1
4	X3	25/01/2018	1	0	0	0	0	0	0	0	0	0
5	X4	Cont.	1	1	1	1	1	1	1	1	1	1
Sheet2

Cell Formulas
Range	Formula
C2	=IF(ISNUMBER($B2),IF(MONTH($B2)>=MONTH(1&"/"&C$1),1,0),1)