Date after a defined period

[leo73pk]

I'm working on a sheet wherein in one column i have an agreement date in next column i have the validity period (years) for which agreements are valid in next colum i want a formula to calculate the expiry date of the agreements based on the agreement date and validity period.

[arthurbr]

If original date is in A1 try

Please Login or Register  to view this content.

[DonkeyOte]

The only thing to be aware of re: above is where A1 falls on a leap year (29th will become 1st March if years anything other than 4)

If you prefer 29th Feb become 28th Feb in the end year where not falling on leap year then given use of XL2007 you could use EDATE

Please Login or Register  to view this content.

Using the DATE version - you could subtract one based on value of A1 where necessary.

Please Login or Register  to view this content.

though I'm sure there are more elegant methods than the above.

[leo73pk]

Thanks both of Your Marvin & Donkeyote,

@Donkeyote,

Sir kindly explain a little more about the following part of your formula, enabling me to understand the logic

Please Login or Register  to view this content.

[DonkeyOte]

It simply multiplies two Boolean outputs. Where both hold True the result of the multiplication will be 1 (TRUE*TRUE) else 0 (FALSE * TRUE or TRUE * FALSE or FALSE * FALSE)

It is the same as:

Please Login or Register  to view this content.

so basically where A1 falls on a leap year and validity_period is anything other than a multiple of 4 we subtract 1 day from original day value (so 29th Feb becomes 28th Feb) else our result would be 1st March

[arthurbr]

Thanks both of Your Marvin & Donkeyote,

Glad to know arthurbr translates as Your Marvin somewhere in the world

[leo73pk]

THANKS FOR YOUR HELP.

@ Donkeyote thanks for your explaining my query in detail.

Regards,