Don't update formula after certain date

surkdidat

Active Member
Joined
Oct 1, 2011
Messages
370
Office Version
  1. 2016
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
Joined
Oct 20, 2009
Messages
8,388
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 1, 2011
Messages
370
Office Version
  1. 2016
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
Joined
Oct 20, 2009
Messages
8,388
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 1, 2011
Messages
370
Office Version
  1. 2016

ADVERTISEMENT

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
Joined
Oct 20, 2009
Messages
8,388
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 1, 2011
Messages
370
Office Version
  1. 2016
“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
Joined
Oct 20, 2009
Messages
8,388
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

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