Reminders for Quarterly Tasks

[realPrincessApril]

Hi all,

At work we have to complete a certain task quarterly for one type of client (let's call them Type Q) and semiannually for another type of client (Type S).

I'd like the cell in column C to highlight to remind me if the current quarter (for Type Q clients) or current half-year (for Type S clients) is within 15 days and I have either not entered a date (i.e., cell is blank, like in C4) or entered one from a prior quarter for Type Q clients or prior half-year for Type S clients. A half-year is defined as between Jan 1 and June 30 or between July 1 and Dec 31 each year. Q1 is Jan 1 to Mar 31, Q2 is Apr 1 to Jun 30, etc.

Client Name	Client Type	Task Last Completed
Joe	S	7/5/2021​
Jane	Q	6/20/2021​
Mary	S
Bob	Q	3/3/2020​

I realize I might be asking a lot but was hoping someone might be able to help with this doozy. Thank you for any time and consideration and have a nice rest of your day.

April

 

[realPrincessApril]

I have this implemented in my workbook. I marked your response above as solution. Thank you very much again Toadstool--so helpful. Take care for now.

 

[James Clear]

Yes, I believe I have correctly interpreted your quarters. The reason I made G1 =TODAY() is so I could put any date in G1 for testing purposes (but we could change all the formulae to =TODAY() once we're sure the formulae are correct).
This means you can take the mini-sheet I provided and type any date into G1 and check that it correctly predicts when the next quarter starts and when the current quarter started (same for half year).
Give it a try to check, for example, that when it's 11 November 2021 then the next quarter and half year start 1st Jan next year and the current quarter (I've said Last Q but could more properly be Current Q) started 1st October 2021 and current half year started 1st July 2021.


My challenge with the 15 day rule is not checking until 15 days before the next Q/S starts but still keeping ones with prior Q or S dates highlighted, as per your statement "Once highlighting turns on it means that the due date is either within 15 days or they are past due (stays highlighted to keep reminding them until they get current again).".

So let me try this:

1. If the current date is within 15 days of the end of a Q or S and the Task Last Completed is not within the current Q or S then it highlights with a gentle pastel yellow/amber.
2. Regardless of the current date if the Task Last Completed is earlier than the previous Q or S then it highlights with an angry red cell.

This approach requires that within the Conditional Format, Manage Rules that you make sure the angry red highlights come first so they don't become just pastel shades during the last 15 days of a quarter.

realPrincessApril.xlsx
	A	B	C	D	E	F	G
1	Client Name	Client Type	Task Last Completed			Today	11-Jul-21
2	Joe	S	05-Jul-21			Next Q	01-Oct-21
3	Jane	Q	20-Jun-21			Current Q	01-Jul-21
4	Mary	S				Next S	01-Jan-22
5	Bob	Q	03-Mar-20			Current S	01-Jul-21
6	Tim	Q	03-Mar-21
7	Jim	Q	01-Oct-21
8	Sarah	Q	30-Jun-21
9	Sally	Q	06-Jun-21
10	Bill	Q	09-Sep-21
11	User	Q	08-Aug-21
12	Paul	S	02-Feb-21
13	Peter	S	12-Dec-20
14
Sheet2

Cell Formulas
Range	Formula
G1	G1	=TODAY()
G2	G2	=DATE(YEAR($G$1)+INT(MONTH($G$1)/10),INDEX({4,4,4,7,7,7,10,10,10,1,1,1},MONTH($G$1)),1)
G3	G3	=EDATE(G2,-3)
G4	G4	=DATE(YEAR($G$1)+INT(MONTH($G$1)/7),INDEX({7,7,7,7,7,7,1,1,1,1,1,1},MONTH($G$1)),1)
G5	G5	=EDATE(G4,-6)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C999	Expression	=AND(A2<>"",B2="S",C2<EDATE($G$5,-6))	text	NO
C2:C999	Expression	=AND(A2<>"",B2="Q",C2<EDATE($G$3,-3))	text	NO
C2:C999	Expression	=AND(A2<>"",B2="S",$G$4-$G$1<=15,C2<$G$5)	text	NO
C2:C999	Expression	=AND(A2<>"",B2="Q",$G$2-$G$1<=15,C2<$G$3)	text	NO

Hi Toadstool,

Your formulae are great..

I have one doubt .can you please explain me the logic behind - INDEX({4,4,4,7,7,7,10,10,10,1,1,1} - what is that we are pursuing from this defined array and why 4,4,4 and not 5,5,5

sorry to ask you silly question, however seriously would like to know the logic behind this logic to increase my knowledge.

 

[James Clear]

Hi Toadstool,

Your formulae are great..

I have one doubt .can you please explain me the logic behind - INDEX({4,4,4,7,7,7,10,10,10,1,1,1} - what is that we are pursuing from this defined array and why 4,4,4 and not 5,5,5

sorry to ask you silly question, however seriously would like to know the logic behind this logic to increase my knowledge.

Also, Pls let me know what should I put if I have to get Next Month.

 

[Toadstool]

I have this implemented in my workbook. I marked your response above as solution. Thank you very much again Toadstool--so helpful. Take care for now.

You're welcome!
Thanks for the feedback.

 

[Toadstool]

I have one doubt .can you please explain me the logic behind - INDEX({4,4,4,7,7,7,10,10,10,1,1,1} - what is that we are pursuing from this defined array and why 4,4,4 and not 5,5,5
Also, Pls let me know what should I put if I have to get Next Month.

Hi James,
Let me break down the whole statement where $G$1 contains the today's date using =TODAY()

=DATE(YEAR($G$1)+INT(MONTH($G$1)/10),INDEX({4,4,4,7,7,7,10,10,10,1,1,1},MONTH($G$1)),1)

So I want the date of the next quarter start and the quarters are 1 Jan, 1 Apr, 1 Jul and 1 Oct each year.
The DATE function wants (year,month,day).

For YEAR I give it the current year plus the integer of current month divided by 10, so if the current month is Jan to Sep it will return zero but Oct to Dec will result in 1 which is added to give current year plus 1.

For the month parameter of DATE the INDEX takes the current month number and indexes into 4,4,4,7,7,7,10,10,10,1,1,1 so if it is month Jan to Mar it will return a 4 because the next quarter starts Apr. Current month Apr to Jun returns 7 for Jul, Jul to Sep gives 10 for Oct and if the current month is 10, 11 or 12 (Oct, Nov, Dec) then it gives me 1 for Jan.

The last DATE parameter is day so it's always a 1 as quarters always start on the 1st.


I'm not sure what you mean by Pls let me know what should I put if I have to get Next Month." so can you explain further?

 

[James Clear]

Hats off to you!! Very deep thinking.

Thanks a lot for your explanation and time, appreciate your assistance with this matter.

Lastly, my question is if instead of Q & S we have monthly frequency then how we can go ahead by using same methodology .

Hi James,
Let me break down the whole statement where $G$1 contains the today's date using =TODAY()

=DATE(YEAR($G$1)+INT(MONTH($G$1)/10),INDEX({4,4,4,7,7,7,10,10,10,1,1,1},MONTH($G$1)),1)

So I want the date of the next quarter start and the quarters are 1 Jan, 1 Apr, 1 Jul and 1 Oct each year.
The DATE function wants (year,month,day).

For YEAR I give it the current year plus the integer of current month divided by 10, so if the current month is Jan to Sep it will return zero but Oct to Dec will result in 1 which is added to give current year plus 1.

For the month parameter of DATE the INDEX takes the current month number and indexes into 4,4,4,7,7,7,10,10,10,1,1,1 so if it is month Jan to Mar it will return a 4 because the next quarter starts Apr. Current month Apr to Jun returns 7 for Jul, Jul to Sep gives 10 for Oct and if the current month is 10, 11 or 12 (Oct, Nov, Dec) then it gives me 1 for Jan.

The last DATE parameter is day so it's always a 1 as quarters always start on the 1st.


I'm not sure what you mean by Pls let me know what should I put if I have to get Next Month." so can you explain further?

 

[Toadstool]

Hats off to you!! Very deep thinking.

Thanks a lot for your explanation and time, appreciate your assistance with this matter.

Lastly, my question is if instead of Q & S we have monthly frequency then how we can go ahead by using same methodology .

So you want the start of the next month? That would be

=EOMONTH(TODAY(),0)+1

...but if you want to use the same methodology then use

=DATE(YEAR(TODAY())+INT(MONTH(TODAY())/12),INDEX({2,3,4,5,6,7,8,9,10,11,12,1},MONTH(TODAY())),1)

 

[James Clear]

So you want the start of the next month? That would be

=EOMONTH(TODAY(),0)+1

...but if you want to use the same methodology then use

=DATE(YEAR(TODAY())+INT(MONTH(TODAY())/12),INDEX({2,3,4,5,6,7,8,9,10,11,12,1},MONTH(TODAY())),1)

Thanks a lot for your assistance.

 

[James Clear]

Thanks a lot for your assistance.


Hi Toadstool,

I prepared this file exactly as suggested by you, however I thought one step further… is there any way with it we can automatically send an email to ourselves if due date is closed by our action date? So that user would not have to open this file on a daily basis.

pls help.

 

[Toadstool]

Hi James,

I don't think I told you how to make your file, I just explained a formula, but automatically sending an email is now in the realms of VBA and you'd need to open the sheet anyway to trigger the VBA.

I suggest you start a new thread as this one isn't related to automatically sending emails. Post some example data using XL2BB and I'm sure the VBA experts on the forum will assist.