How to calculate months & days between two dates??

khatri.naresh

New member
Joined
May 7, 2014
Messages
1
Reaction score
0
Points
0
HOW TO CALCULATE MONTHS & DAYS BETWEEN TWO DATES??
==================================================

I have a table, having range of dates, like:

DD-MM-YYYY TO DD-MM-YYYY
01/01/2006 TO 30/06/2006
15/07/2006 TO 31/12/2006
24/03/2007 TO 30/06/2007
31/07/2007 TO 31/12/2007

Dates are in column A & B.
Column C = Months
Column D = Days

IN COLUMN C, having this formula:-
=DATEDIF(A2,B2+1,"M")

IN COLUMN D, having this formula:-
=DATEDIF(A2,B2+1,"MD")

THE ABOVE TABLE SHOWING THIS RESULT:
ABCD
FROMTOMONTHSDAYS
CASE1 01/01/200630/06/2006 60
CASE2 15/01/200630/06/2006 516
CASE3 27/03/200630/06/2006 34
CASE4 15/02/200630/06/2006 416
CASE5 24/09/201231/12/2012 38











IN ALL THE CASES MONTHS (COLUMN C) ARE CORRECT.
BUT DAYS ARE SHOWING WRONG: IN CASE2,3,4,5

RIGHT DAYS SHOULD BE LIKE THIS:-
CASE 2 = 17 DAYS
CASE 3 = 5 DAYS
CASE 4 = 14 DAYS
CASE 5 = 7 DAYS

PLZ HELP, WHAT IS THE PROBLEM IN ABOVE FORMULAS.
PLZ SUGGEST WHAT FORMULA SHOULD I USE??

THANX IN ADVANCE!!
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
This gives the results that you show

=N(DATEDIF($B2,$C2,"YM")+AND(DAY($C2)=DATEDIF($B2,$C2,"MD")+1,MONTH($C2)<>MONTH($C2+1)))

=(DATEDIF($B2,$C2,"MD")+1)*(NOT(AND(DAY($C2)=DATEDIF($B2,$C2,"MD")+1,MONTH($C2)<>MONTH($C2+1))))
 
Top