Results 1 to 4 of 4

Thread: COUNTING MONTHS

  1. #1
    martinegoddard
    Guest

    COUNTING MONTHS

    I have a spreadsheet which indicates the start and end dates of people hired on a contract basis. Is there a way of automatically calculating the number of months they will be employed.

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: COUNTING MONTHS

    If the start date is in cell B1 and the end date is in cell B2, then the following formula should return the 'inclusive' number of months:

    =12*(YEAR(B2)-YEAR(B1))+(MONTH(B2)-MONTH(B1))+1

    By inclusive I mean that both the starting and the ending months are included. Also, the above formula ignores the starting and ending days.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: COUNTING MONTHS

    Here is a VBA function that will calculate it for you:

    <pre>Public Function GetMonths(dStart As Date, dEnd As Date) As Long
    Dim dW As Integer
    GetMonths = DateDiff("m", dStart, dEnd)
    End Function
    </pre>

    Legare Coleman

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: COUNTING MONTHS

    Hi,
    You can use DateDif to return the number of complete months between two dates:
    =datedif(startdate,enddate,"m")
    but note that this will not count partial months - e.g. if start date is 2nd Jan and end date is 1st Feb it will return 0 months.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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