Help Req on - Date Calculation

[JawaharPrem]

Hi All,

please help in date calculation formula details given and file enclosed for reference.

1. i have offer Validaity month in B Coloum,
offer act date F Coloum and BC Date in D coloum
Now what i nedd to find out the offer closing date
Ex - 1st Case - offer month 4, Offer act date is on 19th april 18 and BC date is 12th the closing data should be on 12th Aug 18.

EX2 - 2nd case - offer month 6, offer act date on 19th Jan 2018, BC Date on 20th May the closing date should be 20th July.

Ex3- 3rd case - offfer month 4, offer act date on 5th april, Bc date on 12th May for such this cases if offer act date less than bc date the date should calculate
the offer act that mont
Like - Offer act date 5-April-2018 the caculation should be from 12th April.

if its possible please help on same.

thanks
Jawahar Prem

 

[Asheesh]

Hi,

Can you explain Ex3 a little more, it is not very clear...

 

[JawaharPrem]

Hi Asheesh,

Thanks for your time, i have worked on this but i not getting the proper output, i enclosed the file and updated the remarks to understand, Please help on this.

thanks
jawahar prem

 

[Asheesh]

I am using the below formula to achieve the result that you already have and in a few cases it is different.

Can you use the below formula and compare with your results plus highlight the rows where the result should be different. Also, please mention the reason why should it be different and what should be the result.

I am sorry as I am not able to comprehend all the logics that have been applied.

=EDATE(DATE(YEAR(OFFER_ACTIVATION_DATE),MONTH(OFFER_ACTIVATION_DATE),DAY(DD)),Month-1)

Note: in the above formula, text highlighted in Red is referring to the column headers, you would need to use the cell reference instead of column references to achieve the results.