Work in progress and work completed
Results 1 to 10 of 10

Thread: Work in progress and work completed
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Work in progress and work completed

    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?

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,086
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Work in progress and work completed

    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).
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Sep 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Work in progress and work completed

    Quote Originally Posted by Joe4 View Post
    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.

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,086
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Work in progress and work completed

    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
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    New Member
    Join Date
    Sep 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Work in progress and work completed

    Quote Originally Posted by Joe4 View Post
    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

  6. #6
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,086
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Work in progress and work completed

    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.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    New Member
    Join Date
    Sep 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Work in progress and work completed

    Quote Originally Posted by Joe4 View Post
    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

  8. #8
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,086
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Work in progress and work completed

    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
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  9. #9
    New Member
    Join Date
    Sep 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Work in progress and work completed

    Quote Originally Posted by Joe4 View Post
    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.

  10. #10
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,086
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Work in progress and work completed

    You are welcome.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •