1. 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

2. What exactly do you mean by "its still show in Col D = 1 whereas if it is > 15th is should show 1"?

3. 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. 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
•