How to convert date to year in Excel & calculate age from date of birth

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.

In a few recent posts, we have explored different ways to calculate dates and times in Excel and learned a variety of useful functions such as WEEKDAY, DAY, MONTH and WEEKNUM. Today, we are going to focus on a bigger time unit and talk about calculating years in your Excel worksheets.

In this tutorial, you will learn:

YEAR function in Excel

The YEAR function returns a four-digit year corresponding to the specified date, as an integer from 1900 to 9999.

The syntax of the Excel YEAR function is as simple as it could possibly be:


Where serial_number is any valid date of the year you want to find. In your Excel YEAR formulas, you can specify dates in several ways:

  • Using the DATE For example, the following formula returns the year for 28 April, 2015:
  • As a serial number representing the date (for more information on how dates are stored in Excel, please see Excel date format). The 28 day of April, 2015 is stored as 42122, so you can enter this number directly in the formula:

    Although you can enter a date as a serial numbers, this method is not recommended because date numbering may vary across different systems.

  • As a cell reference, e.g. =YEAR(A1)
  • As a result of some other formula. For example, you can use the TODAY() function to extract the year from the current date:

Simple YEAR formulas can even understand dates entered as text, like =YEAR("28-Apr-2015"). However, Microsoft does not guarantee correct results when a date is supplied as a text value.

The following screenshot demonstrates all of the above YEAR formulas in action, all returning 2015 as you might expect :)
Using the YEAR function in Excel

How to convert date to year in Excel

When you work with date information in Excel, your worksheets usually display full dates, including month, day and year. However, for major milestones and important events such as product launches or asset acquisitions, you may want to view only the year without re-entering or modifying the original data. Below, you will find 3 quick ways to do this.

Example 1. Extract a year from date using the YEAR function

In fact, you already know how to use the YEAR function in Excel to convert a date to a year. The screenshot above demonstrates a bunch of formulas, and you can see a few more examples in the screenshot below. Notice that the YEAR function perfectly understands dates in all possible formats:
Excel YEAR formula examples

Example 2. Convert date to month and year in Excel

To convert a given date to year and month, you can use the TEXT function to extract each unit individually, and then concatenate those functions within one formula.

In the TEXT function, you can use different codes for months and years, such as:

  • "mmm" - abbreviated months names, as Jan - Dec.
  • "mmmm" - full month names, as January - December.
  • "yy" - 2-digit years
  • "yyyy" - 4-digit years

To make the output better readable, you can separate the codes with a comma, hyphen or any other character, like in the following Date to Month and Year formulas:

=TEXT(B2, "mmmm") & ", " & TEXT(B2, "yyyy")


=TEXT(B2, "mmm") & "-" & TEXT(B2, "yy")

Where B2 is a cell containing a date.
TEXT formulas to convert date to month and year in Excel

Example 3. Display a date as a year

If it does not really matter how the dates are stored in your workbook, you can get Excel to show only the years without changing the original dates. In other words, you can have full dates stored in cells, but only the years displayed.

In this case, no formula is needed. You just open the Format Cells dialog by pressing Ctrl + 1, select the Custom category on the Number tab, and enter one of the below codes in the Type box:

  • yy - to display 2-digit years, as 00 - 99.
  • yyyy - to display 4-digit years, as 1900 - 9999.

Displaying a date as a year

Please remember that this method does not change the original date, it only changes the way the date is displayed in your worksheet. If you refer to such cells in your formulas, Microsoft Excel will perform date calculations rather than year calculations.

You can find more details about changing the date format in this tutorial: How to change date format in Excel.

How to calculate age from date of birth in Excel

There are several ways to calculate age form date of birth in Excel - using DATEDIF, YEARFRAC or INT function in combination with TODAY(). The TODAY function supplies the date to calculate age at, ensuring that your formula will always return the correct age.

Calculate age from date of birth (DOB) in years

The traditional way to calculate a person's age in years is to subtract the birth date from the current date. This approach works fine in everyday life, but an analogous Excel age calculation formula is not perfectly true:


