Daily Counter Based on Criteria (Non VBA)

[amoverton2]

Hello All!

I'm looking for some help with a daily counter based on criteria of a cell.

In column "A" is a date for last contact with a person (this is conditionally formatted to change color based on how long it has been since "LAST CONTACT")

In column "B" a grade is given (A-D) based on column "A"'s date.
(Formula in each cell: = IF(AND(A2<=TODAY(),A2>=TODAY()-10),"A", IF(AND(A2<=TODAY()-11, A2>=TODAY()-20), "B", IF(AND(A2<=TODAY()-21, A2>=TODAY()-30), "C", IF(AND(A2<=TODAY()-31, A2>=TODAY()-600), "D",""))))

In columns "C" thru "F" (DAYS SPENT AT "A", "B", "C", "D") I would like to have a daily counter on which grade is in column "B" starting from May 1st 2021 (if this has to change, so be it) until today (and continuing beyond today).
Example: cell A2's date is 30APR2021, I should see 10 in column "A", 3 in column "B", 0 in columns "C", and "D"

I want the counter to continue going without losing any data if the date in column "A" is updated to today (just keep adding to the count even though there is a new date in column "A").

test.xlsx
	A	B	C	D	E	F
1	LAST CONTACT	LAST CONTACT GRADE	DAYS SPENT AT "A"	DAYS SPENT AT "B"	DAYS SPENT AT "C"	DAYS SPENT AT "D"
2	30-Apr-2021	B
3	25-Mar-2021	D
4	29-Mar-2021	D
5	4-May-2021	A
6	14-Apr-2021	C
7	6-May-2021	A
8	26-Apr-2021	B
9	19-Mar-2021	D
10	30-Mar-2021	D
11	23-Feb-2021	D
12	3-May-2021	A
13	4-May-2021	A
14	30-Mar-2021	D
15	13-Apr-2021	C
16	22-Mar-2021	D
17	6-May-2021	A
18	27-Apr-2021	B
19	7-Apr-2021	D
20	18-Apr-2021	C
Sheet1

Cell Formulas
Range	Formula
B2:B20	B2	= IF(AND(A2<=TODAY(),A2>=TODAY()-10),"A", IF(AND(A2<=TODAY()-11, A2>=TODAY()-20), "B", IF(AND(A2<=TODAY()-21, A2>=TODAY()-30), "C", IF(AND(A2<=TODAY()-31, A2>=TODAY()-600), "D",""))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:F20	Cell Value	between TODAY()-31 and TODAY()-600	text	NO
A2:F20	Cell Value	between TODAY()-21 and TODAY()-30	text	NO
A2:F20	Cell Value	between TODAY()-11 and TODAY()-20	text	NO
A2:F20	Cell Value	between TODAY() and TODAY()-10	text	NO

Thank you for your help!!
Adam

 

[Habtest]

Hi Adam, see if it works:

Book1
	A	B	C	D	E	F
1	LAST CONTACT	LAST CONTACT GRADE	DAYS SPENT AT "A"	DAYS SPENT AT "B"	DAYS SPENT AT "C"	DAYS SPENT AT "D"
2	5/6/2021	A	8	-	-	-
3	5/6/2021	A	8	-	-	-
4	5/4/2021	A	10	-	-	-
5	5/4/2021	A	10	-	-	-
6	5/3/2021	B	10	1	-	-
7	4/30/2021	B	10	4	-	-
8	4/27/2021	B	10	7	-	-
9	4/26/2021	B	10	8	-	-
10	4/18/2021	C	10	10	6	-
11	4/14/2021	C	10	10	10	-
12	4/13/2021	D	10	10	10	1
13	4/7/2021	D	10	10	10	7
14	3/30/2021	D	10	10	10	15
15	3/30/2021	D	10	10	10	15
16	3/29/2021	D	10	10	10	16
17	3/25/2021	D	10	10	10	20
18	3/22/2021	D	10	10	10	23
19	3/19/2021	D	10	10	10	26
20	2/23/2021	D	10	10	10	50
Sheet1

Cell Formulas
Range	Formula
B2:B20	B2	= IF(AND(A2<=TODAY(),A2>=TODAY()-10),"A", IF(AND(A2<=TODAY()-11, A2>=TODAY()-20), "B", IF(AND(A2<=TODAY()-21, A2>=TODAY()-30), "C", IF(AND(A2<=TODAY()-31, A2>=TODAY()-600), "D",""))))
C2:F20	C2	=MAX(MIN(CHOOSE(COLUMNS($C$1:C$1),10,10,10,570),TODAY()-CHOOSE(COLUMNS($C$1:C$1),0,10,20,30)-$A2),0)

 

[amoverton2]

Did you put that formula in C2 and copy it down and over?

 

[Habtest]

Exactly. Does it work?

 

[amoverton2]

So counts, but resets after a new date is placed in column A. Is there a way to get it to not do that and keep counting if column A is updated?

 

[Habtest]

I don't understand what the issue is. In each row the formula returns the result depending on the date, grade in the same row and nothing else. A newly placed date in column A should not affect other rows.

Can you specify what went wrong by Xl2bb?

 

[amoverton2]

From the sheet with the formula inputted and it sorted via newest to oldest.

In row 20 reads: 23-FEB-2021 | D | 10 | 10 | 10 | 50

test (1).xlsx
	A	B	C	D	E	F
1	LAST CONTACT	LAST CONTACT GRADE	DAYS SPENT AT "A"	DAYS SPENT AT "B"	DAYS SPENT AT "C"	DAYS SPENT AT "D"
2	6-May-2021	A	8	0	0	0
3	6-May-2021	A	8	0	0	0
4	4-May-2021	A	10	0	0	0
5	4-May-2021	A	10	0	0	0
6	3-May-2021	B	10	1	0	0
7	27-Apr-2021	B	10	7	0	0
8	26-Apr-2021	B	10	8	0	0
9	18-Apr-2021	C	10	10	6	0
10	15-Apr-2021	C	10	10	9	0
11	14-Apr-2021	C	10	10	10	0
12	13-Apr-2021	D	10	10	10	1
13	7-Apr-2021	D	10	10	10	7
14	30-Mar-2021	D	10	10	10	15
15	30-Mar-2021	D	10	10	10	15
16	29-Mar-2021	D	10	10	10	16
17	25-Mar-2021	D	10	10	10	20
18	22-Mar-2021	D	10	10	10	23
19	19-Mar-2021	D	10	10	10	26
20	23-Feb-2021	D	10	10	10	50
Sheet1

Cell Formulas
Range	Formula
B2:B20	B2	= IF(AND(A2<=TODAY(),A2>=TODAY()-10),"A", IF(AND(A2<=TODAY()-11, A2>=TODAY()-20), "B", IF(AND(A2<=TODAY()-21, A2>=TODAY()-30), "C", IF(AND(A2<=TODAY()-31, A2>=TODAY()-600), "D",""))))
C2:F20	C2	=MAX(MIN(CHOOSE(COLUMNS($C$1:C$1),10,10,10,570),TODAY()-CHOOSE(COLUMNS($C$1:C$1),0,10,20,30)-$A2),0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:B20	Cell Value	between TODAY()-31 and TODAY()-600	text	NO
A2:B20	Cell Value	between TODAY()-21 and TODAY()-30	text	NO
A2:B20	Cell Value	between TODAY()-11 and TODAY()-20	text	NO
A2:B20	Cell Value	between TODAY() and TODAY()-10	text	NO

Using a quick =now()-A20, there are 80 days between today and 23FEB21, which is also 10+10+10+50.

So the current "days spent at" count is
A: 10
B: 10
C: 10
D: 50

Now, here's my problem: each row represents a different person with a different person contacting that person. So the person contacting A20, contacts them yesterday, therefore the date of the last contact is now updated to 13-MAY-21.
row 20 reads : 13-MAY-2021 | A | 1 | 0 | 0 | 0

test (1).xlsx
	A	B	C	D	E	F
1	LAST CONTACT	LAST CONTACT GRADE	DAYS SPENT AT "A"	DAYS SPENT AT "B"	DAYS SPENT AT "C"	DAYS SPENT AT "D"
2	6-May-2021	A	8	0	0	0
3	6-May-2021	A	8	0	0	0
4	4-May-2021	A	10	0	0	0
5	4-May-2021	A	10	0	0	0
6	3-May-2021	B	10	1	0	0
7	27-Apr-2021	B	10	7	0	0
8	26-Apr-2021	B	10	8	0	0
9	18-Apr-2021	C	10	10	6	0
10	15-Apr-2021	C	10	10	9	0
11	14-Apr-2021	C	10	10	10	0
12	13-Apr-2021	D	10	10	10	1
13	7-Apr-2021	D	10	10	10	7
14	30-Mar-2021	D	10	10	10	15
15	30-Mar-2021	D	10	10	10	15
16	29-Mar-2021	D	10	10	10	16
17	25-Mar-2021	D	10	10	10	20
18	22-Mar-2021	D	10	10	10	23
19	19-Mar-2021	D	10	10	10	26
20	13-May-2021	A	1	0	0	0
Sheet1

Cell Formulas
Range	Formula
B2:B20	B2	= IF(AND(A2<=TODAY(),A2>=TODAY()-10),"A", IF(AND(A2<=TODAY()-11, A2>=TODAY()-20), "B", IF(AND(A2<=TODAY()-21, A2>=TODAY()-30), "C", IF(AND(A2<=TODAY()-31, A2>=TODAY()-600), "D",""))))
C2:F20	C2	=MAX(MIN(CHOOSE(COLUMNS($C$1:C$1),10,10,10,570),TODAY()-CHOOSE(COLUMNS($C$1:C$1),0,10,20,30)-$A2),0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:B20	Cell Value	between TODAY()-31 and TODAY()-600	text	NO
A2:B20	Cell Value	between TODAY()-21 and TODAY()-30	text	NO
A2:B20	Cell Value	between TODAY()-11 and TODAY()-20	text	NO
A2:B20	Cell Value	between TODAY() and TODAY()-10	text	NO

Why is the 20 row not reading: 13-MAY-2021 | A | 11 | 10 | 10 | 50 ???
The counter reset with the date change. I'd like the numbers to continue counting even if columns A and B change. If I need more columns to place data in order to continue to count so be it, but I was hoping to contain it to the cells in the mini-sheet.

Thanks for your help!!
Adam

 

[Habtest]

If I understand correctly May 13 in a new line is expected to return a different result from when May 13 overwrites an existing one. I don't think it's possible to make Excel "remember" previous results based on values that are not there anymore, at least not that I know of with formula. You'd have to change the layout by adding rows to store all historical contact dates (as many columns as the number of possible contacts) and use another formula to calculate. If so, we can try after a new layout is uploaded.
Another option is use Macro, if you open to try it could minimize layout change, though I'm not an expert there you'd need to await help from other members.

 

[amoverton2]

Do you know of a formula that will store information (most likely on another sheet, of the current count) in a cell and when the original cell ("last contact date") is updated, the counter moves to the next column (same row A20, B20, etc). This way, the reset is not an issue?

 

[Habtest]

Also not that I know any formula that can be triggered by an event of delete or overwrite to copy from another cell, still sounds like a Macro task.

Another way to go is to maintain the "date" database in another sheet, and replace the current column A with a formula (MAX or INDEX(MATCH)) to return the most recent date, this way you would need to maintain the contact dates in that database/sheet and be able to keep this one as is.

Above all, if formula is the only option, I believe you'd have to store all the historical dates somewhere in the workbook.