Arrear Calculation

[pallu]

hi

i need a Arrear calculation from Effective date i have atttached workings on the same

 

[Deepak]

Hi,

Two points needs to clarify.

• you have used DATE(2015,3,33) ?
• Desired output ?

 

[pallu]

i have tried for the formula but its not working
i ahve attached for desire out put

 

[Asheesh]

Hi pallu - can you give the little background behind the output that you want to achieve...I mean the logic used

 

[pallu]

hi
we have calculate salary arrears from date is mentioned new - old diff is arears update for eg if employee new is 500 old is 400 diff 100 we paid from 01/12/2015 out put should 300 this should be calculate on the with effect from date

 

[Deepak]

hi
we have calculate salary arrears from date is mentioned new - old diff is arears update for eg if employee new is 500 old is 400 diff 100 we paid from 01/12/2015 out put should 300 this should be calculate on the with effect from date

I doesn't agree with this as salary is calculated on month days basis like as 31 for Jan, 28/29 for Feb, 30 for Nov so on so.....

So, Working on the same i have attached the sample.

Your attachment at #3 doesn't follow what u said for.

 

[Asheesh]

Assuming that you take standard working days in a month as 30 days...then your formula is right..just change DATE(2015,3,33) to DATE(2015,3,31)

However, using the logic that is explained..your desired output is wrong..is there anything that we are missing...Also, on real time basis, I would go with Deepak's solution..but may vary from org to org..

 

[NARAYANK991]

Hi Deepak ,

Can you explain your calculations ?

My understanding of the situation is as follows , if we consider row 16 , where the data is :

Old : 9000
New : 15100

which means the arrears per full month would be 6100.

Since the date effective from is December 29 , 2014 effectively the arrears should be calculated as follows :

Arrears for December 2014 : to be calculated

Arrears for January 2015 : 6100

Arrears for February 2015 : 6100

Arrears for March 2015 : 6100

which means the total arrears will be 18300 + some additional amount as arrears for the month of December 2014.

However , you are showing the arrears for March 2015 alone as 18300 ; how is this ?

Narayan

 

[Asheesh]

Deepak's total arrear's in column S is wrong I guess..because arrears till march are reflecting correct in Mar column..since 18300 is a cumulative number..Actual Arrear column doesnt hold any significance...but I would love if arrears are calculated this way --

 

[NARAYANK991]

Hi Asheesh ,

You may be right , but I still have my doubts , because the total arrears , as of March , cannot be exactly 18300 , since there will be a small amount from December 2014 , where the effective date was December 29.

Narayan

 

[pallu]

hi

i have attached out put in file itself can you make formula on this

 

[NARAYANK991]

Hi ,

Going by your manual outputs , if the effective date is not in March 2015 , then the date for calculating arrears is 28 February 2015 , since for rows 27 , 28 and 29 you are not showing the arrears for March ; however , don't you think that for the first two employees , the arrears should be shown in March rather than February , since the effective dates are in March , not February ?

Narayan

 

[Deepak]

My deeply apologize as there was blunder mistake in my last attachment. Check with attached.

 

[Deepak]

hi

i have attached out put in file itself can you make formula on this

Hi @pallu ,

Pls explain...

In P27 effective date is 29-01-2015 & arrear is calculated for 2 days on 30 days basis.

In O28 effective date is 29-12-2014 & arrear is calculated for 3 days on 31 days basis.

Where both having 31 days & both are effective month too.

 

[pallu]

hi Deepak
your working is super but athis arrear working Standard should be 30days Eg, if dec-14 is 31days but we calcualte base is 30days only

 

[Deepak]

hi Deepak
your working is super but athis arrear working Standard should be 30days Eg, if dec-14 is 31days but we calcualte base is 30days only

what for feb