Daily Counter Based on Criteria (Non VBA)

amoverton2

New Member
Joined
May 13, 2021
Messages
30
Office Version
  1. 2016
Platform
  1. 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").

test.xlsx
ABCDEF
1LAST CONTACTLAST CONTACT GRADEDAYS SPENT AT "A"DAYS SPENT AT "B"DAYS SPENT AT "C"DAYS SPENT AT "D"
230-Apr-2021B
325-Mar-2021D
429-Mar-2021D
54-May-2021A
614-Apr-2021C
76-May-2021A
826-Apr-2021B
919-Mar-2021D
1030-Mar-2021D
1123-Feb-2021D
123-May-2021A
134-May-2021A
1430-Mar-2021D
1513-Apr-2021C
1622-Mar-2021D
176-May-2021A
1827-Apr-2021B
197-Apr-2021D
2018-Apr-2021C
Sheet1
Cell Formulas
RangeFormula
B2:B20B2= 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
CellConditionCell FormatStop If True
A2:F20Cell Valuebetween TODAY()-31 and TODAY()-600textNO
A2:F20Cell Valuebetween TODAY()-21 and TODAY()-30textNO
A2:F20Cell Valuebetween TODAY()-11 and TODAY()-20textNO
A2:F20Cell Valuebetween TODAY() and TODAY()-10textNO



Thank you for your help!!
Adam
 

amoverton2

New Member
Joined
May 13, 2021
Messages
30
Office Version
  1. 2016
Platform
  1. Windows
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....
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Forum statistics

Threads
1,141,863
Messages
5,709,074
Members
421,613
Latest member
wyzco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top