Days elapsed & Alert

D

dimitrz

Board Regular
Joined
Aug 30, 2007
Messages
55
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.


Link to the sample :
http://www.excelforum.com/excel-general/654718-days-elapsed-and-alert.html
 
Sort by date Sort by votes
Hi,

Sorry, can't view your file as I'm not a member of that forum but here's an example of how to achieve what you ask:

Say you had a sheet with Start Date in Column A and Status in Column B

The code below checks when you change a cell in B2:B10 and then adds the date and time to the adjacent cell in Column C. It then calculates the difference between the start and end time and colours the row if necessary.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B2:B10")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value = "Closed" Then Target.Offset(0, 1) = Now
Select Case Int(Target.Offset(0, 1) - Target.Offset(0, -1))
    
    Case Is > 10
        
        Target.EntireRow.Interior.ColorIndex = 5
        
    Case Is > 5
    
        Target.EntireRow.Interior.ColorIndex = 3
        
End Select
End Sub

The code needs to be placed on a worksheets code page, right click on the sheet tab and select View Code.

Hope it helps,

Dom
 
Upvote 0
Thanks Don let me go through the code you sent me.

I would have attached the sample sheet here but It seems I dont have acess to file attachment
 
Upvote 0
Okay, image is a bit blurred when I view it but this will check for changes to column C, put todays date in column B if Closed is entered and sort out the formatting as your described.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value = "Closed" Then Target.Offset(0, -1) = Date
Select Case Target.Offset(0, -1) - Target.Offset(0, -2)
    
    Case Is > 10
        
        Target.EntireRow.Interior.ColorIndex = 5
        
    Case Is > 5
    
        Target.EntireRow.Interior.ColorIndex = 3
        
End Select
End Sub

Dom
 
Upvote 0
Thanks Domski

Let me check it out once I am back at work and have my files

I presume that if I want the Time to stop if I select "Closed " or " Resolved" from the drop down menu in say column "C". I can modify this line like this right?

code:

Code: 
 If Target.Value = "Closed" "Resolved" Then Target.Offset(0, -1) = Date


Ps: Apoligize for entering your name as DON instead of DOM in the previous response. when you guys take time out to answer my questions getting your name correct is the least I can do. : -)
 
Last edited:
Upvote 0
More like:

Code: 
If Target.Value = "Closed" Or Target.Value =  "Resolved" Then Target.Offset(0, -1) = Date

Dom
 
Upvote 0
Hi Dom

Either I am stupid or that code is not working - 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 calcilation stops in the days elapsed box that last calulated day should be still visible.


RoyUK
had given me this formula but when I select Closed in the Status column the Days calculated till then also disappears
Code: 
 =IF(C9<>"Closed",B9-A9,"")

sample of my tracker is in my cross post at http://www.excelforum.com/excel-general/654718-days-elapsed-and-alert.html#post1967781

Thanks
 
Upvote 0
I think This is a better explanation of what I require

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Userform Textbox Date and Time (Elapsed Time) Formatting Question
Replies
2
Views
207
dmt32
D
D
  • Solved
NETWORKDAYS not working past 3 days
Replies
5
Views
141
Joe4
Joe4
A
  • Solved
Within cell progress bar for Working days?
Replies
5
Views
818
felixstraube
F
edge37
  • Solved
Days elapsed from start date to finish date
Replies
2
Views
211
edge37
edge37
S
  • Question Question
Fixing Holiday Cancellation Issues in Automated Excel Workbook (VBA)
Replies
6
Views
132
Alex Blakenburg
A

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top