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 5. Total comments: 151

  1. I need to calculate years of service from the start date to 12/31 of the current year, sort of like the today function, but it will be a set date of 12/31 and the year will change every year.

  2. I want to know my age with month and days by using date of birth, How to and which formula can I use?

  3. Is there a way to do calculate the average of a column that has the following information formatting: 2 Year(s), 6 Month(s), 19 Day(s) ... or is there a way to calculate that information into date format using date-to-age function.

    Thanks so much.

  4. 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)

  5. Hi Anurag,

    For removing points i think you can use find & replace formula....

    You can use key (Ctrl + F)

    you have to do the following :-

    1. Select Date
    2. Use Ctrl + F
    3. Find What : . (write dot in this tab)
    4. Replace with : (leave this tab blank)

    and then click "Replace" or for multiple selection you can use "Replace All"

  6. how to remove point in between date of birth for eg-12.09.2011.

  7. 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!

  8. in certificate it is written age as 19 years 01 month and 11 days as on 01 march 1983.
    What will be the date of Birth

  9. How to convert 2.3 in to 2 years 3 months

  10. hi all
    i want to check age by on adding year of birth
    someone can help me send the formula on my email.

  11. Figured it out- put in the desired date next to date of birth and took the absolute value:

    =ABS((D2-C2)/365.25)

    The .25 takes care of leap year (occurs every 4 years)

  12. How can I convert date of birth to age if not using 'Today'? I want to find the age as of January 1 of a particular year, and when I add that value in my equation I get a circular reference warning and an incorrect answer.

    =DATEDIF(D2,E2,"Y")

  13. To calculate years of service, is it ok to use 12/31/2016 as the date instead of today's date? I'm using the formula: YEARFRAC(A1,B1,1). A1 being the 12/31/16 and b1 is the hire date.

    Thanks,

  14. Dear admin,
    I would like to convert 24 years 6 month into 25 year by rounding it. What is the correct formula for this.I wl b glad if u replay me promptly. Thank you very much.

  15. hi Need to set Remark on asset aging e.g.

    if asset is <4.00 = < 4 Year

  16. Hi ! This blog is very useful ! I use the DATEDIF function to calculate the age of children each month (in column the months for the next 10 years, and in raw the children). Now, I need to use a color for the cell where the child reaches "3 years 0 months 0 days", another color for the where he reaches 4, another color for the cell where he reaches 5... and so on.
    I try to use the conditional function but it doesn't work. I wonder if it is because of the format (X years, X months, X days). The format is in "standard" and I changed to "number" but it doesn't work...
    So 2 questions :
    - how to highlight (or use a specific format) when I use the datedif function ?
    - how to highlight the cell where a child reaches 3 yo but not the other cells where is over 3 ?
    I hope you can help me...
    Olivier

  17. 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.

  18. 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

  19. 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)))

  20. Thanks a lot, very useful for me

  21. 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



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)