[red_4_eva]

Hi Red here,

I am new to Tech Support Guy, please forgive any mistakes as those of ignorance not arrogance.

I am hoping that someone has already written themselves a spreadsheet that they are willing to share... because I have burnt out out way too many braincells trying to create something that works. I am an advanced Excel user, but this one has me stumped. . I either need help from someone who knows how to perform very complex time calculations in Excel or I need a freeware calculator application I can download fast that will help me out.

I am testing a new call management system that includes automatic escalation to the management should a call not be responded to or resolved within a specified time frame. Escalation is a matter of hours (respond) or days (resolve). The 'resolve by' calculations are very easy, they are working fine. However the 'respond by' time is dependent on the start/end times of the day, whether or not weekends are worked and the timeframe within which the management wants to see escalation happening e.g. "I want to know if no response after X hours (level 1) and again at Y hours (level 2)". The System is capable of accepting this data, varied by department (ours have different hours of operation), and using the data to calculate the day (date) and time on which an alert will be sent to the management following receipt of said inquiry, should no response have been sent to the inquirer.

MY task is to verify the escalation calculations (the system has been horribly inaccurate in previous tests) and in order to do this I am given a spreadsheet of 'submission date/times' on which I am to input MANUALLY the expected escalation times for each department at a defined first and second level, prior to creating the inquiries themselves in the test system and checking whether their calculations match my expectations. It takes me longer to fill out this spreadsheet with the expected times than it does to carry out the system test itself!

I have found out (boy have I) that Excel really isn't amenable to this kind of elasped time calculation, mainly because some of my time elapses extend beyond 24 hours when you take the business hours into account. Unfortunately, Excel is the only tool I have available to me for this job right now, Access simply isn't an option.

Last but not least, I looked at downloading freeware call management software but that means doing the same job twice (creating a pretend call) and I would have no way of knowing whether the downloaded application was performing the calculations correctly without (yep, you guessed it) working out the times manually...

So LOOOOOOONG story short. I need an Excel worksheet where I can plug the data into an array and have the correct escalation time squirt out the other end of a formula. Or a freely available application where I can do the same.

Any assistance VERY GRATEFULLY received!
Cheers
Red

[MRdNk]

Hi red_4_eva,

Firstly if you need to hours to go beyond 24 hrs in a cell you just need to use a custom cell format [hh]:mm:ss - the brackets allows it to go past 24hrs.

It would be easier to understand the whole problem if you posted an example sheet.
Inc Data In and end result.

- MRdNk

[red_4_eva]

Hello MRdNk

Ok you got me, I was trying to avoid embarrassment, just in case my logic is completely fritzed...

Attached is an example of how far I have got.

TL (level one is Team Lead) 'respond by' appears to be working, but the MG (Level 2) is an hour out in this scenario.

Cheers
Red

[slurpee55]

Hi red_4_eva,

I agree with MRdNk, except, being new to the forum, you probably can't post yet.
Email me a sample of your file - put junk data in it, don't send real data - and include a link in the email to this thread (that is, put in the url for this thread) so I know where to post the file for you.
You can email me by clicking on my name and then viewing my public profile.

[MRdNk]

To make it a little easier to understand, which specific cell (column) are you trying to correct, what should the answer be? How is it currently calculated? And perhaps a simple just the important bits scenario would help.

[red_4_eva]

I put an example in as an attachment in my reply... just above your reply. It looks like it's there. As regards the data, the timings are real, but not sensitive information. We proudly publish our support levels to our customers if only we could calculate them as easily .

Thank you
Red

[slurpee55]

HI - for some reason, it wasn't there the first time...oh well! (It was lost in the net....)
However, looking at your sheet, I am not certain where you want what data....

[MRdNk]

Have to agree with slurpee - its difficult to see the error and where you need the fix!
Need a column letter or Cell reference.

[red_4_eva]

Sorry, I just realised how fried my brain truly is, I gave the wrong info in my example post, calc is way more than 1 hour out and my spreadsheet times started on a Monday instead of the required Saturday . Corrected sheet attached.

Anyhoo, Trying to correct col AE.

According to my fingers (counting thereon) MG respond by time should be 7 Feb at 7:25 am. Cols R thru Z are supposed to be performing the calculation, so I'm hoping you don't want me to write all that out again.

I don't quite follow what you want by way of simple... so Example summary from line 6 is: Email arrives Sat Feb 2nd (out of hours) so is considered as arriving at 7am Monday Feb 4th. If no response is sent out by 3pm that day, TL is alerted. If still no reply after a further 8 business hours, MG is alerted. Business hours works out as 2 left at end of day (3 - 5pm), plus 6 hours from start time the day after; 7 Feb at 7:25 am.

Cheers
Red

[red_4_eva]

Darn it! Not 7 Feb....MG respond by on 5 Feb at 07:25... I need to go home, my brain hurts.
Red

[slurpee55]

LOL - that time for me too - see ya later!

[red_4_eva]

OK it's official, I have brain cramp. Last attempt at lucidity...

Email considered to arrive at 07:00 Mon Feb 4; 16 business hours later is 13:00 Tues Feb 5.

On that befuddled note... I shall abandon my post and try again tomorrow!

Red

[MRdNk]

I'm so glad of that last post couldn't work out why it was 25mins past when you're starting at 7am.

Not sure, if there is something further I'm missing, but...

It looks to me, at this stage, that the error is the use of +Day() in Columns M, V, AH and AK.

=Day() returns the numeric value of the day of the month from a date.
So =Day(25/05/2008) would return a value of 25.

And on your spreadsheet it reads 1 as 02/01/1904, and therefore the returned value is 2, adding 2 days.

See: http://www.techonthenet.com/excel/formulas/day.php

If you want to add 1 day then just use +1, so
V6 (becomes): =G6+1
Replacing: =G6+DAY(1)

In addition I'd be more inclined to use TRUE and FALSE than 1 and 2 in Col K & T.

Let me know how you get on with that lot, and get back to me.