Days elapsed & Alert

[dimitrz]

Hi All,

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 in status column “closed” is selected.

Also if the Days elapsed exceeds say 5 days then the entire row should be highlighted in red and if the days elapsed exceeds 10 days then the entire row should be highlighted in blue


I have enclosed/attached an sample file so that you can understand better what I need.

[Cheeky Charlie]

What happened to all the work already done on this project?

It's a bit demotivating to see you ignore all the help you've already been given...

[dimitrz]

Who said so Cheeky ?,

The project is well on its way as a matter of fact I am adding few more columns in my tracker and these things were previsolsly planned.

I didnt ask them at the same time because it would have created muliple threads from me and answeres wopuld have been limited to just few threads.

Also Experts might have got confused with multiple questions and that would have effected teh qualiy of answeres I got

so I decided to go one step at a time

Trust me there are few more fields and automation that I need to implement to make the tracker more effective.



Ps: though I must say the Workbook is still shared because I am having a little diffuclty to extract the data properely in rows - but since the Shared workbook is holding I though i'll address the workbook consolodiation to the last so that by that Time i'll have all my fields in place

[royUK]

You just need to subtract start date from current date. Place this formula in D9

=IF(C9<>"Closed",B9-A9,"")

[dimitrz]

Thanks Roy

Let me test it out

[dimitrz]

(Latest tracker sample enclosed)

Hi Roy

The formula you gave is not working fully
When I select Closed in the Status column the Days calculated till then also disappears

Please Login or Register  to view this content.

apart from yours I had a code from Dom

Either I am stupid or that code is not working either - tried modifying it to suit the tracker but not happening.

Ps: Even if the row is not highlighted its ok - I can still do a Cond Format for that cell - however my main concern is to get the days elapsed calulated accurately and the calulation to stop when the status is either Closed or Resolved - but even if the calculation stops in the days elapsed box that last calulated day should be still visible.

Dom's code

Please Login or Register  to view this content.

Cross posted at http://www.mrexcel.com/forum/showthr...28#post1684428


Thanks

[dimitrz]

More accurate sample of the Tracker