# Calculation Required

#### pallu

##### Member
Hi

I need a formula for year end calculation, i have attached the worksheet,

Regards
Paul

#### Attachments

• 8.5 KB Views: 10

#### 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

 Amount End Date Formula Result Actual 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

i have tried below formula result is also vary

 DOJ Amount Result Total year value Result formula 16-05-20​ 2000​ 21032​ 24000​ 20975.34​ IF(\$A2

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

#### pallu

##### Member
yes mine is monthly calculation

#### bosco_yip

##### Excel Ninja
Maybe,

D2, formula copied down :

=(DATEDIF(A2,C2,"m")+((DATEDIF(A2,EOMONTH(A2,0),"d")+1)/DAY(EOMONTH(A2,0))))*B2

#### pallu

##### Member
Thank you so much it works well..