# Need help creating a tracker to tell me 60% & 75% of my tick

#### skbark

##### New Member
Hello,

I have an Excel dilemma. We are trying to track when a “Ticket” hits 60% & 75% of its 9.5 Business hour life, which is based on a workweek. I would like to be able to export my info to excel and have it automatically calculate the 60% & 75% stages. In column “E” we would have something like 8/26/2002 12:08, which is the date & time the ticket was opened. In Column “F” we would have either Pending Customer, Workaround, Reassign, or Open. We need the lines with Pending Customer or Workaround to be highlighted in yellow.

There can be an unlimited # of rows that are exported to excel so each line would have to be able to calculate this info.

I will also need this done for a ticket that has a life of 10 business days.

I do not know enough about Excel to do this on my own. Can anyone come up with a formula to calculate this? Or am I out of luck???

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
On 2002-09-25 12:07, skbark wrote:
Hello,

I have an Excel dilemma. We are trying to track when a “Ticket” hits 60% & 75% of its 9.5 Business hour life, which is based on a workweek. I would like to be able to export my info to excel and have it automatically calculate the 60% & 75% stages. In column “E” we would have something like 8/26/2002 12:08, which is the date & time the ticket was opened. In Column “F” we would have either Pending Customer, Workaround, Reassign, or Open. We need the lines with Pending Customer or Workaround to be highlighted in yellow.

I will also need this done for a ticket that has a life of 10 business days.

"We need the lines with Pending Customer or Workaround to be highlighted in yellow."

Investigate 'conditional formatting'.

"I have an Excel dilemma. We are trying to track when a “Ticket” hits 60% & 75% of its 9.5 Business hour life, which is based on a workweek."

Not quite so straightforward! I take it you mean you've got a working day that runs, e.g. from 8-6. So a ticket opened at 17:30 on a Friday won't 'expire' until 17:00 the following Monday. Care to post back with an example before we start on this?

Hello,

Yes, it is a working day. So, the "Break/Fix" Tickets have a 9.5hr life based on an 8:00 - 5:00 M-F workweek. The "Request" tickets have a 10day life based on the same workweek. This also needs to take holidays into consideration... We need the 9.5hr ticket to either “alert” us or to change color at the 60% & 75% life of the ticket life. The 10Day tickets need to alert us at 75% of the ticket life. I was thinking that I would need to separate sheets. One that I can export the 9.5hr tickets to and one that I can export the 10Day tickets to. I am not even sure if this is feasible. But it would make our lives a whole lot easier.

Basically, it is our responsibility to send out an alert when a ticket hits 60% & 75% of its life. Right now, we are trying to do this manually for each and every ticket, which can exceed 100 tickets at a time. Unfortunately, we miss some of these alerts. It is imperative that we don’t miss any of them.

Thank you so much for your interest in this problem.

Sandra

Hi- the 10 day tickets are easy - check out the networkdays() function - something like

=if(networkdays(startdate,today(),holidays)>=7,"Do something","")

or the equivalent in conditional formatting. It could also be possible to automate the generation of an email alert when the condition was met...

The other one is going to take a little longer, but will think about it tomorrow if no-one else has stepped in.

This message was edited by PaddyD on 2002-09-26 06:00

Oh my gosh... It could be set to send an email when the condition was met? That would be fabulous. If you could tell me how to set that up I would be forever grateful. I will try your suggestion for the 10day tickets as soon as I have a spare moment.

Again, thank you for your help!

Sandra

OK - let's start simple & complicate it as needed...(N.B. all of this assumes that you have genuine date / time values)

We can calculate the total age of the ticket as:

Life on day1:
=IF(INT(A5)< TODAY(),(INT(A5)+(17/24))-A5,0)

plus

Life on working days between day1 & now:
=IF(NETWORKDAYS(A5,TODAY())>2,(NETWORKDAYS(A5,TODAY())-2)*(9/24),0)

plus

Life today:
=NOW()-(TODAY()+(8/24))
Book1
ABCDE
4TimeHowmuchonthefirstday?Workinghrsondaysbetweenthen&nowHowmuchtoday?Total
527/09/200209:5000:000:0007:4507:45
626/09/200213:5003:100:0007:4510:55
726/09/200209:1007:500:0007:4515:35
826/09/200210:2306:370:0007:4514:22
925/09/200211:4505:159:0007:4522:00
1026/09/200215:1001:500:0007:4509:35
11
Sheet2

(N.B. At the time of posting this edited version of the table, now() returned 27/09/02 15:30 or there abouts)

We can run the formulas together a bit if needed, but shouldn't be too painful to have them in separate colunms. With the total ticket life calculated, it is straightforward to check if this is greater than, eg, 60% of 9.5 hours:

etc...

So - I haven't really tested this - see if this works as expected with your data. If it does, we can start to smarten it up a bit, add the email alert function etc.

Post back with details...

This message was edited by PaddyD on 2002-09-26 17:26
This message was edited by PaddyD on 2002-09-26 20:47

Hello skbark.

I am pure amateur with VBA, but this code send automatically émail when there is in cell E8 text Alert.

Sub Autpen()
ActiveSheet.OnEntry = "Action"
End Sub

Sub Action()
Call Hit
End If
End Sub

Sub Hit()
Dim myOutlook As Object
Dim myMailItem As Object
Set myOutlook = CreateObject("Outlook.Application")
Set myMailItem = myOutlook.createitem(0)

myMailItem.Subject = "It happened"
myMailItem.body = "At least it happed! Action needed."
myMailItem.send
Set myOutlook = Nothing
End Sub

Copy this to Module and make adjustments with Your sheet and save. Next time when opening workbook, code is looking for text Alert (if any) in cell what You decided and Excel sends émail automatically to address You made.

BRGDS Sir Vili.
This message was edited by Sir Vili on 2002-09-26 21:39

Replies
3
Views
111
Replies
3
Views
596
Replies
2
Views
365
Replies
0
Views
780
Replies
1
Views
535

1,219,035
Messages
6,145,910
Members
450,654
Latest member
lorento

### 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.

### Which adblocker are you using?

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

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