# Don't update formula after certain date

#### surkdidat

##### Active Member
I fear there is not going to be an answer on this, but I have a formula where it counts values between certain dates, however, as this is for a weekly stat sheet, I want the calculation NOT to update after a certain date.. ie if the stats are for week commencing 1A April, after the 8 April, I do not want that row of stats to be affected by changes made in the main spreadsheet

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### steve the fish

##### Well-known Member
That would depend on the data. You cant stop a formula recalculating because of the date but you may be able to make it calculate the same thing over and over once a date has passed.

#### surkdidat

##### Active Member
What I have is the following, Column D being the date, and column I the status.

The status is updated as the work item moves along, and is just one cell (not multiple with dates for the various stages)

=COUNTIFS('WIP'!\$D2:\$D1000,">="&'Weekly Stats'!\$B10,'WIP'!\$D2:\$D1000,"<"&'Weekly Stats'!\$B11,'WIP'!\$I2:\$I1000,D9)

That would depend on the data. You cant stop a formula recalculating because of the date but you may be able to make it calculate the same thing over and over once a date has passed.

#### steve the fish

##### Well-known Member
I presume 'Weekly Stats' cell B10 and B11 are dates so you already stop the formula changing according to those cells. If you change those cells then the formula changes.

#### surkdidat

##### Active Member

In "weekly stats", I have the wc dates in Column B, ie 1 April, 8 April, 15 April etc.
On WIP in "Cell I" I have the status' "New In, WIP, Approved, Rejected" etc
On WIP in "Cell J" I have the status date, so it could have been "New In" on 1 April, but on 8 April it changes to "WIP", but on the stats sheet then the historic stats for wc 1 April will not be accurate as the "new in" (in this example) won't reflect the correct "New In" for that date

#### steve the fish

##### Well-known Member
How can i know your processes? You need to explain better as i have no picture in my mind as to what you mean or what you need.

#### surkdidat

##### Active Member
“WIP” Tab
------Col I ----- | ----- Col J ----- |
---(STATUS)---|-(Status Date--|
New In---------| 01/04/2019
New In---------| 01/04/2019
WIP ------------| 05/04/2019
WIP ------------| 05/04/2019
Approved ----| 07/04/2019
Rejected ------ | 07/04/2019

So, on Stats Sheet
----DATE----- | --- NEW IN --- |--- WIP ---|---Approved---|--Rejected--|
01/04/2019--| -------2-------|-------2-------|--------1---------|----0---------|

However, on 8 April the 2 “New In” Change to “WIP”, so thespreadsheet now looks like this:

WIP ------------| 08/04/2019
WIP ------------| 08/04/2019
WIP ------------| 05/04/2019
WIP ------------| 05/04/2019
Approved ----|07/04/2019
Rejected ------ | 07/04/2019

On the Stats Sheet it now reads

----DATE----- | --- NEW IN --- |--- WIP---|---Approved---|--Rejected--|
01/04/2019--| -------0-------|-------2-------|--------1---------|----0---------|
01/04/2019--| -------0-------|-------2-------|--------0---------|----0---------|

But, I don’t want the stats for the week commencing 1 Aprilto change, as it should still reflect there were 2 items “New In” That week

Hope that makes it clearer!

With the current formula, it now

How can i know your processes? You need to explain better as i have no picture in my mind as to what you mean or what you need.

#### steve the fish

##### Well-known Member
If you change your data sheet then you have to think of rules to supply to the countifs. If you cant think of a rule then you cant do it. Also if you require help please paste in a usable format. You cant paste what you have supplied into excel.

Replies
0
Views
167
Replies
6
Views
292
Replies
0
Views
48
Replies
16
Views
468
Replies
0
Views
232

1,129,479
Messages
5,636,575
Members
416,925
Latest member
malamutus

### 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.

### Which adblocker are you using?

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

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