Calculating Number of Days, Weeks, Months and Years between Dates





To calculate the number of days, weeks, months and years between dates,you can use the formula of “INT”, “TODAY” and “MOD” in Microsoft Excel 2010 and 2013.

INT: - This function is used to return the whole number without decimals. This is better than changing the number of decimal places displayed, which would risk some numbers being rounded up and giving an incorrect result.

Syntax of “INT” function: =INT (number)

Example: Cell A1 contains the date.

  • Select the cell B2 and write the formula =INT (A1)
  • Format this cell as “mm/dd/yyyy”.
  • This function will return only the date from cell A1.

IMG1

 

MOD: Returns the remainder after a number has been divided by a divisor.

Syntax of “MOD” function: =MOD (number, divisor)

img111
 

TODAY: - This function is used to show the current date which will update automatically on a daily basis.

All you need to do is place this function into the age calculation formula in place of a cell reference.

Syntax of “TODAY” function: =Today ()

IMG3

 

DATEDIF:- This function is used to calculate the difference between a pair of dates.

Syntax of “DATEDIF” function: =DATEDIF (date1, date2, output requirement)

We have 2 dates, the 1st date in cell A1, and 2nd date in cell A2.

To calculate the difference in years, use the DATEDIF function as shown in the following formula:

  • Select the cell A3 and write the formula =DATEDIF (A1,A2,”Y”)
  • This function will return the value in years.

IMG4

 

To calculate the difference in months, use the DATEDIF function as shown in the following formula:

  • Select the cell A4 and write the formula =DATEDIF (A1,A2,”M”)
  • This function will return the value in months.

img555

 

To calculate the difference in days, use the DATEDIF function as shown in the following formula:

  • Select the cell A5 and write the formula =DATEDIF (A1,A2,”D”)
  • This function will return the value in days.

IMG6

 

OR

Use the “YEAR”, “MONTH”, “AND” and “DAY” functions as shown in the following formula:-

  • Select the cell A3 and write the formula to calculate the years.
  • =YEAR(A2)-YEAR(A1)-(MONTH(A2)/12)
  • This function will return the no. of years in between 2 dates.

img77

 

A lot of sites avoid using the DATEDIF function to calculate the difference in dates in Excel due to some bugs, as the DATEDIF functiondoesn’t have any documentation in the Excel Help file.But, Microsoft is continuously giving this feature / formula in all new versions.In case you want to avoid this function, you can use manual calculation.
Like below…

=INT((TODAY()-A1)/365.25) & ” years , ” & INT(MOD((TODAY()-A1)/365.25,1)*12) & ” months and ” & INT(MOD((TODAY()-A1)/30.4375,1)*30.4375) & ” days”.

img88

 

It will give the difference in Years, Months and in days. You can use A2 instead of today, where A2 is the greater day than A1 and the formula will give you the elapsed time between these 2 dates.



17 thoughts on “Calculating Number of Days, Weeks, Months and Years between Dates

  1. Hi there,
    i copy-paste your formula as you written above:
    =INT((TODAY()-A1)/365.25) & ” years , ” & INT(MOD((TODAY()-A1)/365.25,1)*12) & ” months and ” & INT(MOD((TODAY()-A1)/30.4375,1)*30.4375) & ” days”

    but it didnt work out. could you give me the correct formula for the result to count years, month, weeks, and days?

    appreciate your help.

    • Milla,

      I think you adding “&” function with space may that could be reason for error, could you pls use below and let us know

      =INT((TODAY()-A1)/365.25)&” years , “&INT(MOD((TODAY()-A1)/365.25,1)*12) &” months and “&INT(MOD((TODAY()-A1)/30.4375,1)*30.4375)&” days only”

      thanks Rishi

  2. INT((TODAY()-A1/365.25) & “years, ” & INT(MOD((TODAY()-A1/365.25,1)*12) &” moths and “& INT(MOD((TODAY()-A1/30.4375,1)*30.4375) &”days only” , i used this formula but don’t calculate, plz give me a reply

    • =INT((TODAY()-A1)/365.25) & ” years , ” & INT(MOD((TODAY()-A1)/365.25,1)*12) & ” months and ” & INT(MOD((TODAY()-A1)/30.4375,1)*30.4375) & ” days”

      Use this. I retyped the quotation marks and it worked.

  3. I think part of the problem is trying to put everything into one cell. I split it into three cells and it works fine.

    A3: =INT((TODAY()-A1)/365.25)
    A4: =INT(MOD((TODAY()-A1)/365.25,1)*12)
    A5: =INT(MOD((TODAY()-A1)/30.4375,1)*30.4375)

  4. i’M TRYING TO SEE IF YOU CAN MAKE A FOMULA WITH A START DATE TO CALCULATE YEAR MONTH DAYS AND WILL STOP WHEN YOU ADD A STOP DATE. COULD YOU PLESE HELP ME WITH THIS PROBLEM. THANK YOU

  5. I am trying to calculate number of open days on action items. Cells used for calculation are today’s date, assigned date, target due date, date closed.

    If there is no date closed and the action item is still open, I would like to know based on today’s date how many days has the action item been open since the assigned date but if the action item has a date closed then I would like to calculate days open based on assigned date and date closed.

  6. hello,
    I have one date in a cell and in a blank cell i would like to know the number of days between that date and todays dates (that the workbook is open) is that possible?

  7. I am trying to calculate the number of months for a project end date occurring in 2014. The project start AND end date can be <2014 or greater than = 2015 – If so, the result is "0". How can I calculate the number of months in 2014 for projects that started before or during 2014?
    So far I have the following:
    =IF(OR(YEAR(End Date field )=2015),””,IF(YEAR(End Date field)>=2015,12,MONTH(End Date field)))

Leave a Reply

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


− seven = 1

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>