• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search results

  1. I

    Calculate Time Difference (Business Hours Only)

    Mike it appears to be solving the issues I found will let you know over the next couple of days. Thanks for all you help. I have learnt new things and as always frustrated myself by knowing I should be better at this. Much appreciated Best Wishes Ian M
  2. I

    Calculate Time Difference (Business Hours Only)

    Hi Mike Sorry to hear about your loss happy to wait for a response. I think I understand why some of the answers are coming out wrong. Some of the tickets are being closed before they are supposed to be able to so they are ending up as less than zero hours. Hear from you when you can mate...
  3. I

    Calculate Time Difference (Business Hours Only)

    Is no one able to give an opinion??? No help out there?? Ian M
  4. I

    Calculate Time Difference (Business Hours Only)

    Well Guys and Girls seems I have run into some problems and need some help please. Have attached a sample file which shows some results that are correct but some don't seem to be working. The rows highlighted in yellow are reporting incorrectly. The rows highlighted in red are giving the...
  5. I

    Calculate Time Difference (Business Hours Only)

    Luke M / Narayan and Mike H As always you have gone above and beyond with your help and support thank you and each of you. I continue to be astonished at your skills and hope one day I will be half as good. The important thing is keep learning which for me is the best part of the forum. Tested...
  6. I

    Calculate Time Difference (Business Hours Only)

    Ok First off I have had to simplify the Q3 calculation for now as there is debate now going on as to the accurate SLA. This has caused all sorts of discussions. So for now we should just calculate all SLAs against possible working hours. So I have some real data in the attached file but...
  7. I

    Calculate Time Difference (Business Hours Only)

    Hi Guys Have not been well the past couple of days. Tooth extraction has caused an infection. Just started testing and will get back to you as soon as possible. Regards Ian M
  8. I

    Calculate Time Difference (Business Hours Only)

    Mike Wow that looks like we have it. Let me play and test it. Also I will have some real-time data to also test it against late tomorrow afternoon that will let me truly test the results. Thank you for your effort and will come back to you as soon as. Regards Ian M
  9. I

    Calculate Time Difference (Business Hours Only)

    Hi Mike you think you have a headache imagine mine. Queue 3 rules are the clock starts running from the second a ticket is changed and ends the second the ticket is closed. It then counts hours passed but excludes weekend hours. So for example ticket changes at 11:00am on a Thursday and close...
  10. I

    Calculate Time Difference (Business Hours Only)

    Hi Narayan Created some sample data and calculated the results manually. Hopefully it all makes sense. Regards Ian M
  11. I

    Calculate Time Difference (Business Hours Only)

    Narayan You have all the correct information. The confusion comes from the fact that when it falls under a 24 SLA the clock starts at 05/08/2014 12:30 and finishes 07/08/2014 13:30 and calculates the complete time worked. a 24 hour SLA ignores the the working hours of the days but would...
  12. I

    Calculate Time Difference (Business Hours Only)

    Hi Narayan thanks for helping out. I understand the helper column suggestion but looking at your example I have got completely lost again. Luke's last solution is working for me apart from the third row. Just to review where we started. Created Date is when we get a support ticket arrive in a...
  13. I

    Calculate Time Difference (Business Hours Only)

    Hi Luke sorry for the late reply, excessively long day and all that. Seems to work brilliantly for the first two example lines, Many thanks. The only issue I have now is the third line example and that why I was asking if maybe we should treat that type of SLA completely differently. It now...
  14. I

    Calculate Time Difference (Business Hours Only)

    Hi Luke I always have problems with nested formulas. Thanks for your response. I took a look and love the solution but testing has shown some issues. I am wondering if I should treat the to types of SLA differently. We have the calculation for working hours in a working day or the continuous...
  15. I

    Calculate Time Difference (Business Hours Only)

    Hi Everyone Rather than hijack a post from earlier today http://chandoo.org/forum/threads/calculating-difference-between-two-dates-times-by-business-hours.20261/ I though I should post a new question. I think the answer is a little simpler but working through the solution for the above post I...
  16. I

    Pivot Table Help

    Do you know Narayank991 I didn't even think about a helper column, you would think I would remember when I see it used in lots of example on here. What a dumb &^% I am. Although saying that I am not sure I am still good enough to have come up with that formula. Still struggle with nested...
  17. I

    Pivot Table Help

    Hi All I regularly have to produce reports that initially only had a few records each week and so was just simple and easy to manipulate the raw data rather than have a more automated solution. Now I am getting more and more records which means I need a better solution. You can find a sample...
  18. I

    Formula Challenge 010 - Two-way interpolation.

    @jeff Is there any way I can stop seeing any challenges set by you, other than gouging my eyes out. I am always intrigued by the question and of course the answers. But I am truly in a mess when I go to bed dreaming of how I would even try to figure this out.
  19. I

    creating week number not starting on 1 Jan [SOLVED]

    Well with some help from elsewhere (Kudos goes to daddylonglegs of http://www.excelforum.com/) I have a solution that falls in line with what you believe to be the criteria NARAYANK991 so either way hopefully Curious George will have his answer...
  20. I

    creating week number not starting on 1 Jan [SOLVED]

    Curious George even I am a little confused with what you are now requiring as is SirJB7. You have had some excellent answers from two great Excel Ninja's. However I understand the frustration you can sometimes feel when you are trying to find an answer. In you original post you wanted week...
  21. I

    creating week number not starting on 1 Jan [SOLVED]

    SirJB7 you beat me to my own answer. As ever you are a superstar I am currently a 2007 user and so a change from argument type 11 to 2 did solve my problem and has allowed me to improve a solution I already had in place. A quick visit to my old friend http://www.excelfunctions.net/ shows quite...
  22. I

    creating week number not starting on 1 Jan [SOLVED]

    appreciate the help, but so you understand. I typed the formula exactly as you had written it and once hitting return it gives a #NUM! error. When checking your worksheet and inspecting any cell with the WEEKNUM formula in (I.E. F2) and pressing return (enter)after checking out your solution the...
  23. I

    creating week number not starting on 1 Jan [SOLVED]

    Thanks SirJB7 for your help, I knew someone would be along to help further. However your formula [=WEEKNUM(A1,11)-WEEKNUM(DATE(2013,4,6),11)+1+52*(WEEKNUM(A1,11)<WEEKNUM(DATE(2013,4,6),11))] doesnt work for me.
  24. I

    creating week number not starting on 1 Jan [SOLVED]

    Hi Curious George I had a similar problem which I solved by using one of the following examples that can be found here http://excelworks.co.uk/default.aspx?page=30200 I chose the Download calendar with custom week numbers (xls 330KB) which enabled me to set the calendar to start on a...
  25. I

    VBA Improvement [SOLVED]

    Well that explains why I was getting what looked like the target cell flashing very very fast, obviously stuck in a loop until it got upset or I reset the code by clicking End. Well thank you for the help and for the very helpful explanation. Onwards and upwards Will find more out by looking up...
Back
Top