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]

This is the original question from the cross-posted thread and subsequent clarifications

one excel spreadsheet was designed as a follow-up list of employees' tasks. It's required to input one value only (planned duration) in a cloumn. The client needs this list to be automaticallly updated everyday by showing the remaining duration in a new column as follows:

Task planned duration remaining duration
report 60 59

in the given example, it is supposed that the planned duration (60) was entered yesterday and the remaining duration is shown today (59).

the problem is that they need to enter the planned duration only without entering a date (target or initial) in another column.

i was thinking if a code can generate a value for the initial date once a duration is entered and this value is not changed so a hidden formula can then calculate the remaining days each day the file is opened

 

[Yongle]

Q1 Is each "Task" unique?
Q2 Is the user only inserting "Planned Duration" ? (ie the "Task " is already in the worksheet)

You say you want formula and target date hidden
Perhaps use a HIDDEN column to contain "Target Date"

1. USER sheet - user adds duration in column B
2. Worksheet_Change event updates
(a) HIDDEN column with "Target Date" for that "Task" [ = "Current System Date" + "Planned Duration" ]
(b) Column C "Remaining Duration" [ = "Planned Duration" ]
3. Workbook_Open event updates all "Remaining Duration" values

 

[mharchitect]

Q1 answer: Yes
Q2 answer: the user will insert a new task and its planned duration in a new row as per the attachment example (Column C is manually calculated in this sheet / after 2 days from the 1st input of planned duration: assumed entered in the same day).

[TABLE="width: 500"]
<tbody>[TR]
[TD]Task[/TD]
[TD]Planned Duration (days)[/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD="align: left"]Remaining Duration (days)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65, width: 89, align: left"]Reporting[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]60[/TD]
[TD]58[/TD]
[/TR]
[TR]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65, width: 89, align: left"]Drawing[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]45[/TD]
[TD]43[/TD]
[/TR]
[TR]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65, width: 89, align: left"]Issuing an RFP[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]30[/TD]
[TD]28[/TD]
[/TR]
</tbody>[/TABLE]

 

[Yongle]

Amend this to suit your own needs
- tasks are in sheet with name NameOfSheet
- headers are in Row1
- column A contains tasks
- column B contains planned duration
- column C contains remaining duration (generated by VBA)
- column D contains target date (generated by VBA)

In ThisWorkbook module

Private Sub Workbook_Open()
    Call UpdateValues
End Sub

In Standard Module

Sub UpdateValues()
    Dim Ws As Worksheet, Cel As Range, Rng As Range
    Set Ws = Sheets("[I][COLOR=#ff0000]NameOfSheet[/COLOR][/I]")
    Set Rng = Ws.Range("A2", Ws.Range("A" & Rows.Count).End(xlUp))
    For Each Cel In Rng
        If Cel.Offset(, 3) >= Date Then
            Cel.Offset(, 2).Value = Cel.Offset(, 3) - Date
        Else
            Cel.Offset(, 2).Value = "overdue"
        End If
    Next
End Sub

In SHEET module

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 1 Then Exit Sub
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
        Target.Offset(, 1) = Target
        Target.Offset(, 2).Value = Date + Target
        Target.Offset(, 2).NumberFormat = "dd/mm/yyyy"
    End If
End Sub

 

[Yongle]

Above VBA ignores this , but you need to consider what should happen if user amends planned duration when workbook is opened on any day AFTER original input - target date is recalculated from current date and would enable user to keep moving date forward
You may want start date to be locked based on original input date!

 

[mharchitect]

I believe you have pointed to the exact point above.
The tried code is returning the remaining duration the same after the machine date is augmented!
No solution till now.

 

[mharchitect]

Is it possible to design a code that returns the entry date once a duration is entered? That would solve the issue.

 

[Yongle]

Perhaps use "current date" stamp instead of calculating the "end" date
(Date is the VBA function equivalent of =TODAY() in Excel)

To date stamp the "current date" in Worksheet_Change, use this method

Target.Offset( , 2) = Date

To prevent the value being overwritten if date stamp already present

If Target.Offset(, 2) = "" Then
   Target.Offset(, 2).Value = Date
     etc

Remaining days calculated like this: Date Stamp + Planned Duration - Current Date

Target.Offset( , 1) = Target.Offset(, 2).Value + Target.Value - Date

You would need to amend both Worksheet_Change and UpdateValues to reflect these changes

 

[mharchitect]

Sorry, I didn't figure out what lines to change. Not expert in VBA. Also not able to attach the xlsm file.