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
    Hi Excel Experts,

    I am currently working on some employee data. I had received the formulas for this data from this forum to calculate the Tenure in the organisation.

    The formula in column G calculates the Tenure based on the following criteria. If joined prior to 01-Oct-07 then 12, if > 01st oct-07 and greater 15th of the month rounded to the next month, less than 15th rounded to previous month.

    There are employee who have had level changes during the year, in such cases column C has the date of level change and then calculate the duration in previous role i.e. Months between Col B and Col C in Col D and Col C to 01 Oct -08 in Col E.

    I already have the formulas that was given in this forum, however i find that it does not round of to the previous month or current month if greate than 15th or less than 15th. i.e. if there has been a level change between 01st to 30th Oct its still show in Col D = 1 whereas if it is > 15th is should show 1.

    Can someone help me tweek this formula. Attached is the excel sheet with the formulas

    Thanks
    Baiju
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    What exactly do you mean by "its still show in Col D = 1 whereas if it is > 15th is should show 1"?

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Does this do what you want?

    In D2:

    =IF(C2="-",0,DATEDIF(MAX(B2-DAY(B2)+1,DATE(2007,10,1)),C2-DAY(C2)+1,"m")-(DAY(MAX(B2,DATE(2007,10,1)))>=15)+(DAY(C2)>=15))

    In E2:

    =DATEDIF(MAX(B2,DATE(2007,10,1),C2)-DAY(MAX(B2,DATE(2007,10,1),C2))+1,DATE(2008,10,1), "m")-(DAY(MAX(B2,DATE(2007,10,1),C2))>=15)

    and fill down.

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

    This is exactly what i was looking for.. thank you so much for the formula

    Regards
    Baiju

Posting Permissions

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