[Vishal2009]

It seems the issues we all were facing for last couple of months, you got it fixed. If you dont mind i would like to try this out on other data extracts too so i can seek your help if required.

[Aj_old]

You can test it as long as you like, just remember to tell us the results and if it's positive then to mark the thread as solved!

[Vishal2009]

AJ, can you please help me understand the reference of Hdays in the formula and the column L wherein we have list of holidays? Did you define column L as Hdays, if yes can you pls explain how so i can do it on my own on other datasheets.

Thanks,

Vishal

[Vishal2009]

AJ, i got it. You defined it under Insert>>Names>>define...sorry to bother you for this simple issue. I will keep you posted about the progress.

Thanks again,

Vishal

[Aj_old]

To not to use the cell reference for the range I just named the range in which dates for holidays will be stored, that's all! It's not the whole column L just a part of it.
Press ctrl+F3 and you'll see the rage I named as HDays, there you can add, delete, or change named rages in you workbook.

[Vishal2009]

****

[Vishal2009]

Hi AJ, its me again . I have a query here. If you look at the spreadsheet cell no A14 & A15, the tickets are raised on 4th which is a Saturday and accepted on 7th which is Tuesday, which mean the Response time should be around 16 hours but here we are getting it around 32 hours which means it is including Sat and Sun in the calculations. I could be missing something here, can you help.

Thanks,

Vishal

[Aj_old]

Originally Posted by Vishal2009

Hi AJ, its me again . I have a query here. If you look at the spreadsheet cell no A14 & A15, the tickets are raised on 4th which is a Saturday and accepted on 7th which is Tuesday, which mean the Response time should be around 16 hours but here we are getting it around 32 hours which means it is including Sat and Sun in the calculations. I could be missing something here, can you help.

Thanks,

Vishal

You are right
Take a look at this, it uses 2 additional columns, but seems to do the work.

[Vishal2009]

Thanks AJ, let me try it on multiple data extracts and i will update you real quick.

Vishal

[Aj_old]

Originally Posted by Vishal2009

Thanks AJ, let me try it on multiple data extracts and i will update you real quick.

Vishal

ok

[Vishal2009]

AJ, sorry but i am getting two errors while to work out the formulae for another month. Take a look at the attachment. Row 37 and 40, the request came in at 21:21 and responded at 21:23 (end time 21:30) should give response time 0:02 but its cmng up -ve as the created date changed to the next day. Can you pls suggest. Thanks.

[Aj_old]

Originally Posted by Vishal2009

AJ, sorry but i am getting two errors while to work out the formulae for another month. Take a look at the attachment. Row 37 and 40, the request came in at 21:21 and responded at 21:23 (end time 21:30) should give response time 0:02 but its cmng up -ve as the created date changed to the next day. Can you pls suggest. Thanks.

You entered 21.30, but this is 21 and 3/100*60 = 18 minutes, change it to 21:30, and enter the time in the hh:mm format (using colon not point or any other)

[Aj_old]

Sorry, change it to 21.5 (this is 21:30)
You did not said that time can be in this format and I did not thought you need to use it in this format.
Take a look at the attached file, I think it should solve this problem

[Vishal2009]

Oh i got it, (dumb me). Yes it is perfect and AJ thank you very much for all your help and efforts you took to fix this issue for me. Many thanks to you. Am closing this thread here and happy to be at Tech Support Guy because of you experts.

Cheers,

Vishal

[Aj_old]

You are welcome!
Glad that was able to help you!