Thanks:  0
Likes:  0

# Thread: Assign a number value to a Date in Excel

1. ## Assign a number value to a Date in Excel

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

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

2. ## Re: Assign a number value to a Date in Excel

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

Excel 2010
ABCDEF
120-Dec-5200
220-Dec-621100
320-Dec-70221-Jan-601
431-Dec-89331-Jan-702
514-Dec-17001-Jan-803
61-Jan-900
7

2c

Worksheet Formulas
CellFormula
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()

3. ## Re: Assign a number value to a Date in Excel

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