Daily Counter Macro

amoverton2

New Member
Joined
May 13, 2021
Messages
21
Office Version
  1. 2016
Platform
  1. Windows
Hi All,

I have no clue about macro's so I'll start with that....

From a previous thread in which we couldn't make it work formula wise: Daily Counter Based on Criteria (Non VBA)

"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")."

I have no clue where to start other than posting I need help...

Thanks
Adam
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

vw412

Active Member
Joined
Dec 16, 2011
Messages
343
Office Version
  1. 2019
  2. 2016
  3. 2010
  4. 2007
Platform
  1. Windows
I have read this and the previous thread (unsuccessful). Some questions:

Does the date in a particular row column A ever change after it is entered initially?
When you work with this sheet do you merely add rows to the end?
The computed values in columns C thru F should update each time you open the workbook based on the current date?

Could you post the first sheet image from the other thread that you initially posted, but fill in the columns C thru F with the values you would expect to find there if it worked the way you want? I am a little uncertain of the goal for this exercise.

I am sure to have more questions but for now I want to understand exactly what you are trying to do.
 

amoverton2

New Member
Joined
May 13, 2021
Messages
21
Office Version
  1. 2016
Platform
  1. Windows
I have read this and the previous thread (unsuccessful). Some questions:

Does the date in a particular row column A ever change after it is entered initially?
When you work with this sheet do you merely add rows to the end?
The computed values in columns C thru F should update each time you open the workbook based on the current date?

Could you post the first sheet image from the other thread that you initially posted, but fill in the columns C thru F with the values you would expect to find there if it worked the way you want? I am a little uncertain of the goal for this exercise.

I am sure to have more questions but for now I want to understand exactly what you are trying to do.
- Does the date in a particular row column A ever change after it is entered initially?: Yes (should be multiple times, at least every 2 weeks over a 6-9 month period).
- When you work with this sheet do you merely add rows to the end? Yes (when the database I pull from (only I have access and cannot given others access) updates and new people are added).
- The computed values in columns C thru F should update each time you open the workbook based on the current date? Yes, based off the last contact date.

right now I'm having issues with XL2BB and capturing the mini sheet, so the picture will have to suffice...

Original-Update.jpg
 
Last edited:

Forum statistics

Threads
1,141,132
Messages
5,704,458
Members
421,351
Latest member
Sheogorath

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