Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Total Months (Excel 2003)

    Hi,

    I am using a simple formula find out the total months the employee has been in the system.=IF(A2<DATE(2006,10,1),12,DATEDIF(A2,DATE(2 007,9,30),"M"))

    However i have observed that by using the formula, i am getting one month short. for eg, if someone has joined on 01-Dec-06 and on 30-Sept-07 should be 12 Months. The output is 11 Months.

    Can someone explian who so. Also if the formula can be modified to check if the Date of Joining is <15th take the entire month else next month.

    Attaching the file for ur reference.

    Regard
    Baiju
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Total Months (Excel 2003)

    DATEDIF subtracts months. It counts 5 months from 4/1/2007 to 9/1/2007 (or 9/30/2007) since 9 - 4 = 5. If you want to count this period as 6 months, you have to add 1 to the result. (Your example is incorrect, by the way - from 12/1/2006 to 9/30/2007 is 10 months, not 12).

    You could use this formula to take into account whether the day is before or after the 15th:

    =IF(A2<DATE(2006,10,1),12,DATEDIF(A2-DAY(A2)+1+14*(DAY(A2)>=15),DATE(2007,10,1),"M"))

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Total Months (Excel 2003)

    Hi Baiju

    From my understanding datedif using M for month, as in your example calculates the number of complete months between Date 1 and Date 2. Your formula is therefore doing the correct thing and counting the months from the end of December. ie Jan through to September
    Jerry

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Total Months (Excel 2003)

    Or perhaps:

    =MIN(12,DATEDIF(A2,DATE(2007,10,0),"M")+(DAY(A2)<1 5)*1)

    though I'm not sure it fits all your possible scenarios.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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