amoverton2
Board Regular
- Joined
- May 13, 2021
- Messages
- 77
- Office Version
- 2016
- Platform
- Windows
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").
Thank you for your help!!
Adam
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