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

 

[amoverton2]

Yeah, this is all beyond my current mental capabilities for excel. Thank you for your help with the daily up counter!! Here's to hoping someone can figure the reset issue....