In this article we will learn how we can count the number of weeks in between two dates, number of months in between two dates, number of days in between two dates and number of years in between two 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.
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.
MOD: Returns the remainder after a number has been divided by a divisor. Syntax of “MOD” function: =MOD (number, divisor)
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 ()
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 count the number of years in between two dates, 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 calculate age from date of birth in excel.
To count the number of months in between two dates, 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.
To calculate the number of weeks and 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.
- To return the number of weeks divide the number of days to 7.
To calculate pregnancy weeks in excel to return week by week days; we can use INT & MOD functions together.
Following is the example of date we have:
- In cell D2 the formula is =INT((C2-B2)/7)&”w “&MOD(C2-B2,7)&”d”
This will work as pregnancy calculator or we can use it as to calculate due date.
“YEAR”, “MONTH”, “AND” and “DAY” functions as shown in the following formula:-
- Select the cell A3 and write the formula to calculate the years.
- This function will return the no. of years in between 2 dates.
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”.
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.