Excel has a function that calculates the number of days between two dates, the DATEDIF function.
The Formula
=DATEDIF(A2,B2,"d")
You got the number of days between two dates, but there is one problem, cell C4 returns an error. Error #NUM! happens because the end date is earlier than the start date.
Please read the article below for a more detailed explanation for DATEDIF function
To fix the #NUM! Error, the solution is to use the subtraction formula. Excel stores date values as an integer number, to find out the number of days between two dates use the subtraction formula.
The Formula
=B2-A2
Look at cell C4, the DATEDIF function returns a #NUM error, but with subtraction formula, there is no error but a negative number.
To calculate the number of weeks between two dates divide the number of days between two dates by number 7. The result is the number of weeks between two dates.
The Formula
=(B2-A2)/7
A result is a decimal number, a number of weeks between October 29, 2018, and January 5, 2019, is 9.714286.
Is it possible to get a full week difference and shows the remaining days (that are not up to 1 week)? Of course, Excel can do this, thanks to the QUOTIENT and MOD function.
The Formula for weeks column
=QUOTIENT(B2-A2,7)
The Formula for days column
=MOD(B2-A2,7)
Between October 29, 2018, and January 5, 2019, there are nine weeks and five days.
Please read the article below for a more detailed explanation for QUOTIENT and MOD function
To calculate the number of months between two dates, you must use the DATEDIF function. You cannot use the division formula that divides the number of days between two dates by 30. Why? Because in a month could be 28, 29, 30 or 31 days.
The Formula
=DATEDIF(A2,B2,"m")
You got a #NUM! error, it means the DATEDIF function unable to do a calculation if the end date is earlier than the start date.
The solution is to change the start date and the end date position if the end date is earlier than the start date. You need the IF function to do this trick.
The Formula
=IF(B2<A2,-1*DATEDIF(B2,A2,"m"),DATEDIF(A2,B2,"m"))
No more #NUM errors and cell C4 can display a negative number :).
The DATEDIF function result is a complete difference. If there is less than 1-month days difference, excel will ignore it.
How to calculate the remaining days that are not up to 1 month? still using the DATEDIF function but the unit argument is “md” (calculating the day difference by ignoring the difference in months).
The formula for days column
=IF(B2<A2,-1*DATEDIF(B2,A2,"md"),DATEDIF(A2,B2,"md"))
How to calculate number of years, months and days between two dates?
The Formula for years column
=IF(B2<A2,-1*DATEDIF(B2,A2,"y"),DATEDIF(A2,B2,"y"))
The Formula for months column
=IF(B2<A2,-1*DATEDIF(B2,A2,"ym"),DATEDIF(A2,B2,"ym"))
The Formula for days column
=IF(B2<A2,-1*DATEDIF(B2,A2,"md"),DATEDIF(A2,B2,"md"))
What is Flash Fill Excel Flash Fill automatically fills your data when Excel detects a pattern in your initial data…
What is Paste Special Paste special is a paste but you can choose the result type. As we all know,…
What is the Excel TEXTJOIN Function? The excel TEXTJOIN function is the best function to concatenate strings in Excel, has…
What is the Excel CONCAT Function? The excel CONCAT function combines all data; it could be a text, a number…
What is the Excel CONCATENATE Function? The excel CONCATENATE function combines text or numbers into a text. CONCATENATE Syntax CONCATENATE(text1,…
What is the Excel REPLACE Function? The excel REPLACE function replaces the text based on a predetermined position. REPLACE Syntax…