Don't update formula after certain date

surkdidat

Active Member
Joined
Oct 1, 2011
Messages
352
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
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,055
Office Version
365
Platform
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
352
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,055
Office Version
365
Platform
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
352
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,055
Office Version
365
Platform
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
352
“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,055
Office Version
365
Platform
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,095,235
Messages
5,443,277
Members
405,223
Latest member
Industrial_Eng_SA

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top