Days Elapsed

[dimitrz]

Hi All,

I am trying to get a solution to this problem for a long time without any sucess - would be greatful if anyone here can help?

What I need to do is have a function which calculates the days elapsed between current date & time and a previous date & time and this calculation should stop when a Colum called Status ?closed or resolved ? is selected.

For example lets say in a tracker
Column B has Time in hours = 12:00
Column C has Date in this format = 15-March-2008
Column M = Status which has options like Open, Closed, Resolved, Onhold

Column O has Now() function formated to give Time in hours
Column P has Now() function formated to give Date like 15-march-2008
Now in column Q , I want the diffrence between O & B i,e how much hours has elpased since B till O eg 10 hours
Now in column R , I want the diffrence between P & C i,e how many days has elpased since C till P eg 7 or 14
However lets say if "Closed or Resolved " is selcted in Colum M then the counting should stop and the count till the closed was selected should be displayed in both Q & R cell.

Its a bit of a complicated query and no one has been able to resolve it till date. hence if you can resolve it I would be greatful to know how to do it.
I use excel 2003 and am a novice in Excel.


I have enclosed the sample tracker where I need to input the details

[Bob Phillips]

[vba]


Private Sub Worksheet_Change(ByVal Target As Range)

With Target

If .Column = 13 And .Row > 1 Then

If .Value = "Open" Then

With .Offset(0, 4)
.FormulaR1C1 = "=RC[-1]-(RC3+RC2)"
.NumberFormat = "[h]"
End With
With .Offset(0, 5)
.FormulaR1C1 = "=RC[-2]-(RC3+RC2)"
.NumberFormat = "d"
End With

ElseIf .Value = "Closed" Then

With .Offset(0, 4).Resize(, 2)
.Value = .Value
End With
End If
End If
End With
End Sub
[/vba]

[dimitrz]

Hi XLD,

Thankyou for your code

Tried your code however it doesnt seem to be working.

Am I doing something wrong

Am enclosing the tracker where in I have added your code.

Please advise

[Bob Phillips]

The code only kicks in when you Close an item, and freezes the times at that point.

[dimitrz]

Thanks XLD for the tips however I would need the calculation to freeze and show when the closed or resolved is selceted but otherwise I need the calclation to go on so that I also have a data on current number of days & hours elapsed

[dimitrz]

What I need to do is have a function which calculates the days elapsed between current date & time and a previous date & time and this calculation should stop when a Colum called Status “closed or resolved ” is selected.

For example lets say in a tracker

Column B has Time in hours = 12:00
Column C has Date in this format = 15-March-2008

Column M = Status which has options like Open, Closed, Resolved, Onhold


Column R has Now() function formated to give Time in hours
Column S has Now() function formated to give Date like 15-march-2008

Now in column T , I want the diffrence between R & B i,e how much hours has elpased since B till R eg 10 hours
Now in column U , I want the diffrence between S & C i,e how many days has elpased since C till S eg 7 or 14

However lets say if "Closed or Resolved " is selcted in Colum M then the counting should stop and the count till the closed was selected should be displayed in both T & U cell.

Its a bit of a complicated query and no one has been able to resolve it till date. hence if you can resolve it I would be greatful to know how to do it.
I use excel 2003 and am a novice in Excel.