Assign a number value to a Date in Excel

[samwong]

Hi

How should I use IF or IFS function to deal with the situation below. Many thanks!

I would to assign the number "1" for the age group of 1960s, "2" for the 1970s and "3" for the 1980s.

Current data:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Age Group[/TD]
[/TR]
[TR]
[TD]1/1/1960[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31/12/1969[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/1/1970[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31/12/1979[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/1/1980[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31/12/1989[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Proposed result:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Age Group[/TD]
[/TR]
[TR]
[TD]1/1/1960[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]31/12/1969[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1/1/1970[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]31/12/1979[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1/1/1980[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]31/12/1989[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[Dave Patton]

2 formula alternatives that you can try.
Copy or fill the formulas down.

Excel 2010
	A	B	C	D	E	F
1	20-Dec-52	0	0
2	20-Dec-62	1	1		0	0
3	20-Dec-70	2	2		1-Jan-60	1
4	31-Dec-89	3	3		1-Jan-70	2
5	14-Dec-17	0	0		1-Jan-80	3
6					1-Jan-90	0
7
2c

Cell Formulas
Range	Formula
B1	=LOOKUP(A1,$E$2:$F$6)
C1	=IF(A1>=32874,0,IF(A1>=29221,3,IF(A1>=25569,2,IF(A1>=21916,1,0))))
A5	=TODAY()

 

[Scott Huish]

Another possibility:
=INT(TEXT(A2,"y")/10)-5