Excel Formula for Tenure by Month

0
votes

I have a table set up with Employee Start Date, Termination Dates and then monthly columns - see image below.

I am trying to calculate the Employee Tenure but based on the following scenarios:

  1. An employee has started within the month or is currently employed and has no termination date - Tenure will calculate as normal with Start Date and reporting month (columns CF, CG etc)

  2. An employee has terminated - Tenure will calculate with Start Date and reporting month until Termination month and then swap to calculation between start and termination date (columns E, F). Once the termination month has passed the tenure will be 0 for that employee. eg. Start 11-Jan-16, Termination 8-Jul-16, Column CF (June) & CG (July) will calculate as normal and Column CH (Aug) should be 0.

Here is an image of my current worksheet set up: enter image description here

The formula I have currently is: 

=IF(OR(CF$2>CurrentReportMonth,$E9>CF$2),0,IF($F9>CF$2,0,IF(OR($E9<=CF$2,$F9<CF$2),DATEDIF($E9,CF$2,"M"),DATEDIF($E9,$F9,"m"))))
2
exceldateexcel-formula
No - as you can see in the image on row 9 it is not calculating the tenure for the month of June - and this employee was employed in that month. It is also still continuing to calculate after the month of termination. – Preena
What happen if someone's start date is the first date of the month (ex. 1-Apr-15), do you want to include that month? – ian0411
From cell CF3, enter this formula and drag over to cell CJ12 to see if this is what you need: =IFERROR(DATEDIF(DATE(YEAR($E3),MONTH($E3)+1,1)-1,MIN($F3,CF$2),"m"),"-"). I assume you don't want to include the month of hire unless it is the first date of the month but let me know if you use different rules. – ian0411
@ian0411 Thanks for the formula - it works only for those which have a termination date - but also continues to show a tenure value after the termination date instead of reverting to 0 (we don't want to include tenure calculations for someone who has left the company). – Preena
Didn't know that you want to make those as 0 so the formula now is longer: =IFERROR(DATEDIF(EOMONTH($E3,0),MIN(IF(AND($F3>0,CF$2>$F3),EOMONTH($E3,0),$F3),CF$2),"m"),"-"). Try this and let me know if this is what you are looking for or I can tweak a little bit more. – ian0411

2 Answers

1
votes

Here is the summary of our discussion:

=IF(OR(CF$2>ReportDate,AND($F9>0,$F9<CF$2),$E9>=CF$2),"-",IF(AND($E9<=CF$2,$F9=0‌​),DATEDIF($E9,CF$2,"M"),IF(AND($E9<=CF$2,$F9>CF$2),DATEDIF($E9,CF$2,"M"),DATED‌​I‌​F($E9,$F9,"M"))))

Use this formula at cell CF9 and copy up and down, and that should get the result.

0
votes

Try this:

=IF($A25>$F$24,0,IF(AND(LEFT(CELL("format",$B25),1)="D",$B25<$F$24),0,IF(ISNUMBER(F25),F25+1,1)))

NOTE:
My data setup in the file:

  • Column A = Start Date
  • Column B = Termination Date
  • I had my monthly dates starting from column E

You will have to adjust the column names as per your worksheet but this should give you the results you want