Assign a number value to a Date in Excel

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Assign a number value to a Date in Excel

  1. #1
    New Member
    Join Date
    Dec 2017
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,301
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #3
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    19,217
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Assign a number value to a Date in Excel

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

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com