Results 1 to 2 of 2

Thread: How to calculate months & days between two dates??

  1. #1

    Lightbulb How to calculate months & days between two dates??



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!!

  2. #2
    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))))

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •