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:

Date	Age Group
1/1/1960
31/12/1969
1/1/1970
31/12/1979
1/1/1980
31/12/1989

<tbody>
</tbody>

Proposed result:

Date	Age Group
1/1/1960	1
31/12/1969	1
1/1/1970	2
31/12/1979	2
1/1/1980	3
31/12/1989	3

<tbody>
</tbody>

 

[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