The first part of the formula (TODAY()-B2) calculates the difference is days, and you divide it by 365 to get the number of years. In most cases, the result of this equation is a decimal number, and you have the INT function round it down to the nearest integer.

Assuming the date of birth is in cell B2, the complete formula goes as follows:

A formula to convert a date of birth to age

As mentioned above, this age calculation formula is not always flawless, and here's why. Every 4th year is a leap year that contains 366 days, whereas the formula divides the number of days by 365. So, if someone was born on February 29 and today is February 28, this age formula will make a person one day older.

Dividing by 365.25 instead of 365 is not impeccable either, for example, when calculating the age of a child who hasn't yet lived through a leap year.

Given the above, you'd better save this way of calculating age for normal life, and use one of the following formulas to calculate age from date of birth in Excel.



The detailed explanation of the above formulas is provided in How to calculate age in Excel. And the following screenshot demonstrates a real-life age calculation formula in action:

=DATEDIF(B2, TODAY(), "y")
A formula to calculate age from date of birth in years

Calculating exact age from date of birth (in years, month and days)

To calculate an exact age in years, months and days, write three DATEDIF functions with the following units in the last argument:

  • Y - to calculate the number of complete years.
  • YM - to get the difference between the months, ignoring years.
  • MD - to get the difference between the days, ignoring years and months.

And then, concatenate the 3 DATEDIF functions in a single formula, separate the numbers returned by each function with commas, and define what each number means.

Assuming the date of birth is in cell B2, the complete formula goes as follows:

=DATEDIF(B2,TODAY(),"Y") & " Years, " & DATEDIF(B2,TODAY(),"YM") & " Months, " & DATEDIF(B2,TODAY(),"MD") & " Days"

This age formula may come in very handy, say, for a doctor to display the exact age of patients, or for a personnel officer to know the exact age of all employees:
Calculating exact age from date of birth in Excel

For more formula examples such as calculating age at a particular date or in a certain year, please check out the following tutorial: How to calculate age in Excel - formula examples.

Calculating leap years in Excel

As you know, nearly every 4th year has an extra day on February 29 and is called a leap year. In Microsoft Excel sheets, you can determine whether a certain date belongs to a leap year or a common year in a variety of ways. I'm going to demonstrate just a couple of formulas, which in my opinion are easiest to understand.

Formula 1. Check if February has 29 days

This is a very obvious test. Since February has 29 days in leap years, we calculate the number of days in month 2 of a given year and compare it with number 29. For example:


In this formula, the DATE(2015,3,1) function returns the 1st day of March in the year 2015, from which we subtract 1. The DAY function extracts the day number from this date, and we compare that number with 29. If the numbers match, the formula returns TRUE, FALSE otherwise.

If you already have a list of dates in your Excel worksheet and you want to know which ones are leap years, then incorporate the YEAR function in the formula to extract a year from a date:


Where A2 is a cell containing the date.

The results returned by the formula are as follows:

Calculating leap years in Excel

Alternatively, you can use the EOMONTH function to return the last day in February, and compare that number with 29:


To make the formula more user-friendly, employ the IF function and have it return, say, "Leap year" and "Common year" instead of TRUE and FALSE:

=IF(DAY(DATE(YEAR(A2),3,1)-1)=29, "Leap year", "Common year")

=IF(DAY(EOMONTH(DATE(YEAR(A2),2,1),0))=29, "Leap year", "Common year")

Use the IF function to make the leap year formula more user-friendly

Formula 2. Check if the year has 366 days

This is another obvious test that hardly requires any explanation. We use one DATE function to return 1-Jan of the next year, another DATE function to get 1-Jan of this year, subtract the latter from the former and check if the difference equals to 366:

=DATE(2016,1,1) - DATE(2015,1,1)=366

To calculate a year based on a date entered in some cell, you use the Excel YEAR function exactly in the same way as we did in the previous example:

=DATE(YEAR(A2)+1,1,1) - DATE(YEAR(A2),1,1)=366

Where A2 is a cell containing the date.

