[Vishal2009]

Hi,

I am not an expert in Excel but can understand the functions in it. I am working on a project where i have to calculate Response time for a ticket request. This is project works only 8 hrs a day and 5 days a week whereas there is no time restriction for incoming ticket requests. I have attached a sample data here. In the spreadsheet, column C shows the actual time when the ticket is created and Column D show when the ticket is accepted. Now if project works 12 noon till 9 pm and if the ticket is raised outside these working hours, it is treated at 12 noon. It should not include Saturday, Sunday and any public holiday. So if the ticket is raised on Friday 10 pm (Shift ends at 9.30 pm Friday) and its a public holiday on Monday, then the ticket accepted on Tuesday at 12:15 pm should give the Response time as 15 mins and not total no of hours from the ticket ticket raised. One more info about the time stamp, it is in mm/dd/yy hh:mm format.

Can someone please help me to get a formula for this calculation.

Thanks,

VK

[turbodante]

Hey Vishal, welcome to the forum.

You say you're okay with functions/formulas. Perhasp if I nudge you towards using

= NETWORKDAYS(Start_date,End_Date,holidays)

for the holiday,weekends issue.

[Vishal2009]

Sorry friend, i take my word back. Am not that expert on all the formulas. Can you please help me by explaining a bit more as how i will be refering the holiday list in my calculation... am sure this is going to be a silly question for you.. sorry about that...

[turbodante]

Originally Posted by Vishal2009

Sorry friend, i take my word back. Am not that expert on all the formulas. Can you please help me by explaining a bit more as how i will be refering the holiday list in my calculation... am sure this is going to be a silly question for you.. sorry about that...

Apologies for the assumption: you’re quite right to feel daunted with this formula – I must admit I’ve never used it before, but this is the best I could produce using the rules you’ve provided is attached.


The formula required:
=D2-(IF(AND((NETWORKDAYS(C2,C2,$J$7:$J$10))=1,AND((TIME(HOUR(C2),MINUTE(C2),0)) >$J$4,(TIME(HOUR(C2),MINUTE(C2),0))<$K$4)),C2,WORKDAY(DATE(YEAR(C2),MONTH(C 2),DAY(C2)),1,$J$7:$J$10)+$J$4))

relies on three main things:

• a further table to for public holidays to exclude from the calculation
• Start/Stop times table: working hours – it is important that you use the formula: = TIME(hour,minute,seconds) for the two entries.
• Cell format to be [h]:mm:ss (this is in Format Cells > Number > custom)

In the attached, there are errors returned, which looks like Response times before 12:00, which will throw you an error; also if responses recorded fall on weekend or public holidays.

Hope it works for you.

[Vishal2009]

Thank you very much for your help and yes it is perfect. I will find a work around to fix the Response time before the shift start. I may ask for your help again as i am amazed after looking at such a long-lengh formula you came up with. Hats off to your knowledge.

Thanks Again,

Vishal

[Vishal2009]

Sorry but it seems the formula is not able to capture it for few of the cells. If we take a look at the A12 (INC000001029162), the Response time should be around 40-50 hours whereas we are getting it around 160 hrs. Can you please take a look and suggest?

Thanks,

Vishal

[Aj_old]

Vishal, one question: the working hours are always the same, let say 8.00 - 16:00?
Or?

[Vishal2009]

Yes, the working hours are always the same.

[Aj_old]

Is there possible the situation when the response is not within working hours (IE earlier or later) or such situations are excluded?

[Vishal2009]

Yes, there could be few such situations as if the person reports to office say 10-15 mins prior to office timings. In that case i still want to keep it restricted to the actual office start time (12.00)as accepted time. I mean even if the ticket is accepted at 11:45, i want to treat it at 12:00. Hope i answered your question.

Vishal

[turbodante]

So you're only counting working hours, everything outside of these hours are excluded?

[Vishal2009]

Yes, that is correct. All the requests raised outside the shift timings should be treated as if raised at 12:00 noon next working day.

[turbodante]

What I mean is, you want to make the calculation of only working hours. ie. for 03/10/2008 19:33 to 10/10/10/2008 12:17 that is

durations of
1:27
9:00
9:00
9:00
9:00
0:17

which is 37:44?

[Aj_old]

Take a look at the attached workbook.
I added a new column to make it easier (to calculate the real date and time, that will satisfy your constraints)

[turbodante]

Looks like the ticket Aj. Nice work.