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

  1. What is the formula I should use when trying to find the total number of entries for a specific year? See Below:
    First Name Rejection Date
    Laura 12/17/2013
    Veronica 12/19/2013
    Jonathan 1/23/2014
    Erin 2/1/2014
    Lucinda 2/24/2015
    Rosa 3/14/2015
    Bradley 7/2/2016
    Maria 7/30/2016
    David 8/7/2017
    Cheryl 8/7/2017
    Paige 8/7/2017

    I am trying to figure out how many people were "rejected" in 2013, 2014, 2015, etc. without having to create numerous columns, etc.

    Thank you for your help.

    1. Hi, Megan,

      if you have an additional status column saying who was rejected, you can use COUNTIFS function. Please read this article to learn more about the COUNTIFS syntax and to be able to use it.

  2. I want to get the length of service of my manpower. Is there a formula for this in excel? So that I could identify the number of years of my manpower. Thank you

  3. Thanks, this helped me a lot.
    Regards,
    Js

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

  5. Hi
    Help!
    How can I exit 35 months in Excel?

    1/1/1988 TO 31/121988
    1/1/1989 TO 31/12/1989
    1/1/1990 TO 30/11/1990

  6. Hi
    Help!
    aHow can I exit 35 months in Excel?

    1/1/1988 TO 31/121988
    1/1/1989 TO 31/12/1989
    1/1/1990 TO 30/11/1990

  7. Formula for calculating when a person will be 17 years and 6 months old?

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

  9. Hi
    Help!
    I need a formula that will calculate the number of days in the year over a period of time, not from January to December, that will take into account leap years. For example a date of contract starts 01/02/2016 and ends 31/01/2017 = 366 days. But, a contract that starts 01/04/2016 and ends 31/03/2017 is 365 days. How do I create a formula that will take this into consideration across several years and lines of data?
    Thanks :)

  10. I want to calculate Date of Birth of 25 years 4 months 12 days to date/month/year(ex.01/01/2017) format, kindly help, thanks.

  11. 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 ?

  12. Hi
    Could you please help me to collect data from age.

    I used =DATEDIF(actualdata,NOW(),"y") and I got everyone age.

    Now I want to see only:
    - over 65 years old -
    and
    - under 16-

    I am trying quickly recognise who is under 16 and over 65 from my list and how many people is under 16 and how many people is over 65.

    I be thankful if you show me the way how to this.

    Thank you

  13. How do I take a cell with a date in it and convert it to years using a specific date not TODAY as a date. Example I need 06/01/1996 to show me how many years they have been working for us from hired date through 12.31.2016.

  14. If I get to know the year.. I can 1st Jan and their birth year for all the employees Those who have not shared their date of birth

  15. I have only a age for 300 employees, like 30 yers 20 yers and 40 yrs etc.. then how do I calculate their birth year in excel..

    It will be a great help if you could provide me a formula on this..

  16. Hi,

    My query quite simple..

    I have only a age for 300 employees, like 30 yes 20 yes and 40 yes then how do I calculate their birth year in excel..

    It will be a great help if you could provide me a formula on this..

  17. Thank you!

  18. Hi, I kept having shortage of year. For example:
    Start 15/12/2014
    Expiration 14/12/2016
    Period 23 months

    Where I should get 24 months.

    Please assist.

  19. Hi Svetlana, I have a column that is using the format m/d/yyyy h:mm. How do I isolate the year?

  20. Re: Calculating leap years...

    =DAY(DATE(YEAR(A2),3,1)-1)=29

    ...can be simplified to

    =DAY(DATE(YEAR(A2),3,0))=29

    No need to subtract one, or use EOMONTH, as the DATE function already treats the Year/Month/Day arguments as offsets, not absolute values.

  21. Hi!
    Thanks for the amazing post; it helped a lot.

    I just have one question I'm trying to calculate following information which I got using DATEDIF

    0 Years, 8 Months, 0 Days
    4 Years, 3 Months, 0 Days
    1 Years, 9 Months, 0 Days
    1 Years, 1 Months, 0 Days

    Thanks in advance

  22. Hello,
    I want to know the formula how can i get the remaining time of Service if the retirement total period is 62 years

  23. Ple help one boy asking ple help name: Sanjay date of birth:6:12:2000,now what is age now ple help me

  24. I need to find out what year someone was 65 years old
    So I have a column with DOB DD/MM/YYY and I need a calculation to tell me YYYY they turned 65.

  25. Thanks very much for useful infornation

  26. Dear Madam

    In my case the employee of company are working earlier than 2000, but a policy was introduced in 2009 by company in which employee who is working earlier than 2000 can get his 1st salary increment of 15% of basic pay after 2 years i.e. 2011 and the next increment of 10% of basic pay in the third year i.e. 2012.
    Please help me I stuck hard in this position

    Regards

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

  28. I have a spreadsheet that lists the employees start date. I am trying to separate into bands- 0-6M, 6M-1Y, 1Y-3Y,3Y-5Y,5Y+. Any ideas? I can add columns, then sort by, but this is a large organization, and I'm trying to automate as much as possible.

    Thanks, everyone!

  29. Hi. I'd like to display the difference between two reading test ages as a negative if a pupil's results have deteriorated and as a positive if they have made progress.

    For example, the positive difference between: 06 Year(s), 05 Month(s) and 06 Year(s), 11 Month(s) (after the next test).

    Thanks in advance! Michelle

  30. 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"

  31. Hello,

    How do I find the effective and expiration dates using a date
    For Ex: I have a loss date as 6/2/2015 and my effective date is 10/1/2015 and expiration dated is 10/1/2016

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

  33. Suppose Date of Birth of a person is 07/12/1962. I want to calculate on what date he completes his 55 years of age.

  34. Hi, can you please help me ?

    Let's say I have 11 tables with different amount.
    11
    4466
    568
    789
    356
    567 suppose if this are days, how do we calculate the avarage in years ?

  35. Hi there,

    I am trying to calculate age from year of birth only (not DOB). It seems to work using simple subtraction however when I click and drag the bottom right corner of the highlighted area it just copies and pastes the contents of the cell rather than using the subtraction formula for each cell in the age column. How do I correct this as I have a lot of data?

    Best wishes
    Blaithin

  36. CAN I USE THE FORMULA =YEARFRAC(N141,O141,1)*12 TO CONVERT MONTHS AND DAYS TO DECIMAL?
    EXAMPLE
    5/14/2015 12/31/2015 7 months, 17 days 7.59

  37. Dear madam!
    i need your help

    if i type the year 2013 then how count in number 3 year

  38. Good day,
    I have a worksheet with drop down list for day, month and year in separate cells. I want a formula to calculate and display the age in the next cell. I tried a lot, but it is not calculating from the drop down list. Please help me.

  39. How can I know 58 year over age from date of birth

  40. Hi I need to know how to convert something like, 48 years and 2 months into years. Would really appreciate an answer, thanks.

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

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

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

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

  45. 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"

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

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

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

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

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

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 :)