Daily Counter Based on Criteria (Non VBA)

amoverton2

New Member
Joined
May 13, 2021
Messages
11
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Habtest

Board Regular
Joined
Jul 30, 2020
Messages
180
Office Version
  1. 2016
Platform
  1. Windows
Hi Adam, see if it works:
Book1
ABCDEF
1LAST CONTACTLAST CONTACT GRADEDAYS SPENT AT "A"DAYS SPENT AT "B"DAYS SPENT AT "C"DAYS SPENT AT "D"
25/6/2021A8---
35/6/2021A8---
45/4/2021A10---
55/4/2021A10---
65/3/2021B101--
74/30/2021B104--
84/27/2021B107--
94/26/2021B108--
104/18/2021C10106-
114/14/2021C101010-
124/13/2021D1010101
134/7/2021D1010107
143/30/2021D10101015
153/30/2021D10101015
163/29/2021D10101016
173/25/2021D10101020
183/22/2021D10101023
193/19/2021D10101026
202/23/2021D10101050
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",""))))
C2:F20C2=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

New Member
Joined
May 13, 2021
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
Did you put that formula in C2 and copy it down and over?
 

Habtest

Board Regular
Joined
Jul 30, 2020
Messages
180
Office Version
  1. 2016
Platform
  1. Windows
Exactly. Does it work?
 

amoverton2

New Member
Joined
May 13, 2021
Messages
11
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Jul 30, 2020
Messages
180
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
May 13, 2021
Messages
11
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
ABCDEF
1LAST CONTACTLAST CONTACT GRADEDAYS SPENT AT "A"DAYS SPENT AT "B"DAYS SPENT AT "C"DAYS SPENT AT "D"
26-May-2021A8000
36-May-2021A8000
44-May-2021A10000
54-May-2021A10000
63-May-2021B10100
727-Apr-2021B10700
826-Apr-2021B10800
918-Apr-2021C101060
1015-Apr-2021C101090
1114-Apr-2021C1010100
1213-Apr-2021D1010101
137-Apr-2021D1010107
1430-Mar-2021D10101015
1530-Mar-2021D10101015
1629-Mar-2021D10101016
1725-Mar-2021D10101020
1822-Mar-2021D10101023
1919-Mar-2021D10101026
2023-Feb-2021D10101050
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",""))))
C2:F20C2=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
CellConditionCell FormatStop If True
A2:B20Cell Valuebetween TODAY()-31 and TODAY()-600textNO
A2:B20Cell Valuebetween TODAY()-21 and TODAY()-30textNO
A2:B20Cell Valuebetween TODAY()-11 and TODAY()-20textNO
A2:B20Cell Valuebetween TODAY() and TODAY()-10textNO


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
ABCDEF
1LAST CONTACTLAST CONTACT GRADEDAYS SPENT AT "A"DAYS SPENT AT "B"DAYS SPENT AT "C"DAYS SPENT AT "D"
26-May-2021A8000
36-May-2021A8000
44-May-2021A10000
54-May-2021A10000
63-May-2021B10100
727-Apr-2021B10700
826-Apr-2021B10800
918-Apr-2021C101060
1015-Apr-2021C101090
1114-Apr-2021C1010100
1213-Apr-2021D1010101
137-Apr-2021D1010107
1430-Mar-2021D10101015
1530-Mar-2021D10101015
1629-Mar-2021D10101016
1725-Mar-2021D10101020
1822-Mar-2021D10101023
1919-Mar-2021D10101026
2013-May-2021A1000
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",""))))
C2:F20C2=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
CellConditionCell FormatStop If True
A2:B20Cell Valuebetween TODAY()-31 and TODAY()-600textNO
A2:B20Cell Valuebetween TODAY()-21 and TODAY()-30textNO
A2:B20Cell Valuebetween TODAY()-11 and TODAY()-20textNO
A2:B20Cell Valuebetween TODAY() and TODAY()-10textNO


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

Board Regular
Joined
Jul 30, 2020
Messages
180
Office Version
  1. 2016
Platform
  1. Windows
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

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

Board Regular
Joined
Jul 30, 2020
Messages
180
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,136,304
Messages
5,674,973
Members
419,537
Latest member
ucatchy

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