conditional formatting formula with date ranges 5

[ksnpc]

B15 is a date of birth field where a user enters a date.
B16 is a date received field where a user enters the date an application is received.

I am trying to figure out how to create a conditional formatting formula that I can use to flag B16 as red if an invalid date is entered.
Here are the rules to determine what is valid:
When the applicant turns 65 years old, they have a 7 month period to submit an application - normally it is 3 months before their birthday, their birthday month, and 3 months after. But if their birthday falls on the first of the month, then they have 4 months prior to their birthday, their birthday month, and two months after their birthday.

Conceptually, this is what I think I need to do:
=if ((Day(B15)=1), if(...condition to describe range from 4 months prior to 2 months after...), if(...condition to describe range from 3 months prior to 3 months after...))

This is more complicated than any of the conditional formatting I've done in the past. So I don't even know if I'm on the right track. Can anyone offer any advice?

I appreciate any help,
Shauna

 

[SkipVought]

Hi,

So let's get some clarity.

A DOB is 2/7/1952 (Feb 7). My interpretation of your requirements is that I would have from 11/7/2016 to 6/7/2017. Had the DOB been 2/1/2052, the range would be 10/1/2016 to 5/1/2017.

Please verify or clarify.




Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[ksnpc]

That is how I stated it. But I failed to state that they have the entire month 3 months before or 3 months after, etc.
So the 3 months ahead at the beginning of the month to the 3 months following at the end of the month.

So using your example. If the DOB is 2/7/1952 then they have from 11/1/2016 to 5/30/2017. And if it is 2/1/1952 then they would have 10/1/2016 to 4/30/2017.

Thank you - I should have included an example in my original post to be clearer.

 

[strongm]

5/31/2017 surely

Conceptually, you need a condition that is based on whether cell B15 is not between a calculable start date and a calculable end date

 

[ksnpc]

Oh - 5/31 you're right. That was one of my problems - figuring out how to account for the different number of days in the months. I was thinking of using the EOMONTH function (which I just learned about) but I'm really just using this as a conditional formatting formula so I might just put 31 for the end date for all months and not worry about it.

I'm trying to figure out how to get that condition written but I'm still coming up short. I think I've just been looking at it too long. This is the most recent iteration of the mess I've been playing with:

=if (day(B15)=1),(AND(B16<date(year(B15)+65, month(B15)-4,1), (B16>date(year(B15)+65,month(B15)+2, 31)), (AND(B16)<date<year(B15)+65, month(B15)-3, 1), (B16)>date(year(B15)+65, month(B15)+3, 31))

 

[SkipVought]

I'm using Named Ranges based on the Names in the LEFT HAND COLUMN:
[pre]
DOB 2/1/1952
AppDate 10/1/2016
BrthDay65 2/1/2017
From 10/1/2016
Thru 4/1/2017
[/pre]
[pre][tt]
DOB is entered by the user
AppDate is entered by the user
BrthDay65: =DATE(YEAR(DOB)+65,MONTH(DOB), DAY(DOB))
From: =DATE(YEAR(BrthDay65),MONTH(BrthDay65)-IF(DAY(DOB)=1,4,3),1)
Thru: =DATE(YEAR(From),MONTH(From)+6,1)
[/tt][/pre]

And then the CF formula in AppDate cell: =OR(AppDate<From,AppDate>Thru)

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[ksnpc]

Oh wow that is so much clearer! It never even occurred to me to use named ranges. I changed the Thru to =DATE(YEAR(From),MONTH(From)+6,31) but otherwise this was flawless.

Thank you so much!

 

[SkipVought]

I should have picked that up.

However Thru should be =DATE(YEAR(From),MONTH(From)+7,0) since the last day of any month could be between 28 and 31.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[strongm]

My From calculation is fractionally different:

=DATE(YEAR(DOB)+65,MONTH(DOB)-3-(DAY(DOB)=1),1)

 

[SkipVought]

@strongm, nice!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[ksnpc]

Thanks! I really appreciate both of you taking the time to help me.