Conditional Formatting with Dates

[Samtk]

Hi all,
my first post so sorry if the structuring of it is off a little. I am working on a Loan Tracking Sheet at work and am faced with two problems:

1.) I have a column that is full of dates that financial statements were delivered to us, and to track this I wanted to create a conditional format that would highlight any cells that had been received within the current quarter + 30 days. My issue here is I can obviously put down a date for the end of each quarter + 30 days which is Q1 + 30 = 04/30/2022, Q2 + 30 = 07/30/2022, Q3 + 30 = 10/30/2022, and Q4 + 30 = 01/30/2023, which is simple but would force me to go back at the end of every quarter and update the conditional format. So, how would I create a formula that would automatically evaluate whether the date that the financial statement was delivered falls within the current quarter + 30 days?

2.) I have a column that has the dates we received Tax Returns from customers, and we need to highlight any cell that has not been delivered to us before May 18th. The only issue here is that several customers have extensions which would make the date for some November 17th. How would I differentiate the cells that have an extension and those that don't so that those that have an extension would not be mistakenly highlighted?

Sorry for the long post, but thank you in advance.

 

[Tupe77]

1. I recommend making a cell with the current quarter. (Or should it be the previous quarter plus 30?) You compare the date to that. It is not worth defining a quarter in conditional formulation. Quarter cell can be manual input or formula.
2. The question is, how do you know the customer has an extension?

 

[Tupe77]

Does this formula work for step 1?

=AND($B2>=EOMONTH(TODAY()-30,MOD(3-MONTH(TODAY()-30),3)-3)+1,$B2<=EOMONTH(TODAY()-30,MOD(3-MONTH(TODAY()-30),3))+30)

 

[Tupe77]

I believe my formula does what you asked for, but it’s not necessarily what you want?
The problem is that it selects the current quarter. The result given by the formula always changes after a quarter +30 days.
The formula works for the period 04/1/2022 - 07/30/2022, but if you return to the same data on 08/01/2022 it will no longer highlight the dates between 04/1/2022 - 07/30/2022 as they do not belong to the current quarter + 30.

You will probably need to redesign the functionality you want?

 

[Samtk]

I believe my formula does what you asked for, but it’s not necessarily what you want?
The problem is that it selects the current quarter. The result given by the formula always changes after a quarter +30 days.
The formula works for the period 04/1/2022 - 07/30/2022, but if you return to the same data on 08/01/2022 it will no longer highlight the dates between 04/1/2022 - 07/30/2022 as they do not belong to the current quarter + 30.

You will probably need to redesign the functionality you want?

Hi, thank you for the quick response! So the goal of the formula is to highlight any cell that would be before this current quarter, and I suppose due to the fact that that extra 30 days from last quarter falls within this quarter, it does not really affect how the function will perform. So I guess at the most basic form of it, I need to highlight any date that would fall before the beginning of this quarter which would be 04/01/2022.

 

[Samtk]

I forgot to add, the end goal is to make it so nobody in the future needs to come in and update the formula to match this quarter.

 

[Tupe77]

My formula highlights cells whose date falls on the current quarter, or if the quarter has already changed, it highlights for 30 days cells whose date falls on the previous quarter.

The $ B2 address must be changed to match (first row with dates and correct column)

 

[Samtk]

Hi sorry,

misunderstanding on my end, which dates go in the first row?

 

[Tupe77]

=AND($B2>=EOMONTH(TODAY()-30,MOD(3-MONTH(TODAY()-30),3)-3)+1,$B2<=EOMONTH(TODAY()-30,MOD(3-MONTH(TODAY()-30),3))+30)

1. Switch address $B2 from formula to first cell address with date in "column that is full of dates that financial statements were delivered to us"
2. select "column that is full of dates that financial statements were delivered to us"
3. Conditional Formating -> New Rule -> Use a formula to determine which cells to format
4. Paste formula(step 1) to -> Format values where this formula is true:
5. Set the desired format from format
6. Apply

 

[Samtk]

Thanks so much! That tworked