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:
A B C D FROM TO MONTHS DAYS CASE1 01/01/2006 30/06/2006 6 0 CASE2 15/01/2006 30/06/2006 5 16 CASE3 27/03/2006 30/06/2006 3 4 CASE4 15/02/2006 30/06/2006 4 16 CASE5 24/09/2012 31/12/2012 3 8
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!!
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))))
Bookmarks