Formula to return text when column has dates in a specific range

Hi,

I think you are going to need a helper column that uses a formula to compare the adjacent "Date" cell to all the other dates in the column. Then your "Trigger" formula would look at the helper column to determine if there is a value equal to or grater than 3.

In helper column I, cell I3, enter and copy down:

Please Login or Register  to view this content.

In "Trigger" cell use:

Please Login or Register  to view this content.

Hope this works for you and is helpful.

Cheers

Hi thanks for your response!

It's the first time I have tried to use a formula like this so I am not sure if I have done it correctly as it doesn't seem to be working, I have attached the file below, can you let me know if I have missed something?

Thanks!

Hi,

Almost there. Formulas in your helper column shouldn't look at anything other than the array that is being evaluated. Yours was also looking at the Trigger cell.

See attached example.

Cheers

Hi Southward,

Ah ok thanks! That makes sense it still seems to be slightly wrong in when it determines the triggers, it might be my poor explanation so apologies! When I input the first 2 dates no matter what I put the third date in it always returns "Yes" regardless of what date it is when it should only return yes if all 3 dates are within a 3 month period. for example if the first date is the 01/06/2016 the latest the third date can be for the trigger to be yes must be 01/09/2016, the second date can fall anywhere in between these dates really as then all 3 dates are within a 3 month period.

Another example would be if the first date is 01/06/2016, the second date is the 01/09/2016, the third date is 01/10/2016 and then a fourth date is the 01/11/2016, the trigger should ignore the first date as the only three dates now within a 3 month period is the final three entries, so the trigger should read No until that last entry is put in place.

If that makes sense haha, I think I have managed to confuse myself!

Kind Regards,

James

Try

In I4 and copy down

=IF(H4="",0,COUNTIF($H$4:H4,">="&H4-90))

Hi James,

Sorry about that. I should have included an IF statement so the helper formula returned "nothing" if the adjacent cell was blank.

Use this in I4 and copy down:

Please Login or Register  to view this content.

Cheers

Well there you have it with confirmation from John. And he is from your side of the world, so you know it has to be right.

Use Sothward's ....

=IF(H4="","",COUNTIF($H$4:H4,">="&H4-90))

to avoid 0 in your column

Awesome guys thank you both so much! One final thing (hopefully!) and this is just a formatting issue but I can't seem to get it right, I am inputting these dates as we would do in the uk, so in the attachment I have put 01/01/16 and it detects this as the 1st of January, the second date I input is the 12th of January 12/01/16, however excel changes this automatically to the American date format meaning the first of December, so when I attempt to put in the twentieth of January 20/01/16 the formula no longer works as it is looking for a twentieth month! I have tried messing around with the formatting of the cells but it either doesn't work or if you delete the contents of the cell the format reverts back and so the formula stops working. Any suggestions?

If this final tweek can be made I promise it's the last thing haha!

Kind Regards

Hmm that's strange, could this be an option/setting that is in Excel itself as opposed to a formatting issue? The file on my end is stuck as 01 January 2016, 01 December 2016 and 20/01/16 when trying to enter 01/01/16, 12/01/16 and 20/01/16

It may be due to me playing with custom formatting when I was working with it. I used custom format to force DD/MM/YYYY. Maybe if you remove the custom format it will revert back to your normal format.

Sorry about that

Doh! I worked it out, it's because I updated to Windows 10 yesterday and my computer defaulted its clock to US format so Excel was reading the format from my system no matter what I changed, I only just noticed it now! What an idiot haha! It's all fully working now thanks everyone! I will definitely be adding reputation for all your help!

Thanks all!

Glad I could be of some assistance.

Cheers

Thank you for the rep.

As an aside, installed Windows 10 on 2 m/cs but didn't experience your problem!

Hmmm how strange, at least it's all sorted now!

One more question I wanted to add around adding functionality and understanding the formula. At the moment the formula looks for 3 dates that fall in a 3 month period, if I wanted to change this formula to look for 5 dates in a 5 month period I have managed to break the formula down from what you have provided so I can edit and tailor it and I have worked out how to get this functioning correctly by changing:

=IF(MAX(C4:C9)>=3,"Yes","No") to: =IF(MAX(C4:C9)>=5,"Yes","No")

and

=IF(B5="",0,COUNTIF($B$5:B5,">="&B5-90)) to: =IF(B5="",0,COUNTIF($B$5:B5,">="&B5-150))

Is there a way I could incorporate both of these? So the formula will look for 3 dates in a 3 month period and 5 in a 5 month period?

Thanks again in advance!

That sounds like it could get very complicated.

See attached example for a possible solution.

Cell K3 is used to provide the number of dates per period.

Keep in mind that the range of dates is on a 30 day basis.

Cheers