Work in progress and work completed

[Stclements1]

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?

 

[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:

=C11-B2

to

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

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:

=C11-B2

to

=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?

=D2

 

[Stclements1]

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?

=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 start date B11
Task end date C11
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:

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]

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:

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:

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]

We need to slightly alter our approach in checking against a multi-cell range. Try this:

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.

 

[Joe4]

You are welcome.