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

skbark

New Member
Joined
Sep 24, 2002
Messages
13
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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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?

Paddy
 

skbark

New Member
Joined
Sep 24, 2002
Messages
13
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 :)
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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.

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

skbark

New Member
Joined
Sep 24, 2002
Messages
13

ADVERTISEMENT

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 :)
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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:

if(ticket_age>=57/240,"Alert","")

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

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

Sir Vili

Board Regular
Joined
Jul 11, 2002
Messages
148
Hello skbark.

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

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

Sub Action()
If Range("E8").Value = "Alert" Then
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.Recipients.Add "Your émail address here"
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
 

Forum statistics

Threads
1,144,363
Messages
5,723,917
Members
422,527
Latest member
JayTheKaz

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
Top