Amount Calculations

[AVG123]

Hello friends...........

i am in a problem, please help me for solve this.......

i am upload a Excel File in which what to do:-

D6 have a date (31-07-2016) and i want to search it in E2:E3, F2:F3, G2:G3, H2:H3, I2:I3, J2:J3,
D6 (31-07-2016) it is not exist in E2:E3, it will show 0 in E6
D6 (31-07-2016) exist in F2:F3, then calculate total number of months between 31-07-2016 & F3
D6 (31-07-2016) not exist in G2:G3, calculate total number of months between g2 & g3
D6 (31-07-2016) not exist in H2:H3, calculate total number of months between h2 & H3
D6 (31-07-2016) not exist in I2:I3, calculate total number of months between i2 & I3
D6 (31-07-2016) not exist in J2:J3, calculate total number of months between j2 & J3

please see the excel file for easily understand

[Bo_Ry]

According to you description total number of months between.
Please try at E6 drag to J6

=IFERROR(DATEDIF(MAX($D6,E2),E3,"d"),0)

[AVG123]

Thanks for reply but it is not working.................

[José Augusto]

I suppose the Bo_Ry is right, just calculated the difference in days instead of months
Try

Please Login or Register  to view this content.

[AVG123]

sir thanks for reply it is working but missing one condition.................
if D6 date exist in F2:F3, then =IFERROR(DATEDIF(MAX($D6,E$2),E$3,"m"),0) it is working.........now in E2:E3=should be 0, G2:G3=6months, H2:H3=6months, I2:I3=6months, J2:J3=3months.



please see the ImageTest.jpg file for easily understand (Highlight in Yellow i manually calculate, and above is your formula
please fix it...........

[Bo_Ry]

Please try E6
=IFERROR(DATEDIF(MAX($D6,E$2),E$3+1,"m"),0)

[AVG123]

thanks for reply it is working on flawless on complete months i.e. 31-10-2017, 28-02-2018, 31-12-2016


(1)Please see d10 have 07-07-2016, and i know July-2016 have 31 days
here i want 31-07=24days
now 24*c10/31=12883
now 12883*f4/100=258 how to calculate this

[JeteMc]

See if the following formula will yield the values you are expecting:

Please Login or Register  to view this content.

Let us know if you have any questions.