Duration countdown (single input)

[mharchitect]

Greetings,

As a policy in other forums, I am posting the same thread below perhaps you have a proper solution for the query

https://www.excelforum.com/excel-pr...ation-countdown-single-input.html#post5084896

ThanksGreetings,

As a policy in other forums, I am posting the same thread below perhaps you have a proper solution for the query

https://www.excelforum.com/excel-pr...ation-countdown-single-input.html#post5084896

Thanks

 

[Yongle]

Test this to see if it does what you want

With worksheet laid out like this

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​
1​	Task	Planned	Remaining	Start Date
2​	t1	11​	2​	15/03/2019​
3​	t2	15​	11​	20/03/2019​
4​	t3	10​	10​	24/03/2019​

Sheet: NameOfSheet

CODE

In THISWORKBOOK module

Private Sub Workbook_Open()
    Call UpdateValues
End Sub

In SHEET module

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Start As Range
    If Target.Row = 1 Or Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
        Set Start = Target.Offset(, 2)
        Start.NumberFormat = "dd/mm/yyyy"
        If Start = "" Then Start = Date
        Target.Offset(, 1) = CLng(Target + Start - Date)
    End If
End Sub

In STANDARD module

Sub UpdateValues()
    Dim Ws As Worksheet, Cel As Range, Rng As Range, EndDate As Long
    Set Ws = Sheets("NameOfSheet")
    Set Rng = Ws.Range("A2", Ws.Range("A" & Rows.Count).End(xlUp))
    For Each Cel In Rng
        EndDate = Cel.Offset(, 3) + Cel.Offset(, 1)
        With Cel.Offset(, 2)
            If EndDate >= Date Then .Value = CLng(EndDate - Date) Else .Value = "overdue"
        End With
    Next Cel
End Sub

 

[mharchitect]

This is much better, thank you. But both columns C & D are static. When I change the machine date, the new task takes the new date as Sart date and the old ones are not changed, till now this is perfect.
But remaining dates of the old tasks are not updated when I change the machine date. Can we make Column D static and Column C dynamic so that it will be linked to Today () function to calculate the new remaining days when the date changes?

 

[Yongle]

I do not understand why you have a problem
- running UpdateValues should do everything you need
- explanations below should help you get there

But both columns C & D are static

column C is not static
- it ALWAYS changes if column B changes
- you can run UpdateValues whenever you want to recalculate column C

When I change the machine date, the new task takes the new date as Sart date and the old ones are not changed

Why are you changing the machine date?
- I never manually change the PC date

Can we make Column D static and Column C dynamic so that it will be linked to Today () function to calculate the new remaining days when the date changes?

In post#5, I gave you code below so that column C is recalculated when the workbook is opened
- if the date has changed, column C is automatically recalculated
(must be in ThisWorkbook module)

Private Sub Workbook_Open()
    Call UpdateValues
End Sub

After changing the machine date you could
- save the file, close it and reopen it (above code recalculates column C)
OR
- add a button so that you can run UpdateValues (recalculates column C)
OR
- force a recalclulation of column C every thime the workbook is saved by ...
... placing code below in ThisWorkbook module (NOT standard or sheet module)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Call UpdateValues
End Sub

If I have misunderstood something, please explain EXACTLY what you are trying to do, listing ALL the steps

 

[mharchitect]

I found that macros were not enabled. Now it's working well.
Another thing... Can we add a status column. So after completion, a task can have a "done" status? And the Remaining will turn to 0.
Otherwise, all tasks will become overdue.
Thanks

 

[mharchitect]

Hello again my friend, are you on a leave these days?

I appreciate if you come back with a hint on the last query above.

Many thanks again for your support.

 

[Yongle]

Can we add a status column. So after completion, a task can have a "done" status? And the Remaining will turn to 0

Code below works for me

- status column is now column E
- original code simplified where possible and feature requested has been added
- click on cell in E and value toggles between "Done" and blank and the value in column C is updated accordingly

in SHEET module

toggles value in column E
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Target
        If .Row = 1 Or .CountLarge > 1 Then Exit Sub
        If .Column = 5 Then
            If .Value = "" Then .Value = "Done" Else .Value = ""
            .Offset(, 1).Select
            Call UpdateValues
        End If
    End With
End Sub
'reacts to changes in column B, puts current date in column D, runs UpdateValues
Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Row = 1 Or .Count > 1 Then Exit Sub
        If .Column = 2 Then
            If .Offset(, 2) = "" Then .Offset(, 2) = Date
            Call UpdateValues
        End If
    End With
End Sub

in STANDARD module

Sub UpdateValues()
    Dim Cel As Range, EndDate As Long, ws As Worksheet
    Set ws = Sheets("[I][COLOR=#ff0000]NameOfSheet[/COLOR][/I]")
    For Each Cel In ws.Range("A2", ws.Range("A" & Rows.Count).End(xlUp))
        EndDate = Cel.Offset(, 3) + Cel.Offset(, 1)
        Cel.Offset(, 3).NumberFormat = "dd/mm/yyyy"
        With Cel.Offset(, 2)
            If EndDate >= Date Then .Value = CLng(EndDate - Date) Else .Value = "overdue"
            If Cel.Offset(, 4) = "Done" Then .Value = 0
        End With
    Next Cel
End Sub

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​
1​	Task	Days	Remaining	Date	Status
2​	t001	10​	2​	28/03/2019​
3​	t002	15​	8​	29/03/2019​
4​	t003	16​	10​	30/03/2019​
5​	t004	12​	7​	31/03/2019​
6​	t005	15​	11​	01/04/2019​
7​	BEFORE
8​

Sheet: NameOfSheet

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​
1​	Task	Days	Remaining	Date	Status
2​	t001	10​	2​	28/03/2019​
3​	t002	15​	8​	29/03/2019​
4​	t003	16​	10​	30/03/2019​
5​	t004	12​	7​	31/03/2019​
6​	t005	15​	0​	01/04/2019​	Done
7​	AFTER

Sheet: NameOfSheet