Work in progress and work completed

Stclements1

Board Regular
Joined
Sep 15, 2018
Messages
105
Office Version
365
Platform
Windows
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?
 

Some videos you may like

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,642
Office Version
365
Platform
Windows
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
Joined
Sep 15, 2018
Messages
105
Office Version
365
Platform
Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,642
Office Version
365
Platform
Windows
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
Joined
Sep 15, 2018
Messages
105
Office Version
365
Platform
Windows
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 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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,642
Office Version
365
Platform
Windows
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
Joined
Sep 15, 2018
Messages
105
Office Version
365
Platform
Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,642
Office Version
365
Platform
Windows
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
Joined
Sep 15, 2018
Messages
105
Office Version
365
Platform
Windows
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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,642
Office Version
365
Platform
Windows
You are welcome.
 

Watch MrExcel Video

Forum statistics

Threads
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...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top