And naturally, you can enclose the above DATE / YEAR formula in the IF function for it to return something more meaningful than the Boolean values of TRUE and FALSE:

=IF(DATE(YEAR(A2)+1,1,1) - DATE(YEAR(A2),1,1)=366, "Leap year", "Non-leap year")
Another way to determine leap and non-leap years in Excel

As already mentioned, these are not the only possible ways to calculate leap years in Excel. If you are curious to know other solutions, you can check the method suggested by Microsoft. (As usual, Microsoft guys are not looking for easy ways, are they?) And you can find 14 other formulas on this blog.

Hopefully, this article has helped you figure out year calculations in Excel. I thank you for reading and look forward to seeing you next week.

112 responses to "How to convert date to year in Excel & calculate age from date of birth"

  1. SANJAY MEENA says:


    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.

    • 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"

      • Rabiul Islam says:

        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.

      • CYNDI says:

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

  2. Su says:

    Thanks a lot, very useful for me

  3. Maeg says:

    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

  4. Becky says:

    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.

  5. Becky says:

    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.

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

  6. Olivier says:

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

  7. ajit says:

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

    if asset is <4.00 = < 4 Year

  8. Niroj KAtuwal says:

    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.

  9. Angie J. says:

    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.


  10. Gregg says:

    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.


  11. Gregg says:

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


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

  12. waheed says:

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

  13. Soban Bisht says:

    How to convert 2.3 in to 2 years 3 months

  14. bidhan says:

    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

  15. Kunal says:

    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.

  16. anurag chaturvedi says:

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

  17. Kunal says:

    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"

  18. Julieanna says:

    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.

  19. Leslie McKeon says:

    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.

  20. Sha Mahmud says:

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

  21. NIcole says:

    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.

  22. Fifa says:

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

  23. shivanand sharma says:

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

  24. SEBI THOMAS says:

    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.

  25. shankar gavot says:

    Dear madam!
    i need your help

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

  26. Jezalene Joy Albeza says:

    5/14/2015 12/31/2015 7 months, 17 days 7.59

  27. Blaithin says:

    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

  28. Aibek says:

    Hi, can you please help me ?

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

  29. Ramteke B S says:

    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.

  30. Ramteke B S says:

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

  31. Gopi says:


    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. Rahman says:

    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.

  33. Michelle Hill says:

    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

  34. Katy Ingram says:

    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!

  35. Crystal says:

    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.


    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?

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

  36. KHOKHAR says:

    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


  37. Jai Prakash Meena says:

    Thanks very much for useful infornation

  38. Kellie says:

    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.

  39. praveen says:

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

  40. Sharee says:

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

  41. Buddy says:

    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

  42. Bruce L says:

    Re: Calculating leap years...


    ...can be simplified to


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

  43. AD says:

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

  44. Nora MF says:

    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.

  45. Laura M Martin says:

    Thank you!

  46. Ravi kumar says:


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

  47. Ravi kumar says:

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

  48. Ravi kumar says:

    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

  49. jen says:

    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.

  50. eli says:

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

  51. Jody says:

    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

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

  52. Matt Marak says:

    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.

  53. Katrina says:

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

  54. Trey says:

    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:


  55. Peter Marsh says:

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

  56. SAVAN CHOUHAN says:

    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

  57. SAVAN CHOUHAN says:

    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

  58. kanagaraj says:

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

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

  59. JS says:

    Thanks, this helped me a lot.

  60. Popoy says:

    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

  61. Megan says:

    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.

  62. asfaq says:


    I want to calculate service period in three different cell at excel. Ex- Suppose someone's service year is 05 Years, 10 Months & 09 days. I want to show above three result in three different cell. Is it possible? if possible please reply me. I am real big trouble.


  63. vikram jit gupta says:

    Hi All,

    How can I add year fraction in excel. for e.g. I have 1st company experience as 2.6 (2 years 6 months) and next company experience is 16.10 (16 years 10 months) if I am adding both ideally my total experience is 19.4 (19 years 4 months) whereas if I am trying to calculate in excel, outcome is 18.76.

    Please advise if I could use any alternate formula to sum up year fraction in excel.


  64. Arvind says:

    Hi All,

    I have one query regarding my data I have a cell-like 05;11;18 and where 05, 11 and 18 are the date so how can I extract and count it as 3.

  65. Ravindra Kanojiya says:


    I want to know how to calculate retirement age remaining years in negative value means if employees already cross the retirement date so how they show in reaming year in negative value.

    Please help it's urgent.

  66. Stuart Rosenthal says:

    Hello experts! I wish to calculate a student's current grade in school based on their graduation year. The trick is that students' grade levels change as of July 1st each year. Examples: A student's graduation year is 2021. Today is May 25, 2018. They are currently in 9th grade, but as of July 1st, 2018 will be in 10th grade. Perhaps it would be useful to assume that "graduation year X" really equals "June 30, year X"?

    Here are the graduation years and corresponding grade levels through June 30th. As of July 1st, the grade level will need to rise by 1.

    2029 - 1
    2028 - 2
    2027 - 3
    2026 - 4
    2025 - 5
    2024 - 6
    2023 - 7
    2022 - 8
    2021 - 9
    2020 - 10
    2019 - 11
    2018 - 12
    >2018 - grad

    Further, could the formula cause the cell to be shaded yellow if the grade is 1-5, green if grade 6-8, blue if grade 9-12, and pink if grad?

    It would also be useful to be able to do the opposite - calculate a grad year based on their current grade in school.

    I know you must enjoy a good puzzle!

    Many thanks.
    Stuart R

  67. Ann says:

    Hi ,

    If I have 13/05/2014 date and I want to convert to Financial year April to March how do I do?

  68. Ashish says:


    Please help!!

    I have tenure as 17.5 years, need to know the joining date from today what formula I should use.

    Please advise?


  69. April J Garcia says:

    How can i add year to date for payroll?

  70. April J Garcia says:

    I was wondering how i can add year to date to an excel sheet gor payroll i have no current year to date on the spreadsheet right now?

    • Doug says:

      I think you're asking how to create a running total. This is where the latest data is added to the existing total to display the most current total. This procedure is repeated each time data is added so that the sheet shows the newest data and the newest total.
      If this is what you want to do, you create a running total by entering a range formula which is part absolute reference and part relative reference. The range formula looks like this: $A$1:A1. Absolute reference with the "$", relative reference without it. Where A1 is the first cell in the range.
      So, if the cell that will hold the payroll to be used in the Year-to-Date amount is in column "D" and the calculated running total is in column "E" the formula in column "E" will look like this:
      =SUM($D$6:D6) formula in E6 displays Year-to-Date of amounts in cells above E6
      =SUM($D$6:D7) formula in E6 displays Year-to-Date of amounts in cells above E7
      =SUM($D$6:D8) formula in E6 displays Year-to-Date of amounts in cells above E8
      After you've copied it down to row 6, 7 & 8. This will calculate the running total to row 8. Just continue to copy this formula down the "E" column to display the most current Year-to-Date.

  71. Layla says:

    Hi, I hope you can help me with the correct formula to calculate separation pay. What is the formula to show that a fraction of at least six (6) months shall be considered as one (1) whole year.

    Start Date: 02/08/1995
    Separation Date: 09/05/2018
    Years of Service: 23 years and 6 months

    How can I get 24 years total years of service since 6 months should be considered an additional year? Thankful for your help in advance.

  72. Oddvar says:

    I need to calculate the number of days that is in each month that regards to the Start date and End date.
    Fx. Des 18 (zero), Jan 19 (zero), feb 19 (13 days) , mar 19 (31 days) and so on

    Start date ---- End date des.18 jan.19 feb.19 mar.19 apr.19 mai.19 jun.19
    15.02.2019 18.10.2019

  73. JERRY MOTO says:

    how can I use if function to calculate the YEAR column from a given date column? Example :I HAVE A COLUMN

  74. RIZWAN ALAM says:

    mere pass age ka column of year define karna hai kaise hoga..
    srno name age dob( dd-mm-yyyy)
    1 aaa 42
    2 bbbb 55
    3 ccc 65
    4 ddd 32

  75. manjunathan says:

    Doj - 01-07-2005
    cut off date - 01-04.2019
    How many year experience (made hiffen in all dates)
    Thanks in advance

  76. FELIX L. MIRAVILLA says:

    Would appreciate if you give provide a formula for below scenario. Thank you in advance.
    Start date: 5-27-2013
    End Date: 10-3-2019
    Counting number of years using a fraction of at least six (6)months being considered as one (1) whole year. So, 5-27-2013 to 12-31-2013, is considered one (1) year. Counting on a yearly basis, it means that from 5-27-2013 to 10-3-2019, is considered 7 years. Can you please provide me a formula for these?

  77. Jindee says:

    I only have a data of year and number of day in a year. Example Year is 2020 and the number of day in the year is 32 then the result should be Feb 2, 2020. Is there a formula which I can obtain this result?

  78. Lee says:

    can you please help to create a formula that can be use
    i want to determine and the remarks appear based on below in one cell only
    if date is less than 6 months from now = SLOWLY MOVING
    if date is less than 1 year from now = NON MOVING
    if date is less than 3 months = RUNNING

  79. Ganesh says:

    I have list of employees with their date of birth (in dd-mmm-yyyy format) n a worksheet.
    I want to highlight the date of birth in rows which are unique. ie., just the date & month when they are unique ignoring the year. eg., there may be two employees, 1 with date of birth 2-Jun-1993 & another with date of birth 2-Jun-1975. So, i want these two to be highlighted ignoring the year.
    Can you please help me how to do it either using a formula or conditional formatting.
    Thanks in advance.

  80. Cyril Paul says:

    I'd like to calculate end-dates for financial purposes.

    E.g., a security with a 1 year maturity period will mature on 31/12/2020, if the investment was made on 01/01/2020.

    DATE formulae in Excel that I have seen so far seem to reflect the anniversary of the start date, i.e., 1 year from 01/01/2020 is given as 01/01/2021 (and not 31/12/2020) and 5 years from 01/01/2020 is given as 01/01/2025 (and not 31/12/2024, which is the correct date).

    Is there any way to address this?

  81. Muhammad Manan says:

    Can some one help me to hide values in zero if I use this formula to calculate employment tenure in organization.
    I get the result 0 years 0 months 20 days.
    I want to show it as 20 days.


  82. Ravi says:

    How to find the date of birth by retirement date and retirement age?
    For Example: Retirement date is 01-Mar-2014 and Retirement age is 58 years.
    Now how to find the date of birth.
    Thanks in advance.

  83. Christine says:

    This is bizarre... Is there no way just to enter simple years into an Excel table without converting from something, so that Excel recognizes it as a year??
    I made a simple data table and a line chart with two data sets. But... They came out as separate, consecutive curves, not overlapping. And the years with data were not spread in a time scale in my graph, but regularly, even where I had 100-year gaps. I decided it is, because the cells were formatted as "general". OK, but whatever I tried messed up my year values. I don't have days to start with, only years. I reformatted the cells and re-entered the years, and they change. I. e. I type in 1818, and the cell makes 1904 out out of it (Why??). Same happens if I type the data in first, then reformat to yyyy. If I invent dates before reformatting, all ends up as the same year. This is so wrong. I wouldn't bother with the cell format and leave it at "general", but then Excel doesn't get the distances right between the time periods. Can Excel not do years by themselves???

  84. Christine says:

    Oh, and I cannot invent days to get around this, either, because the program abbreviates both, the 1900s and the 2000s to the last 2 digits, so there is no longer a difference between the two millenia (i.e. 1913 and 2013 both show up only as 13 - Why??). Not so with the 1800, it would still be 1813 - what IS going on??

  85. Christine says:

    It becomes even worse: It doesn't look as if Excel can do dates before 1900? I cannot attach a screen shot here, can I?

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