Stop increasing Today() by each day if cell contains specific text THEN stop count & save value

[TTUK]

Hi all,

I am wanting to have the spreadsheet increase each day by day by using today(), but when 'Under Offer' has been entered into a cell I want the count to stop, and for it to tell me how long since the Start Date and the count of today() it has been without increase by +1 the next day.

My spreadsheet is as such.
TODAY() is entered into H$2$
The START DATE is entered into RANGE F9:F100
The AGE is entered into RANGE H9:H100
The "UNDER OFFER" text is entered into RANGE P9:P100

The place where I want it to return and tell me how long the count has been going for is RANGE J9:J100 and I don't want this to increase as the TODAY() formula will continue on each day...

Hope this makes sense.
Please let me know if you require further explanations from me

Thanks, TTUK

 

[Chrisdontm]

A hint of an answer... If you had a button that said "Enter" once the date was needed... The macro assigned to that button would copy the date in the cell where the TODAY() is located and paste it's "value only" into another cell, then copy it elsewhere if needed... I had to do that once... I hope that helps you get there...

 

[TTUK]

Hi Chrisdontm,

I have made the solution work another way.
I have used a VBA script whereby, it copies the AGE in to the column next to it, as values, not the formula of TODAY()-START DATE
So now each day I have the static values, and if the RANGE in P9:P100 = "UNDER OFFER" it uses the VBA VALUE instead of the continuing formula...

Hope this makes sense and helps anyone else who is trying to do something like I was....

Regards, TTUK

 

[Sixthsense]

Use worksheet change event to monitor the "UNDER OFFER" column and copy paste the concerned row data formula cell as values.