Comments on: YEAR function convert date to year in Excel

This tutorial explains the syntax and uses of the Excel YEAR function and provides formula examples to extract year from date, convert date to month and year, calculate age from the date of birth and determine leap years. Continue reading →

Comments page 2. Total comments: 74

  1. Hi
    Could you please help me to change the year format.
    how to change 10-mar-40 to 10-mar-2040 in excel

    1. Hi,

      After you entered the date (make sure to enter the complete year - 2040, not just 40), select the cell and press Ctrl+1 to open the Format Cells window. There, go to Date tab and find the following data type: 14-Mar-2012. Select it and press OK.

      Hope it helps!

  2. using the formula =DATEDIF(A2,NOW(),"y") gets me the age for A2. how do I put that formula into 300 rows with out having to change the formula each it each time i.e:
    =DATEDIF(A3,NOW(),"y")
    =DATEDIF(A4,NOW(),"y")
    =DATEDIF(A5,NOW(),"y")
    etc.

    Thanks

  3. trying to figure out the date. if age 18 or more at time of exam, then purge date is 7 years from date of exam.

    If under 18 on date of exam, then purge date is 7 years from 18th birthday.

    I Have one student
    DOB 10/2/1985
    Exam 03/07/04

    Second Student
    11/01/1960
    06/12/78

    and about 700 other students. how do I find the purge date?

    1. DOB Exam Date Age@exam Purge date
      11/01/60 06/12/1978 17 ?

  4. Trying to calculate the age of a student at the time they participated in a program. I have the date of birth and I have the year of the program in which they participated. Some students participated in multiple programs in various year.

    Example:

    Name Date of Birth Program Year
    Larry Johnson 3/21/1982 College Scholarship 1999
    Tyler Scott 12/01/1986 STEM Camp 2000
    Tyler Scott 12/01/1986 College Scholarship 2001

    Is there one formula I can use to calculate the estimated age at the time the student participated in the program?

    1. Hello Crystal,

      Supposing, the DOF is in column B, and the year of the participation in the program is in column D, you can use the following formula:

      =DATEDIF(B2,DATE(D2, 1, 1),"y")

      Because the exact date of the program is not defined, you can supply the date and month that you think are the most appropriate, like January 1 in the above example: DATE(D2, 1, 1)

  5. Hi dear,
    How to calculate age from this format that
    1day in cell a1, January in cell a2, and 1992 in cell a3 plz solve this.

    1. Hello Rahman,

      Please use
      =DATEDIF(DATE(A3,MONTH(DATEVALUE(A2&"1")),A1),TODAY(),"y") & " Years, " & DATEDIF(DATE(A3,MONTH(DATEVALUE(A2&"1")),A1),TODAY(),"ym") & " Months, " & DATEDIF(DATE(A3,MONTH(DATEVALUE(A2&"1")),A1),TODAY(),"md") & " Days"

  6. Please comment. Its urgent. I think Svetlana Cheusheva can solve it.

    1. Hello Ramteke,

      If my understanding of the task is correct, you just need to add 55 years to the date of birth.

      If so, please use this formula, where A2 is the date of birth:
      =DATE(YEAR(A2) + 55, MONTH(A2), DAY(A2))

  7. Hi I have a list of excel sheets where patients ages are changed to Months (for e.g. 24, 36 mths etc). How do I calculate the average age of the list? How do I find the median and range of ages? This is a list of 375 patients. Please advise. Thank you.

    1. Hello Julieanna,

      To calculate the average age and the median of ages, you can use the AVERAGE and MEDIAN function, respectively. For example:

      =AVERAGE(A2:A376)
      =MEDIAN(A2:A376)

  8. Hi Svetlana,

    can you help please?

    I need formula for giving Financial year to a specific date.
    (eg. if the date is 31-03-2016 formula should say "2015-16" like that if date is 31-03-2015 answer should be "2014-15"

    Hope you will answer my query.

    Thanks in advance.

    1. Hello Svetlana,

      Can you please help me our with the solution formula?

      Thanks in advance.

      1. Hi Kunal,

        Try the following formula, where A1 is the source date:
        =YEAR(A1)-1&"-"&TEXT(A1, "yy")

        1. Thank you

        2. Fantastic.
          Thank you very much!

  9. That gives a True or False, where I definitely need to see the age in years and months. I'll try something else, thanks for the help though.

    1. Hi Becky,

      Your initial request was "use conditional formatting to highlight where a student is under 18 years of age", and it it exactly what the formula does. To put it differently:

      - If you enter the formula in a cell, it returns true / false.

      - If you create a conditional formatting rule based on the above formula, it will highlight students under 18 years of age. If you need the detailed instructions on how to create a formula-based rule, please check out the following tutorial: Excel formulas for conditional formatting

      To see the age in years and months, you can keep using your original formula.

  10. Hi Ablebits, can you help please?
    I have this formula working:
    =DATEDIF(T78,I78,"Y")&"y"&DATEDIF(T78,I78,"YM") &"m"
    where T78 is a date of birth and I78 is a specified date (start of course). It shows me the age of a student in years and months.
    I'm trying to use conditional formatting to highlight where a student is under 18 years of age, but coming unstuck as it doesn't like the "y" and "m" text in the formula.
    Many thanks for any advice.

    1. Hi Becky,

      "y" and "m" turn the output into a text string and that is why Excel cannot compare it with a number.

      Try creating a conditional formatting rule with the following formula (where 78 is the topmost row with dates):
      =DATEDIF($T78,$I78,"Y")<18

  11. Would appreciate if you give provide a formula for below scenario. Thank you in advance.

    Tenure shall be computed from the hiring date of the employee up to the effectivity date of separation
    A fraction of six months shall be considered as one whole year and less than 6 months shall be considered in its exact proportion to one calendar year
    Fifteen (15) to Thrity (30) days shall be considered as one month service while eight (8) to fourteen (14) days shall be condiered as half-month service.

    Sample: Employee hiring date is July 25, 2000
    a. Separated effective Jan 15, 2016, his tenure will be 16 years
    b. Separated effective Jan 2, 2016, his tenure will be 15.46

    1. Hello, Maeg,

      Please see the formula below:
      =DATEDIF(B3,C3,"y")+IF(DATEDIF(B3,C3,"ym")>=6,1+IF(DATEDIF(B3,C3,"md")>=15,1,IF(DATEDIF(B3,C3,"md")>=8,0.5,0)),IF(DATEDIF(B3,C3,"md")>=15,1,IF(DATEDIF(B3,C3,"md")>=8,0.5,0)))

  12. Sir

    I want to find out the age (years, months, days) on a particular date by using date of birth.

    for example date of birth is 01/04/1984
    I want to know my age on 25/08/2015 in years months and days by using MS Excel.

    please help me.

    1. Supposing that your date of birth is in A1 and the other date in B1, you can use the following formula:

      =DATEDIF(A1, B1,"Y") & " Years, " & DATEDIF(A1, B1,"YM") & " Months, " & DATEDIF(A1, B1,"MD") & " Days"

      1. When I did this formula I get NAME?
        I cannot get the bd and age to work

      2. This formula not work in my excel2013.

        Name Date of Birth Today Age
        Md.Biplab Hosen 02/11/2010 20/05/2016
        Md. Azizul Hakim 12/03/2014 20/05/2016
        Md. Badsha Miah 06/06/2014 20/05/2016
        Md. Shariful Islam 07/06/2014 20/05/2016
        Ripon Chandra Basak 11/06/2014 20/05/2016

        So give me result with formula.

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)