Conditional Formatting End of Quarter

[Loadmaster]

I would like a conditional format that if on the last day of the end of the
quarter that if there is no date within that quarter (ie previous quarter in
cell A1), that cell A1 turns red and stays red into the next quarter, until a
new date is entered into cell A1.

 

[Loadmaster]

Take out the word "previous" in my question.

 

[Lars-Åke Aspelin]

Maybe some more words should be taken out or be replaced...

What is "...the last day of the end of the quarter..."?

It would be easier to understand what you are after if you include a
few examples with different data and the expected results for each
example.

Lars-Åke

 

[Loadmaster]

The last day of the end of this quarter is the 31st of Dec. So if on the 31st
of Dec the date within cell A1 is not within the 30th of Sep, not counting
the 30th of Sep to the 31st of Dec, counting the 31st of Dec. Cell A1 will
format as red and continue showing red into Jan Feb etc ... until a new date
is entered into cell A1. The next quarter ends on Mar 31st, if a date is in
cell A1 is within 31st Dec to the 31st of Mar then cell A1 will remain white
or green whatever I choose. Cell B1 is automatically set up to show the end
of the quarter with the following formula:
=DATE(YEAR(TODAY()),INDEX({3;6;9;12},INT((MONTH(TODAY())+2)/3))+1,0). I also
had to format the date as dd mm yy as the above formula entered a bunch of
digits. I hope this helps you figure out an answer. Sorry for the confusion.

 

[Lars-Åke Aspelin]

Lets say that you have the date 25th of September i cell A1.

On 29th of December cell A1 is green.
On 30th of December cell A1 is still green.
On 31th of December cell A1 turns red.
On 1st of January cell A1 is still red.
On 2nd of January cell A1 is still red.
Then a new date is entered in cell A1.
If the new date is before 25th of September, what will happen to the
color?
If the new date is Sept 26th, Sept 27th, Sept 28th, Sept 29th, Sept
30th, Oct 1st, Oct 2nd, ...., December 30th, December 31st, Jan 1st,
Jan 2dn, Jan 3rd - I all these cases, "a new date is entered into
cell A1". Does it mean that it should turn green in all these cases?

I guess there are some "built in" conditions on which type of dates
that can be entered into A1 that you know of but is not obvious if you
don't what these dates are used for. Maybe entering a "new date"
always mean that you enter todays date, or a later date than before,
but not neccessarily much later. It could still be a quite "old" date.

Lars-Åke

 

[Loadmaster]

Hypothetically if today is the 31st of Dec “Last day at the end of the
quarter†and the date in cell A1 is the 25, 26, 27, 28, 29 or 30 of Sep, cell
A1 should be red. Flight crews must obtain a date within the current quarter
otherwise they loose there currency. If today is the 1, 2, 3 etc… of Jan and
the date in cell A1 still reads a date in Sep or prior, cell A1 remains red
until a newer date within the current quarter is entered into cell A1. When a
new date is entered into cell A1 it means the aircrew member has just updated
his/her qualification and cell A1 can remain neutral, white or green
depending on what option I choose for cell fill. If today is the 30th of Dec
and the date within cell A1 is within the previous quarter cell A1 can remain
neutral, white or green as they still have that day to update there currency
prior to the end of the quarter. The new date entered is always within the
current quarter. Your colors are all correct on your thread above. I hope
this helps in finding a solution.

 

[Lars-Åke Aspelin]

So here is the conditions if I got your description right:

Cell A1 should be highlighted if any of the following two conditions
are true:

1) Today is the last day of (the end of) the quarter and A1 has a date
that is before the first day of the same, current, quarter

2) A1 has a date that is before the first day of the previous quarter.

Put this formula in any unused cell

=OR(AND(TODAY()=DATE(YEAR(TODAY()),INDEX({3;6;9;12},INT((MONTH(TODAY())+2)/3))+1,0),A1<DATE(YEAR(TODAY()),INDEX({3;6;9;12},INT((MONTH(TODAY())+2)/3))-2,1)),A1<DATE(YEAR(TODAY()),INDEX({3;6;9;12},INT((MONTH(TODAY())+2)/3))-5,1))

all in one line.

Then set a conditional formatting on cell A1 to be highlighted the way
you choose if the cell with the formula above has the value TRUE.

Hope this helps / Lars-Åke

 

[Loadmaster]

Thank-you very much, it worked.

So here is the conditions if I got your description right:

Cell A1 should be highlighted if any of the following two conditions
are true:

1) Today is the last day of (the end of) the quarter and A1 has a date
that is before the first day of the same, current, quarter

2) A1 has a date that is before the first day of the previous quarter.

Put this formula in any unused cell

=OR(AND(TODAY()=DATE(YEAR(TODAY()),INDEX({3;6;9;12},INT((MONTH(TODAY())+2)/3))+1,0),A1<DATE(YEAR(TODAY()),INDEX({3;6;9;12},INT((MONTH(TODAY())+2)/3))-2,1)),A1<DATE(YEAR(TODAY()),INDEX({3;6;9;12},INT((MONTH(TODAY())+2)/3))-5,1))

all in one line.

Then set a conditional formatting on cell A1 to be highlighted the way
you choose if the cell with the formula above has the value TRUE.

Hope this helps / Lars-Ã…ke