Don't update formula after certain date

[surkdidat]

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

 

[steve the fish]

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]

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]

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]

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]

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]

“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]

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.