In order to calculate the time elapsed from a certain date, we will use a birthdate as an example

Enter birthday date in cell A1: 01/01/1980
To calculate the number of years from the date, enter the following formula in cell B1: =YEAR(TODAY())-YEAR(A1)

The result (rounded): 23 Years

For a decimal solution, use the following formula: =DATEDIF(A1,TODAY(),”m”)/12

The result: 23.25 Years
To calculate the number of months from the date, enter the following formula in cell D1: =DATEDIF(A1,TODAY(),”m”)

The result: 279 Months

To calculate the number of days from the date, enter the following formula in cell E1: =DATEDIF(A1,TODAY(),”d”)

The result: 8499 Days

(Republished with Amendments)

More Under Finance

Posted Under

Category : Finance (3750)
Type : Articles (18266)
Tags : Excel Tips (36)

0 responses to “Excel Tips – Calculate Years, Months, Days elapsed from a certain date in Microsoft Excel”

  1. S.K. Srivastava says:

    I checked up above formulas but I found that only one formula (for years) is working and rest are not working. for the rest three formulas I am getting the error code #NAME?

  2. Anantha says:

    revati.madiwale@LNTEBG.com vatsalyadangi@gmail.com please check your respective mails for solutions and also provided the excel formula sheets for ready to use

  3. Anantha says:

    To vatsalyadangi@gmail.com. – go to conditional formatting tab and set the rule. You have to define range and set the desired colors.

  4. Puttartaju S says:

    REVATI,

    EXample:-

    A B C D

    1 10.04.2012 11.04.2012 =(B1-A1) =(C1*0.20%)

    NOTE THAT A1 & B1 HAVE TO BE IN DATE FORMAT5, AND C1 HAS TO BE IN NUMBER FORMAT.

    DATE FORMAT SHOULD PREFERABLY BE DD/MM/YYYY.

    ADDITIONAL COLUMN FOR % COMMISSION ALSO CAN BE USED.

    REGARDS

  5. Puttartaju S says:

    VATSALYA,

    Seleect the Data you want for your consideration.

    Go to INSERT tab -> Table.
    Select your design.

    Then Select the table and go to HOME tab -> CONDITIONAL FORMATTING -> HIGHLIGHT CELL RULES -> and from here you can define your criteria and excel will automatically highlight your desired values.

    regards

  6. revati madiwale says:

    can anyone tell me , how to calculate formula for BG commission. e.g. BG issued for 2 yrs period is 10.04.2012 to 11.04.2014. Commission rate is .20 % pa.
    I want to put this formula so automatically once data field in the excel for different BGs commission will get calculated automatically. Please mail your answer on revati.madiwale@LNTEBG.com

  7. VATSALYA says:

    Can anyone tell me a formula whereby the Cell in Excel changes its color to a specified one after a particular value is entered in it???

    Plz mail the answer on vatsalyadangi@gmail.com.

    Thanx.

Leave a Reply

Your email address will not be published. Required fields are marked *

Featured Posts