[turbodante]

Nice work Aj.

[Vishal2009]

Hi Aj, need your help again for the same issue. Take a look at this data,

Created Time : 2/2/2009 20:55
Responded Date : 2/3/2009 12:09
Response Time : 23:14:26
Created : 02/02/2009 20:55:10
Responded : 03/02/2009 12:09:36

Only for this data the Response time is not coming up what we are expecting it to be. Can you suggest?

Vishal

[Aj_old]

Originally Posted by Vishal2009

Hi Aj, need your help again for the same issue. Take a look at this data,

Created Time : 2/2/2009 20:55
Responded Date : 2/3/2009 12:09
Response Time : 23:14:26
Created : 02/02/2009 20:55:10
Responded : 03/02/2009 12:09:36

Only for this data the Response time is not coming up what we are expecting it to be. Can you suggest?

Vishal

Yes you are right
I'll take a look and post back a new formula!

[Aj_old]

I made some changes, the problem was that if response time was earlier than the request time (different dates of course) it did not work properly!
Take a look at the attached file.

[Vishal2009]

Thanks AJ, let me try this out for multiple extracts and update you real quick. Cheers, Vishal

[Vishal2009]

Hi Aj,i seems the formula is resulting into the additionial 8 hours to the correct response time for all the tickets raised after 12.00 noon. e.g. in the spreadsheet cell no A53, 54, 55 the tickets are raised at around 7.00 pm and are accepted at around 8.00 pm but the response time is more that 8 hours instead of few mins. Can you please take a look at this? Thanks, Vishal (Apologies for bothering you for this issue!!!!)

[Aj_old]

Try this one, I used an UDF!

[Vishal2009]

That sound about perfect. There are few cells where we are getting the following error,
#End Date > End Hour
But i will manage it manually so shouldnt be a problem. Thanks again for all your help AJ.

Vishal

[Aj_old]

Originally Posted by Vishal2009

That sound about perfect. There are few cells where we are getting the following error,
#End Date > End Hour
But i will manage it manually so shouldnt be a problem. Thanks again for all your help AJ.

Vishal

That's not an error!
I designed the function to return this string in case the response time is after Work hours are ended!
So for example if the work hours are 8:00 - 20:00, then if response time is 20:05 it will return this string!

[Vishal2009]

Hi AJ,

Now while i am trying to use the string for a new data i am getting following message as a response time,

#NAME?

I tried changing the in and out timings, different combinations for the input data but its not going. I could be missing something here, can you please advice?

Thanks,

Vishal

[Aj_old]

Originally Posted by Vishal2009

Hi AJ,

Now while i am trying to use the string for a new data i am getting following message as a response time,

#NAME?

I tried changing the in and out timings, different combinations for the input data but its not going. I could be missing something here, can you please advice?

Thanks,

Vishal

Do you get this error for every row?
Or only for some timings?
I opened the workbook and there is no error.

[Vishal2009]

Oh I get it for every row.....

[Vishal2009]

Aj, i just checked the macro settings and changed it medium and got the issue fixed. sorry to bother you for this but have another issue, its showing incorrect response time for same day acceptance. Can you pls take a look? Thanks

[Aj_old]

Originally Posted by Vishal2009

Aj, i just checked the macro settings and changed it medium and got the issue fixed. sorry to bother you for this but have another issue, its showing incorrect response time for same day acceptance. Can you pls take a look? Thanks

Upload an example!

[Vishal2009]

Here we go...