• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculation Required

pallu

Member
1, Result amount update in excel

2, if person Joined between 01-04-20 to 31-03-21 calculation on prorate based on annually

Regards
Paul
 

AlanSidman

Well-Known Member
Data Range
A
B
C
D
1
DOJ​
Amount​
End Date​
Result​
2
43967​
2000​
44286​
=B2*12*YEARFRAC(A2,C2,3)​
3
44243​
2000​
44286​
=B3*12*YEARFRAC(A3,C3,3)​

Data Range
A
B
C
D
1
DOJ​
Amount​
End Date​
Result​
2
5/16/2020​
2000​
3/31/2021​
20975.34​
3
2/16/2021​
2000​
3/31/2021​
2827.40​
 

pallu

Member
Thanks for your help, but result would not be actual

AmountEnd DateFormula ResultActual amount is
16-05-20​
2000​
31-03-21​
20,975.00​
21032​
16-02-21​
2000​
31-03-21​
2,827.00​
2928​
 

AlanSidman

Well-Known Member
Please show your actual manual calculation because excel is doing it based upon the fractional amount of time the EE is part of the organization based upon a 365 day year.
 

pallu

Member
Hi
Thanks for your valuble time

i have tried below formula result is also vary

DOJAmountResultTotal year valueResultformula
16-05-20​
2000​
21032​
24000​
20975.34​
IF($A2<DATE(2020,4,1),(D2),(D2)/365*(DATE(2021,3,31)-$A2))
16-02-21​
2000​
2928​
24000​
2827.397​
IF($A2<DATE(2020,4,1),(D2),(D2)/365*(DATE(2021,3,31)-$A2))

first case
actual amount is 2000*10 = 20000, for porata 16days of May = 1032 Total amount paid to be 21032
 
Top