How to create a data range from specified criteria

[earthworm]

Suppose a contact is booked on 9th May-2016 for 3 months . Since the day of booking how many times the contract has been renewed and what is the last date of renewal till current date.

example

[TABLE="width: 875"]
<colgroup><col><col span="5"><col><col span="4"></colgroup><tbody>[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Last Date of Renewal[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Booking Date[/TD]
[TD]Renewal [/TD]
[TD]Renewal [/TD]
[TD]Renewal [/TD]
[TD]Renewal [/TD]
[TD]Renewal [/TD]
[TD]Renewal [/TD]
[TD]Renewal [/TD]
[TD]Renewal [/TD]
[TD]Renewal [/TD]
[TD]Renewal [/TD]
[/TR]
[TR]
[TD]9-May-16[/TD]
[TD]9-Aug-16[/TD]
[TD]9-Nov-16[/TD]
[TD]9-Feb-17[/TD]
[TD]9-May-17[/TD]
[TD]9-Aug-17[/TD]
[TD]9-Nov-17[/TD]
[TD]9-Feb-18[/TD]
[TD]9-May-18[/TD]
[TD]9-Aug-18[/TD]
[TD]03-02-18[/TD]
[/TR]
</tbody>[/TABLE]

based on above till today the last date of renewal was 9th November 2017 . How can i achieve it in a single cell without creating the entire date matrix next to it.

i tried this formula but i am lost

=ROW(INDIRECT(C6&":"&TODAY()))

basically since the date of booking i applied edate formula with 3 months to preceeding cell. How can i insert this logic inside above formula .

If there is any other trick please share along with details explanation.


the goal is i dont want to create the entire date table next to it to achieve the result using index match and true false logic. I only want in a single cell . Please also note that 3 months can also be 6 months , 12 months and 7 days etc.

 

[arthurbr]

Last renewal date before today =INDEX(A1:A100,match(true,index(a1:a100XXtoday()),0),))

I am getting some problems editing the " lesser than" operator on this forum. So replace the XX with that symbol<today(),0),))<today(),0),)) will="" give="" you="" the="" last="" renewal="" date="" before="" today<="" html=""></today(),0),))<today(),0),))>

 

[Giordano Bruno]

If the date is in A1, the formula ==INT((YEAR(TODAY())*12+MONTH(TODAY())-YEAR(A1)*12-MONTH(A1))/3)-1 will give you the number of renewals and if that formula is in cell C1, then the formula =DATE(YEAR(A1),MONTH(A1)+(C1+1)*3,DAY(A1)) will give you the next renewal date. It does have a small problem it the original day is greater than and the end of the month in the next renewal month.

 

[earthworm]

If the date is in A1, the formula ==INT((YEAR(TODAY())*12+MONTH(TODAY())-YEAR(A1)*12-MONTH(A1))/3)-1 will give you the number of renewals and if that formula is in cell C1, then the formula =DATE(YEAR(A1),MONTH(A1)+(C1+1)*3,DAY(A1)) will give you the next renewal date. It does have a small problem it the original day is greater than and the end of the month in the next renewal month.

=INT((YEAR(TODAY())*12+MONTH(TODAY())-YEAR(A1)*12-MONTH(A1))/3

Secondly I made a little tweak and its seems to be working fine . I still doubt though.
=DATE(YEAR(A1),MONTH(A1)+(C1-1)*3,DAY(A1))

I will be oblige if you can give a detailed explanation of the logic of above two formula or post video in youtube.

 

[Giordano Bruno]

Year(today())*12 + month(today())gives number of months from year zero. Similarly for the date in A1. Deduct one from the other take the integer of those months divided by variable 3, then deduct one to remove the original date (which was not a renewal).
In the second formula, I add back the 0ne, multiply the total number of three months periods by 3 and add that many months to the date in A1. If the day in A1 was for example 31 and there are only 30 days in the new month, then the formula will return the first day of the following month. To fix that problem would take increase the complexity of the formula somewhat.

 

[earthworm]

Year(today())*12 + month(today())gives number of months from year zero. Similarly for the date in A1. Deduct one from the other take the integer of those months divided by variable 3, then deduct one to remove the original date (which was not a renewal).
In the second formula, I add back the 0ne, multiply the total number of three months periods by 3 and add that many months to the date in A1. If the day in A1 was for example 31 and there are only 30 days in the new month, then the formula will return the first day of the following month. To fix that problem would take increase the complexity of the formula somewhat.

Can you please share that complex fool proof formula through which accurate date appears.

 

[Tetra201]

Try

=INT(DATEDIF(A1,TODAY(),"m")/3) for the number of renewals

=EDATE(A1,C1*3) for the most recent renewal (takes care of the 28/29/30/31 days issue)