date - Excel Formula for Tenure by Month -


i have table set employee start date, termination dates , monthly columns - see image below.

i trying calculate employee tenure based on following scenarios:

  1. an employee has started within month or employed , has no termination date - tenure calculate normal start date , reporting month (columns cf, cg etc)

  2. an employee has terminated - tenure calculate start date , reporting month until termination month , swap calculation between start , termination date (columns e, f). once termination month has passed tenure 0 employee. eg. start 11-jan-16, termination 8-jul-16, column cf (june) & cg (july) calculate normal , column ch (aug) should 0.

here image of current worksheet set up: enter image description here

the formula have 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")))) 

here 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 formula @ cell cf9 , copy , down, , should result.


Comments

Popular posts from this blog

resizing Telegram inline keyboard -

command line - How can a Python program background itself? -

php - "cURL error 28: Resolving timed out" on Wordpress on Azure App Service on Linux -