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]

full disclosure: It didn't seem like this was getting much traction here, so I have now tried posting this question (with some clarifications) at Excel Forum.

 

[Toadstool]

Hi realPrincessApril,

Please clarify a couple of things. Let's just look at Q type clients and assume the date is 20 December 2021, so the next quarter begins 1 January 2022.

1. Do you want the highlight to be in effect from 17 December 2021 through 31 December 2021 or 16 January 2022?
2. If the client has a date of 20 October 2021 then they are in the current quarter so do I highlight if they are before the current quarter date which started 1 October 2021 or do I highlight because they are before the date of the new quarter 1 January 2022?

 

[realPrincessApril]

Hi Toadstool. Thanks for your reply, and those are great questions.

1. Highlight would start 15 days prior to end of Q4 2021. Highlight would stay there permanently until the worker updates the date to whatever quarter is current. So let's say user has entered 10/3/2021. Cell would not highlight until 15 days prior to end of Q4 2021. Let's say worker continues to not update this task throughout Q1 of 2022 (stays highlighted). Let's say user even missed Q1 2022 (highlighted the whole time), and they finally enter a new date of 4/3/2022--cell would unhighlight at that point and would stay unhighlighted through all of Q2 2022, even at the end of the quarter, because the worker did the task in Q2 2022. Cell would only begin to highlight again at end of Q3 2022, assuming user has not updated the task again. T

2. I might have accidentally covered this question in my response to Q1. As long as the cell has a date within the current quarter (for Type Q clients), no highlighting needed, even if we are coming to quarter close. Cell only begins to highlight once worker is at risk for not entering a current quarter date and the current quarter is coming to a close. So if we prtend today is 12/20/21 and user has entered 10/20/21, no highlighting for rest of Q4 2021 because a Q4 date was logged. Still no highlighting beginning in 2022 until 15 days prior to end of Q1, if user does not update the date by then. 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).

I hope I'm clarifying and not making it more confusing. Just let me know if more context and examples would be helpful. Thank you again I know this is complex. I have done a bunch of formulas for the reminders in my sheet, but this one is beyond me.

 

[Toadstool]

I'm sorry but I am a bear of very little brain and I'm just not following, especially the 15 day rule.

Here's what I was testing but I don't think it's what you wanted.

realPrincessApril.xlsx
	A	B	C	D	E	F	G
1	Client Name	Client Type	Task Last Completed			Today	09-Jul-21
2	Joe	S	05-Jul-21			Next Q	01-Oct-21
3	Jane	Q	20-Jun-21			Last Q	01-Jul-21
4	Mary	S				Next S	01-Jan-22
5	Bob	Q	03-Mar-20			Last 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
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<$G$5)	text	NO
C2:C999	Expression	=AND(A2<>"",B2="Q",C2<$G$3)	text	NO

 

[realPrincessApril]

Hi Toadstool,

Thank you for your thoughtful response and all the work you put into that. Really very nice of you.

The quarter and half-year periods are fixed. So, for quarters, each year:
Q1 begins Jan 1 and ends Mar 31
Q2 begins Apr 1 and ends Jun 30
Q3 begins Jul 1 and ends Sept 30
Q4 begins Oct 1 and ends Dec 31

For the half-years, each year:
Half-year 1 begins Jan 1 and ends Jun 30
Half-year 2 begins Jul 1 and ends Dec 31

If I'm looking at your formulas correctly (big if with my own feeble brain!), I think this changes things. Please correct me if I'm wrong.

Also, if this helps for context: At work, we have a flow sheet in each client's paper binder (yes, we still are required to keep them in addition to our electronic charts, yikes). On that flow sheet, we literally have to check a box for whether we did the task in each quarter that year (or each half-year for those clients). There are of course lots of tasks like this. So I'm building the Excel spreadsheet to set up reminders to tell the worker like, hey you didn't do this task yet in this quarter or half year, and the quarter (or half-year) is coming to a close, so you better do it fast! That's what I mean by the 15 days part (to start highlighting the cell red a couple weeks before the quarter ends so that the user sees time is running out). The highlighting would then go away once the user enters a date within the current quarter or half-year depending on the type of client. If the user then doesn't do the task again in the next quarter or half-year, the highlighting doesn't come back until the end of that time period, to remind the worker that it's time to complete that task for that client again.

Does that help?

 

[Toadstool]

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

 

[realPrincessApril]

Ah I see, and yes, your explanation is right on. I'm playing around with the sample now (and your idea to have a date for testing is super helpful--I wasn't sure how to do that either).

 

[realPrincessApril]

Alrighty, first, you are amazing! This seems to work very well.

I'm attaching an updated sheet, in which I added a few conditional rules. You were on my wavelength with color schemes (including angry red lol). I'm handling the times and coloring with yellow/orange/red (progressively angrier) reminders as the due date approaches.

