Calculate no of days between periods

[Salosh]

Dears,

Our company has been taken over by new mgt (Y co) on 1st Nov 2012 from old mgt (X co). Y co has the policy of providing employees with EOS benefits as mentioned below
1st 5 yrs - 1 month salary per year
5-10 yrs - 1.5 month salary per year
+10 yrs - 2 months salary per year

But for deciding the slab, total period of service is considered. The below table provides the calculation of days as on 31st Dec 2012 based on different joining dates which I need to fix by way of formula. Request your help

Name	Hire date	Salary	Total service (days)	X co	Y Co	1st 5 yrs	5-10 yrs	+10 yrs
George	09-02-2005	1000	2883	2822	61		61
John	16-12-2007	1000	1843	1782	61	45	16
Margret	13-01-2008	1000	1815	1754	61	61
Paul	12-01-1980	1000	12043	11982	61			61
Sofia	03-11-2002	1000	3712	3651	61		2	59
Jason	19-01-2003	1000	3635	3574	61		61
Julie	01-12-2012	1000	31		31	31

<TBODY>
</TBODY>

 

[etaf]

Takeover date - hiredate - will give days xCo
31/12/2012 - hiredate - will give the the Total days

you can use =DATEDIF(hiredate,enddate,"Y") to get the number of years service and then I hoped to use an IF( formula < =5 , <=10, then greater than 10 years )
BUT
not sure how you are proportioning the 61 days

maybe obvious but i have looked a few times and cant see it

 

[Salosh]

Thanks Etaf

But DatedIF formula shows only completed years of service. hence if we use end date as 31st Dec'12 or 31st Oct'12 it will remain the same.

Regarding 61 days, it is the no of days between 1st Nov 2012 & 31st Dec 2012 which is the no of days served by the person in Y co.

For me this will be a month on month exercise to find the EOS provision to be accounted in books, ie, after 31st Dec'12 I need to redo the accrual at end of every month by just changing the date

 

[etaf]

Regarding 61 days, it is the no of days between 1st Nov 2012 & 31st Dec 2012

I understood that bit
its the split you are showing for the 5,10,>10 i didn't understand and still don't

the DateDif can also use months so you can test with months as well

 

[Salosh]

I will explain in detail

Take the case of John above, his total service period is 1843 days ( 31st Dec 2012 less Hiring date). He is eligible for EOS benefits for the period he served in Y com, ie, 61 days in this case since we are taking the cut off as 31st Dec 2012 ie, 31st Dec 2012 less 1st Nov 2012. He completed 5 yrs on 15-12-2012 which means that he will get 1 month salary/year for 45 days (30 days in Nov & 15 days in Dec). for the balance 16 days ( 31st Dec 2012 less 15th Dec 2012) he will get 1.5 month salary/year. In the same way when I calculate the provision on 31st jan 2013, he will get 1 month salary/year for 45 days & 1.5 month salary/year for 47 days ( 16 days in Dec'12 & 31 days in Jan'13).

Hope it is clear now