Formula to perform a calculation

[jward44]

I was wondering if one of you lovely people could help me with the following.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
To give a bit of background on what I'm doing, we have a ticketing system that we use to work on issues submitted by our user base. We will assign the ticket a severity and this will apply an SLA. For example a sev 4 may have an SLA of 48 hours. Occasionally we will stop the SLA clock while we gather further info, or change the severity and alter the SLA.<o></o>
When this happens, the SLA clock on the ticket isn't always accurate which makes it hard to work out when the SLA will breach.<o></o>
<o></o>
If the clock ticks 24/7 it's easy enough to work out how long is left. When the support hours are between certain times of the day it gets a bit trickier.<o></o>
<o> </o>
That text above only just makes sense to me so I’ll try and give an example:
<o> </o>
SLA is 48 hours total. Support hours are between 07:00 and 22:00, so the clock only ticks between these hours.
If an issue is logged at 08:00 on day 1 and is resolved on day 2 at 13:00, we took 20 hours of the SLA to fix the issue and so had 28 hours left on the clock.
That’s quite a basic example, as we might see the clock stop/start a few times between It being logged and resolved, but the principle is there.
I’m looking for a way to take the time and date stamps of when the ticket started, stopped etc, however many there are, and have excel calculate the time I have left on the clock.
Hope that makes sense to someone here and I’ll be very grateful for any help with this.
Many thanks
James

 

[jimrward]

just as a first attempt, it will be something on the lines of

(enddate_and_time - startdate_and_time in hours) - (numberofdays *9)

which in your example is

29 - (1*9), where 9 is the number of hours between close of play and start the next day

which gives 20

just need to come up with the (end - start) time in hours

 

[jward44]

Thanks for the reply

I've tried your suggestion and it doesn't seem to be working, possibly because i'm doing it wrong...

In my spreadsheet I have for example
A1 - 23/03/2011 08:00:00
B1 - 24/03/2011 13:00:00

which are my start and end dates and times.

C1 - =B1-A1

This gives me
29:00:00

Which is fine.

The number of days will be a variable so I've set field D1 to contain this
D1 - 1

E1 - =D1*9

F1 then would be for my final calculation but this is where it falls down.
F1 - =C1-E1

This gives me a whole lot of ###########

Is this something to do with the format of the data I have in the cells or is it related with how I've set this up do you think?

Thanks again for the help.

 

[jimrward]

ok had a rethink and changed it around so that we multiply the number of days * 15 and add the hours

=TEXT(B1-A1,"d")*15 + TEXT(B1-A1,"h")