# Work in progress and work completed

#### Stclements1

##### Board Regular
Cell B2= Todays Date
Cell B11= Start Date
Cell C11 = End Date
Cell D11= Days Remaining

At present cell d11(gives the value of end date of the task - todays date (c11-b2)
What I want to do is if the task is completed then cell g11 will have a 0 (implying task completed)
and cell d11 will no longer do the calculation c11-b2 but will retain the date at the point that cell g11 becomes zero.

Is this possible?

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### Joe4

What I want to do is if the task is completed then cell g11 will have a 0 (implying task completed)
What determines if a task has been completed? Is it just an End Date in cell C11 that is in the past?
Maybe an alteration to this formula from:
Code:
``=C11-B2``
to
Code:
``=MAX(C11-B2,0)``
Is that what you are looking for?
If not, please explain in more details (not sure what G11 is or where it comes into play here).

#### Stclements1

##### Board Regular
What determines if a task has been completed? Is it just an End Date in cell C11 that is in the past?
Maybe an alteration to this formula from:
Code:
``=C11-B2``
to
Code:
``=MAX(C11-B2,0)``
Is that what you are looking for?
If not, please explain in more details (not sure what G11 is or where it comes into play here).
Thank you for the reply, sorry I didn't make it as clear as I might.

In cells B11 and C11 are the start and end date of a task in cell D11 is the formula C11 (the end date) - B2 (Todays date, written as, today()
In G11, when the task has been completed the value of this cell is 0 at which point I want the calculation in cell D2 to cease so that it no longer updates from cell B2
By freezing the number at the point the task is completed I have the start and end date in cells B11 and C11 and D11 show 0 as there are no longer any days remaining on this task. That information will come from cell G11 which when its value is 0 then the task is completed.

I hope this helps but please feel free for further explanation if needed. I would try to attach the spreadsheet but it comes across as jumbled when attached.

#### Joe4

So which cell do you want "frozen in time"? B2 or D2?

How is the value in G11 being updated to 0? Is there a formula in that cell, or is it being manually updated?
If a formula, what is the formula?

Is B2 simply the following formula?
Code:
``=D2``

#### Stclements1

##### Board Regular
So which cell do you want "frozen in time"? B2 or D2?

How is the value in G11 being updated to 0? Is there a formula in that cell, or is it being manually updated?
If a formula, what is the formula?

Is B2 simply the following formula?
Code:
``=D2``
The value 0 in G11 is manually inputted at the completion date of the task at which point the calculation C11 (projected end of task) - B2(Todays date) ceases to operate and the days remaining which is D11 no longer updates and remains static
The formula in B2 is simply todays date which updates daily by using the formula B2=today()

Todays date B2
Task days remaining D11 (formula in D11 = C11- B2
G11 Has the value 0 when task is completed

So whilst the task is in progress D11 will change daily as it takes todays date away from the end date
What I want to do is when the task is finished 0 is inputted into G11 and D11 stops using the formula C11-B2

Appreciate your help and hope this helps

#### Joe4

In order to change a cell from a formula to a hard-coded value automatically requires using VBA. Below is code that will do that.
Just right-click on the sheet tab name at the bottom of the sheet you want to apply this to, select "View Code", and paste this VBA code into the resulting VB Editor window:
Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)

'   See if value in cell G11 is updated
If Not Intersect(Target, Range("G11")) Is Nothing Then
'       See if cell G11 set to 0
If Range("G11") = 0 Then
'           Turn D11 from formula to hard-coded value
Range("D11").Value = Range("D11").Value
End If
End If

End Sub``````
As long as VBA code is enabled, this code will automatically turn D11 from a formula to a hard-coded value when cell G11 is manually set to 0.

#### Stclements1

##### Board Regular
In order to change a cell from a formula to a hard-coded value automatically requires using VBA. Below is code that will do that.
Just right-click on the sheet tab name at the bottom of the sheet you want to apply this to, select "View Code", and paste this VBA code into the resulting VB Editor window:
Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)

'   See if value in cell G11 is updated
If Not Intersect(Target, Range("G11")) Is Nothing Then
'       See if cell G11 set to 0
If Range("G11") = 0 Then
'           Turn D11 from formula to hard-coded value
Range("D11").Value = Range("D11").Value
End If
End If

End Sub``````
As long as VBA code is enabled, this code will automatically turn D11 from a formula to a hard-coded value when cell G11 is manually set to 0.
Many many thanks for that, it works perfectly, so grateful. Sorry to ask but one more question if you don't mind if I wanted the coded to work in multiple cells such as G11:GG25 AND d11:D25 would I change line two from simpply G11 to G11:G25 and line 6 from simply D11 to D11:D25

#### Joe4

We need to slightly alter our approach in checking against a multi-cell range. Try this:
Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range
Dim cell As Range

'   See which cells in G11:G25 updated
Set rng = Intersect(Target, Range("G11:G25"))

'   Exit if no cells updated in that range
If rng Is Nothing Then Exit Sub

'   Loop through updated cells in G11:G25
For Each cell In rng
'       See if column G set to 0
If cell = 0 Then
'           Turn column D to hard-coded value
cell.Offset(0, -3).Value = cell.Offset(0, -3).Value
End If
Next cell

End Sub``````

#### Stclements1

##### Board Regular
We need to slightly alter our approach in checking against a multi-cell range. Try this:
Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range
Dim cell As Range

'   See which cells in G11:G25 updated
Set rng = Intersect(Target, Range("G11:G25"))

'   Exit if no cells updated in that range
If rng Is Nothing Then Exit Sub

'   Loop through updated cells in G11:G25
For Each cell In rng
'       See if column G set to 0
If cell = 0 Then
'           Turn column D to hard-coded value
cell.Offset(0, -3).Value = cell.Offset(0, -3).Value
End If
Next cell

End Sub``````
Many thanks for the assistance, works perfectly. A great help thanks once again.

You are welcome.

1,102,029
Messages
5,484,274
Members
407,436
Latest member
Szafranski

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...