Hey All,
Nice to see you in a excel forum.
I have a problem for salary calculation between two dates e.g logic 1 and logic 2 listed there.
When tried to calculate manual (Logic 1 ) it is seems fine but when tried to calculation from logic 2 get different amount 1797.
Can somebody let me know where is the mistake or or can create new formula for it..
Note: logic 1 is correct but we need to calculation as per logic 2.
Logic 1
Manual Days calculation
Start date End Date Salary Montly days Formula Prorata salary
24-Mar-17 31-Mar-17 49,500 8 =E9/31*F9 12774
Manual 12 Months calculation
Start date End Date Salary Montly Months Formula Prorata salary
1-Apr-17 31-Mar-18 49,500 12 =E14*F14 594000
Total days+Montsh 606774
Logic 2
Start date End Date Months Salary Montly Formula Prorata salary
24-Mar-17 31-Mar-18 12.22 49,500 =F2*E2 604,977
Total 604,977
Different Logic 1 Vs Logic 2 1,797
Months calculation formula
=(DATE(YEAR(C17),MONTH(C17),DAY(C17))-DATE(YEAR(B17),MONTH(B17),DAY(B17)))/(365.25/12)
Thank you now file attached
Bookmarks