Calculate days between two dates, until job completed.

I am trying to work out a way to show the increasing number of days between two dates that stops’ increasing once another cell is “Closed Out”

Column “D” shows the date in which the job is raised, with column “AM” showing the completion date once the “Closed Out” drop down within Column “AK” by using the below Macro (this was my very first ever Macro used, so not experienced in Macro at all)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 37 Then
Target(1, 2) = Time
Target(1, 3) = Date
Columns("B:C").AutoFit
End If
End Sub


Using the above, this provides me with a way to count the days between start and completion date only, but I am after a way to count the days once the start date is manually entered within Column “D” to increase day-on-day and to stop increasing once Column “AK” is “CLOSED OUT”.

This number needs to be displayed within Column “C”

I don't know of a way this can be done using a formula, so Macro may be needed.

Any help gratefully appreciated.

Andy,

1. Thanks for the tip will look to amend.
2. unfortunately, the date stamp swill not work. Once the start date in manually entered, there is a period of 7 days in which the job is to be closed out. Using conditional formatting 0-3 days remain white, 4-6 days Yellow, 7+ in red. The time between Start Date & Stop Date is then collected and placed within a graph.

If I were to just use the Date/Time Stamp for Stop/Start, this will not allow the Conditional Formatting to update each day unless each date within the active jobs are manually altered.

Hi Andy,

I do like this Macro very much and will be placing it within another document I will be working after his, however this Macro continue's to count up even when the Cell "AK" reads "CLOSED OUT".

Is there any way possible to have the count stop increasing when the above is actioned?

This keeps coming up with a Compile Error "Ambiguous Name Deetected" within the below section

"Private Sub Worksheet_Change(ByVal Target As Range)"

---

This is now my entire Macro written within the doc:

Please Login or Register  to view this content.

Hi Andy,

Your gonna hate me but I am still not able to make this work. Is their a way I can attach the doc to this site?

At the quick reply box, bottom right, there's a button "To go advanced" where you can manage attachements