If cell is more then or n/a check next

[Xebilon]

Hi!

Ive got an issue I hope someone can help me with. Im trying to see if I can find how long it will take to create a ticket based on when an incomming call was recived.
The problem is that not all calls might result in a ticket, and one call might create two tickets, or a ticket has been made with no connection to an incomming call.

In attached screenshot, in green there has been one contact, which created two tickets, and in orange, we have multiple contacts but only 2 created ticket, and red which has been made with no connection to a call.

Is there a way to make a formula to check in date and time and then the difference from when contacted to when created. I was thinking of a IF statment if the difference in more than "X" then check cell bellow. But I've got no idea how to create it, or if there is a better way.

Thanks all in advance.

 

[bobsan42]

from the data you show there is no way to tell to which call a ticket refers.
Normally you would have a call reference, then the ticket must refer back to it. Only then we can think about formulas.
for example: which of the three early calls on 20231205 is the basis for the ticket from 08:25. Or is it based on a call from another date?

 

[Xebilon]

from the data you show there is no way to tell to which call a ticket refers.
Normally you would have a call reference, then the ticket must refer back to it. Only then we can think about formulas.
for example: which of the three early calls on 20231205 is the basis for the ticket from 08:25. Or is it based on a call from another date?

I was affraid that this was the case.

I agree that if there was a reference it would be much simpler. But the raw data from the phonelog only shows date and time when an incomming call was made.
While the ticket has a reference number system, I didnt have it in the table above, as the reference doesnt say anything, as its only in the ticket system, and not in the phonelog.

But can it be made with something like to check if something is close or within 15 min difference. and use D:E as a check list.

Lets say for example
if A1 is found in D , then if B1 within 15 min of E show difference of B1 and result of found value got from E.
This formula is not correct in any way, but lets say?
Dont know if a Index/Match would solve this.

But if its indeed impossilbe Ive got my hands full ahead of me.

 

[bobsan42]

I don't think anything accurate can be achieved here with this data.
Even if we assume the call and the ticket have the same date and the call time is shortly before the ticket ... For example::
A call at 8:10 and one at 8:15, then you have two tickets at 8:20 and 8:25. A formula I can come up with will return call time 8:15 for the two tickets. So I wouldn't rely on anything without a reference to at least the call time
Anything else is just guesswork.
The other option would be to put a ticket reference against each call. This will face a problem of multiple tickets per call and is probably tedious to do.
And you already have an estimate of 15 minutes, so why bother.
Isn't it more important how long it takes for a ticket to be resolved/closed?

 

[Xebilon]

I don't think anything accurate can be achieved here with this data.
Even if we assume the call and the ticket have the same date and the call time is shortly before the ticket ... For example::
A call at 8:10 and one at 8:15, then you have two tickets at 8:20 and 8:25. A formula I can come up with will return call time 8:15 for the two tickets. So I wouldn't rely on anything without a reference to at least the call time
Anything else is just guesswork.
The other option would be to put a ticket reference against each call. This will face a problem of multiple tickets per call and is probably tedious to do.
And you already have an estimate of 15 minutes, so why bother.
Isn't it more important how long it takes for a ticket to be resolved/closed?

Thanks for the feedback, the best solution I have right nowt is litterly ask for a more detailed rawdata of the phonelog to easier match a ticket.

And both yes and no on tickets bein resolved/closed. Yes in thats one criteria that others are looking at, but for me its to see how long it takes from call to complited a ticker as a reference point to see how much of the work is done creating a ticket, and also see if it can be improved over time. (Sure every ticket in uniqe so one might take 1 min, another 10 min depending of the complexity)

Thanks for taking your time answering this post.