Expression	My rough understanding on when each highlights	Format Output
=AND(A2<>"",B2="S",C2<EDATE($G$5,-6))	if blank or contains date from a previous half-year, for S clients	Red Fill (angry red because the task was either never done or it's really overdue)
=AND(A2<>"",B2="Q",C2<EDATE($G$3,-3))	if blank or contains date from a previous quarter, for Q clients	Red Fill (same reasoning)
=AND(A2<>"",B2="S",$G$4-$G$1<=10,C2<$G$5)	If cell's date is prior to current half-year and we are within 10 days of end of current half-year, for S clients	Also Red (to indicate we are getting very close to the deadline)
=AND(A2<>"",B2="Q",$G$2-$G$1<=7,C2<$G$3)	If cell's date is prior to current quarter and we are within 7 days of end of current quarter, for C clients	Red (same reasoning)
=AND(A2<>"",B2="S",$G$4-$G$1<=20,C2<$G$5)	Old date and within 20 days of half-year end for S folks	Orange
=AND(A2<>"",B2="Q",$G$2-$G$1<=14,C2<$G$3)	Old date and within 14 days of quarter end for Q folks	Orange
=AND(A2<>"",B2="S",$G$4-$G$1<=30,C2<$G$5)	And some milder-colored warnings	Yellow
=AND(A2<>"",B2="Q",$G$2-$G$1<=21,C2<$G$3)		Yellow

So if I didn't mess anything up, I hope you don't mind a couple questions on implementation:

1. Do I replace $G$1 with TODAY() in all instances? I don't even mind leaving G1 as a reference so that I retain the handy testing feature in the future, but I wonder if it slows down the sheet (I have about 15 columns of reminders and there will be about 30-60 active client rows at any given time).

2. Do you recommend leaving the reference cells in a hidden column in the sheet? (or are we putting all of the G* values in the formulas with those long strings from your Cell Formulas table above)? In the former case, I would probably put the little reference table in a separate sheet in the workbook called "Lookup Tables," where I have some other stuff like that.


Thank you again this is really awesome.

Minisheet Q and S Updated.xlsx
	A	B	C	D	E	F	G
1	Client Name	Client Type	Task Last Completed			Today	12/17/2021
2	Joe	S	12/11/2021			Next Q	1/1/2022
3	Jane	Q	10/10/2021			Current Q	10/1/2021
4	Mary	S				Next S	1/1/2022
5	Bob	Q	7/1/2021			Current S	7/1/2021
6	Tim	Q	3/3/2021
7	Jim	Q	10/1/2021
8	Sarah	Q	6/30/2021
9	Sally	Q	6/6/2021
10	Bill	Q	9/9/2021
11	User	Q	8/8/2021
12	Paul	S	2/2/2021
13	Peter	S	12/12/2020
Sheet1

Cell Formulas
Range	Formula
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:C13	Expression	=AND(A2<>"",B2="S",C2<EDATE($G$5,-6))	text	NO
C2:C13	Expression	=AND(A2<>"",B2="Q",C2<EDATE($G$3,-3))	text	NO
C2:C13	Expression	=AND(A2<>"",B2="S",$G$4-$G$1<=10,C2<$G$5)	text	NO
C2:C13	Expression	=AND(A2<>"",B2="Q",$G$2-$G$1<=7,C2<$G$3)	text	NO
C2:C13	Expression	=AND(A2<>"",B2="S",$G$4-$G$1<=20,C2<$G$5)	text	NO
C2:C13	Expression	=AND(A2<>"",B2="Q",$G$2-$G$1<=14,C2<$G$3)	text	NO
C2:C13	Expression	=AND(A2<>"",B2="S",$G$4-$G$1<=30,C2<$G$5)	text	NO
C2:C13	Expression	=AND(A2<>"",B2="Q",$G$2-$G$1<=21,C2<$G$3)	text	NO

 

[Toadstool]

realPrincessApril,

I like your table. The AND means that all conditions must be true for the highlight to appear.
You must apply the formula starting at row 2 because the formulae all start with row 2 so any other starting row would offset. I see your sample is down to row 13 but I did down to row 999 and you should apply down to the last row you'll ever use.

You've said "is blank" but actually it's is not blank. The A2<>"" is because an empty cell is treated as zero which for a date is 1 January 1900 so only if the date is empty we do not want to highlight it.

EDATE lets you take a date and specify plus or minus months to calculate a new date, so yes, C2<EDATE($G$5,-6) will be TRUE and cause a highlight if the date is less than six months before the current half year start for S type. Your changes and interpretation look correct.

Your questions:
1. You can do the replace of $G$1 with TODAY() but personally I wouldn't. A quick check of TODAY() or a reference to $G$1 for 100,000 rows both finished in no more than a second so no timing considerations and yes, it makes testing easier if you make further changes.

2. I absolutely agree that keeping the Next/Current Q/S calculations as single cells makes sense. Moving them to existing "Lookup Tables" sounds a good idea. If you create a new sheet on the sample, select F1 to G5, Ctrl-X then in the new sheet Ctrl-V you should find that the Conditional Format statements now automatically refer to the new sheet.

It sounds like you've got it sussed!